Combining Multiple IF Statement with AND and OR

Topic

New Reply

I’m looking for a little help combining multiple IF statements. I don’t have a lot of experience with this, so I created a table with each of my conditions to help. I can get a single conditions and the desired results to work; however, when I attempt to combine the statements, I cannot get my syntax correct. Excel will accept my formula, but it returns a #Value error in the cell. I’ve done some research, but since Excel is not showing a formula error, I’m have a hard time isolating what part(s) of the formula is wrong.

Here’s what I’m trying to accomplish.

Condition 1: If A1 is greater than today; E1 is blank

Condition 2: If A1 equals to today AND B1, C1 and D1 (all 3) are blank, E1 is 10

Condition 3: If A1 equals to less than today and B1, C1 or D1 (either of the 3) are NOT blank, E1 is 20; otherwise E1 is 30

Here’s my formula:
=IF(B31>TODAY(),””),IF((AND(B31=TODAY(),C31=””,D31=””,E31=””,)),10),IF(OR(B30>=TODAY(),C30″”,D30″”,E30″”,),20,30)

43171-IF

Reply | Quote

Replies

JAATR,

I think this fixes the formula but I’m not sure what you are trying to accomplish:

[noparse]=IF(B31>TODAY(),””,IF(AND(B31=TODAY(),C31=””,D31=””),10,IF(OR([/noparse][noparse]B30>=TODAY()[/noparse][noparse],C30″”,D30″”,E30″”),20,30)))[/noparse]

The term in blue does not make sense based on your example as that column would not contain a date! Likewise, I deleted the term [noparse]E31=””[/noparse] as that is the cell containing this formula!

43181-JAATR

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RetiredGeek

Thanks for your help. Your formula worked perfectly.

Reply | Quote

Hi Jaatr

I’m have a hard time isolating what part(s) of the formula is wrong.

..put the cellpointer in the cell that has a complex formula. In the formula bar, use the mouse (or whatever) to highlight some part of the formula sections, press [F9] and it will calc that particular section. You can use the calc button in this way to check which parts of the formula give the expected results.

zeddy

Reply | Quote