Improving IF formula

[Alecs]

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

[#2]

@Alecs 

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)

[#3]

@HansVogelaar 

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

[#4 · Accepted solution]

@Alecs 

Stay with your original formula.

[#5]

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))