IF function for many cells

3 posts / 0 new
Last post
IF function for many cells


I am using IF function to capture the data those who come on time or late to come to the office. The thing is, there are 31 cells that represent date so I try to insert the formula like this,

=IF(D6<=8),(F6<=8),(H6<=8),(J6<=8),(L6<=8),(N6<=8),(P6<=8),(R6<=8),(F6<=8),(H6<=8),(H6<=8),"On time","Late")

But it did not work :(

Appreciate much if you can help to figure out how can i solve this

This is not a valid IF function

The problem is that your formula is not a valid IF function.

Your formula looks like this:

=IF(D6<=8),(F6<=8),(H6<=8),(J6<=8),(L6<=8),(N6<=8),(P6<=8),(R6<=8),(F6<=8),(H6<=8),(H6<=8),"On time","Late")

The syntax of the IF function doesn't allow you to do this.

The IF function requires this syntax:

=IF(logical_test, value_if_true, value_if_false).

If you want to have multiple logical tests as shown in your formula, you need to have multiple IF statements, e.g.

=IF(D6<=8,"Late",IF(F6<=8,"Late","On time"))

This formula will check if D6<=8. If it is, it will print "Late". If it is not, it will then check if F6<=8. If this is true, it will print "Late". Otherwise it will print "On time".

Note that this formula will always return "Late" if both D6 and F6 are less than or equal 8. Otherwise it will return "On time". Another, simpler way to write this formula would be this:

=IF(AND(D6<=8,F6<=8),"Late","On time")

This formula is closer to your original formula and uses only one IF function. Note that if you wanted the formula to return late if either D6<=8 or F6<=8, you would use OR instead of AND, like this:

=IF(OR(D6<=8,F6<=8),"Late","On time")

Either way, writing a formula using the AND or OR functions as part of the logical test is often much easier to follow than writing a formula with lots of nested IF functions.

Check out our lesson on writing formulas with multiple IF functions here, and our lesson on using logical functions such as AND and OR here.





IF statements for text in mulitple cells

I am trying to build a if statement to return a value. The example I have is this.
data in cell b8=tx
data in cell b9=dobi
data in cell b10=south or north
I am looking for the formula to return a value in cell b11 that basically says if b8="tx" and b10="south"or"north", then tell me mem but if b10 doesn't = south or north and cell b9=dobi, then return prov as the value.
I can get the formula to work if I change cell b10 to yes or no with this formula. =if(and((B8="TX",B10="Yes"),"mem","prov").
Any help with this would be great

Add new comment