Nested IF statements

[tkessler]

I have a problem with creating a formula in a worksheet where I have to calculate dues payments based on sales volumes.


Lets say I have 12 different dues calculations that start like:

Sales @ $5,000,000 or less they pay $975 and the sales volume is listed in cell A1 the formula would be: IF(A1,=5000000,975)

The next scenario would be Sales from $5M to $25M and they would pay $975 + $45 for each $1M over $5M the formula would be: =((A1-5000000)/1000000*45)+975

The next scenario would be sales form $25M to $50M and they would pay $1875 + $38 for each 1M over $25M the formula would be: =((A1-25000000)/1000000*38)+1875


I have twelve of thess formulas that I need to take into account. So if I have rows of Companies with various sales volumes I need to have a formula that looks at the sales volume and then does one of the calculations bases on their sales. I am struggling with how to do this. It is too many arguements for a nested IF statement.

Help.

[tigeravatar]

tkessler,

Attached is an example workbook based on the sample data you provided. It contains a helper table in columns E:F that has the Min - Max threshold ranges, what the base pay is and the + per Mil rate is for each threshold. Column A contains random numbers between 4 million and 50 million. Column B contains the formula:

Please Login or Register  to view this content.

Adjust the upper bounds as necessary as you increase the helper table.

Hope that helps,
~tigeravatar

[Miraun]

Hey Tkessler;

You're absolutely right, if you're working off of nested IF statements, it will be beyond the limitations of even 2007. You have a few options open to you. They are completely dependent on how volatile these equations can be.

1) You can use the Lookup function based off of the different values, and assign a unique identifier to identify the equation to use. For more information on the lookup function, here's a great thread:
http://www.excelforum.com/excel-2007...with-text.html

2) Paired with the lookup function, you can use Vlookups to an off-page containing the equations, multipliers, additives, etc... Another reference for you
http://www.excelforum.com/excel-gene...ml#post2544811


ALTERNATIVELY.

You can not nest IF statements!
Consider the conditions that you're working with, and you can break it up with adding multiple IF statements.
=If(A2=<5000000,925,0) + If(And(A2>5000000,A2<250000000,925+.01*A2,0)+IF(And(......
(Honestly, I didn't go through your examples, but if you were nesting IFs, I'm sure you get the idea.

Hope this helps, and good luck.