Large Nested If Statements: how to improve?

[dccjr3927]

Hello. I searched the forum for previous posts on the topic ("nested if") and found several that might have been helpful. However, when I open the thread it is blank inside. I would like to know if there is a way to simplify my If formula/statement.

Please Login or Register  to view this content.

I apologize for the long line. I am not sure how to line break the formula. I am also attaching a sample workbook displaying functionality. I have several similar formulas (formulae?). Any assistance would be greatly appreciated.

[AliGW]

Welcome to the forum.

Rather than us having to reverse engineer a long formula, please explain in WORDS what the formula is doing (the logic).

[AliGW]

This should give you the correct answer:

=SUMPRODUCT((Fees!$B$2:$C$14)*(Fees!$A$2:$A$14=C2)*(RIGHT(Fees!$B$1:$C$1,3)=B2))*D2

Two of your original calculations seem to be taking a value from the WRONG B2 column.

[Pete_UK]

Try this:

=IF(OR($B2="B2B",$B2="B2C"),VLOOKUP($C2,Fees!$A$2:$D$14,3,)*$D2,"not defined")

Hope this helps.

Pete

[HansDouwe]

Please try in E2 and copy down:

Please Login or Register  to view this content.

[dccjr3927]

Thank you all for the assistance with this. I now have multiple ways to simplify the formula. Thanks.

[AliGW]

Glad to have helped.

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[HansDouwe]

Glad to help, thanks for your feedback and rep .

[dccjr3927]

AliGW - I arranged the columns in the test worksheet to mirror my actual sheet (I had simplified it for the post). Yours still works perfectly, while mine give the value as $0.00. I have made a copy of the worksheets from my original. I cannot figure out how I messed it up. I should not that the InvoiceExport sheet inherits values from a sheet called InvoiceImport. Not sure if that makes a difference.

HansDouwe - I also tried it your way and go no result either. I am thinking that it is the operator.

[AliGW]

E2 contains text, not a number, so there is a mismatch. Try this:

=SUMPRODUCT((Fees!$C$2:$D$14)*(Fees!$A$2:$A$14=E2+0)*(RIGHT(Fees!$C$1:$D$1,3)=N2))*D2

[dccjr3927]

That was it! Thank you, thank you, thank you!

[AliGW]

You are most welcome.

[HansDouwe]

The same will also apply to my formula. If you apply this in your last workbook, you will also have to change E2 to --E2 or 1*E2 or 0+E2 or E2*1 or E2+0