SOLVED

# Improving IF formula

Brass Contributor

# Improving IF formula

Hello guys,

is it possible to write this formula in a simpler way? I use it in a spreadsheet multiple times in multiple Sheets and from time to time I get the error: Excel ran out of resources (on MAC) and therefore, calculations are not being made properly

I attach one small excel file as example. I think this is the best way for you to see.

Basically, Sheet"model" cell C10 needs to calculate taking in consideration the following rules:

Rules can't change... the ouput must follow the same rules even if the formula is different.

=IF(AND(D10>0,\$C\$5>=DATE!\$B\$3,\$C\$5<DATE!\$C\$3),D10,
IF(AND(D10>=0,\$C\$5>=DATE!\$C\$3,\$C\$5<DATE!\$D\$3,\$C\$5=DATE!\$C\$3),0,
IF(AND(D10>0,\$C\$5>=DATE!\$C\$3,\$C\$5<DATE!\$D\$3),D10,
IF(AND(D10>=0,\$C\$5>=DATE!\$D\$3,\$C\$5<DATE!\$E\$3,\$C\$5=DATE!\$D\$3),0,
IF(AND(D10>0,\$C\$5>=DATE!\$D\$3,\$C\$5<DATE!\$E\$3),D10,
IF(AND(D10>=0,\$C\$5>=DATE!\$E\$3,\$C\$5<DATE!\$F\$3,\$C\$5=DATE!\$E\$3),0,
IF(AND(D10>0,\$C\$5>=DATE!\$E\$3,\$C\$5<DATE!\$F\$3),D10,
E10)))))))

Alecs

4 Replies

# Re: Improving IF formula

This appears to produce the same result:

=IF(E5>0,IF(OR(\$C\$5=DATE!\$C\$3:\$E\$3),0,IF(AND(\$C\$5>=DATE!\$B\$3,\$C\$5<DATE!\$F\$3),E5,F5)),F5)

# Re: Improving IF formula

thank you for your reply! this is very good but there is only one rule that is missing:

From my initial formula, second row:

IF(AND(E5>=0,\$C\$5>=DATE!\$C\$3,\$C\$5<DATE!\$D\$3,\$C\$5=DATE!\$C\$3),0,

Can you please take a look? I tried a few combinations but it returns FALSE instead of 0. It is beyond my understanding...

I attach the excel file.

Here is the scenario:

DATE sheet:

MODEL sheet:

If today's date is = to DATE 1,2,3,4 or 5 and cell E5 = 0 , and F5 > 0, the end result must be 0 , not 50

Thank you very much

Alecs

best response confirmed by Alecs (Brass Contributor)
Solution

# Re: Improving IF formula

Managed to do it by adding the condition in front. Now all is oki 🙂
Thanks a lot for all the help provided! I will replace this formula in my entire workbook

=IF(AND(E5>=0,OR(\$C\$5=DATE!\$C\$3,\$C\$5=DATE!\$D\$3,\$C\$5=DATE!\$E\$3,\$C\$5=DATE!\$F\$3)),0,IF(E5>0,IF(OR(\$C\$5=DATE!\$C\$3:\$E\$3),0,IF(AND(\$C\$5>=DATE!\$B\$3,\$C\$5<DATE!\$F\$3),E5,F5)),F5))
1 best response

Accepted Solutions
best response confirmed by Alecs (Brass Contributor)
Solution