Forum Discussion
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)))))))
Many thanks in advance! 🙂
Alecs
Stay with your original 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)
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
Stay with your original formula.
