Expansion to existing nested IF formula not working

[kohlo]

Posting on behalf of a coworker who is trying to automate some price/margin checks. It's in a Google Sheet but as far as I know this should also work in Excel.

The current formula we have in place that is working checks whether there is a "-" in column O which means there's a specific set of calculations to do, and if there's no "-" it will execute the latter part of the formula.

=ARRAYFORMULA(if(A2:A="","",if(O2:O<>"-",if(E2:E>300,F2:F*1.13,F2:F*1.115+2),if(E2:E>300,F2:F*1.082,F2:F*1.115+2))))

It needs to be expanded to this:

=ARRAYFORMULA(if(A2:A="","",if(O2:O<>"-",if(E2:E>300,F2:F*1.12,if(E2:E<300,F2:F*1.13,if(E2:E<140,F2:F*1.12+2.5,if(E2:E<110,F2:F*1.125+3,if(E2:E<70,F2:F*1.13+2.5,if(E2:E<40,F2:F*1.13+2.5,if(F2:F*1.13+2),if(E2:E>300,F2:F*1.082,F2:F*1.115+2))))))))

It's basically the same formula with bith more extensive price calculation tiers. For some reason this new expanded formula doesn't work, it give either 'error' or 'false' depending on the mood of the sheet. Can anyone see what is going wrong here?

 

[Fluff]

It's in a Google Sheet

In future please post such questions in the General Discussion & Other Applications section of the board. I have moved it for you this time.

as far as I know this should also work in Excel.

Nope, There is no such function as ARRAYFORMULA in Excel.

 

[Fluff]

When checking if a value is below a target, you must start with the smallest value 1st, in this bit if(E2:E<300,F2:F*1.13,if(E2:E<140,F2:F*1.12+2.5,it will never test if E is <140 simply because anything that is lees then 140 is also <300

Towards the end of the formula you have if(F2:F*1.13+2)which makes no sense as you have not included either a true of false argument for the IF

Finally at the end of your formula you have if(E2:E>300,F2:F*1.082,F2:F*1.115+2)but you have already checked if E >300 so this is redundant.

 

[kohlo]

Thank you for your reply! I forwarded it to my coworker and he made a few changes, it now half-works:

=ARRAYFORMULA(if(A2:A="","",if(O2:O<>"-",if(E2:E>300,F2:F*1.12,if(E2:E<10,F2:F*1.13+2,if(E2:E<40,F2:F*1.13+2.5,if(E2:E<70,F2:F*1.125+2.5,if(E2:E<110,F2:F*1.1125+3,if(E2:E<140,F2:F*1.12+2.5,F2:F*1.13,if(E2:E>300,F2:F*1.082,F2:F*1.115+2))))))))))

The <> makes the formula work for the rows that don't have "-" in column O, however it doesn't fire a calculation for when there is a "-" in column O. He expects this last bit to fire in that scenario:

if(E2:E>300,F2:F*1.082,F2:F*1.115+2)

Any ideas on how to make this work?

 

[Fluff]

You are still testing if E>300 twice in the formula, so the 2nd one will never be used.

 

[kohlo]

It's supposed to go like this:

If O does not equal "-" do the string of calculations with the first (E2:E>300, and if O does equal "-" do the 2nd (E2:E>300. Has he not split the formula up correctly to do this?

 

[Fluff]

Try it like

=ARRAYFORMULA(IF(A2:A="","",IF(O2:O="-",IF(E2:E>300,F2:F*1.082,F2:F*1.115+2),IF(E2:E>300,F2:F*1.12,IF(E2:E<10,F2:F*1.13+2,IF(E2:E<40,F2:F*1.13+2.5,IF(E2:E<70,F2:F*1.125+2.5,IF(E2:E<110,F2:F*1.1125+3,IF(E2:E<140,F2:F*1.12+2.5,F2:F*1.13)))))))))

 

[kohlo]

That did the trick, thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.