Nested if statements - is there a better way?

[Guest]

Here's the deal:
Think of this as a royalties issue. Depending on the sales for a specific
month, the percentage of royalties due will change. Say January sales were
$1,850,00 (A1). Then the royalties would be 3%:

Sales less than: Royalty %:
$1,000,000 1%
$1,700,000 2%
$1,800,000 3%
$1,900,000 4%
$2,000,000 5%
.....and so on

I am tired of nesting my if statments and am wondering if there is some
other formula or something that would work? (if(A1<=2Mill, 5%, if(A1<1.9Mill,
4%.......))

Please help me unnest my if statements!

 

[Bob Phillips]

=VLOOKUP(A1,{0,0.01;1000001,0.02;1700001,0.03;1800001,0.04;1900001,0.05;2000
001,0.06},2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Roger Govier]

Hi

If the increments go up by 1% with each additional $100,000 sales value,
then you could solve it with a simple formula.
Put your Trigger value of $1,500,000 in say D1 and the base of
$1,000,000 in E1 and use the following formula (assuming sales in A1).
The Trigger is set at 1,500,000 to give the starting 2% value when
compared with your 1,700,000.

=MAX(A1*1%,A1*((A1-$D$1)/$E$1))

You don't say what happens to the value between $1,000,000 and
$1,699,999.
If the base of 1% is held on only up to $1M worth of sales unless the
$1.7M is reached, then modify the formula to

=MAX(MIN(A1,$E$1)*1%,A1*((A1-$D$1)/$E$1))

 

[Guest]

Thanks Roger, a step in the right direction but...

Your equation works as long as my increments are the same, but they are not.
I think essentially what I need is something that works like the max function:

D1 is equal to 14

A1:B4 values are:
5 1%
7 2%
13 3%
19 4%
22 5%

I want an equation that will lookup the max number from A1:B4 that is less
than the value in D1 (14) and return the corresponding percentage. The
equation I bassicaly need is: Vlookup(Max(A1:B4)<=D1, 2, false) but the max
function only returns a false because the max of A1:B4 is not less that D1.

Any more help would be appreciated!!!!

 

[Roger Govier]

Hi

Bob Phillips gave you a solution using Vlookup, but all the values were
in an array within the formula, based upon the values set in your
original posting.

However, to use lookup table outside of the array, we can achieve what
you want without the use of the Max() function, by making use of a
feature of Vlookup without using the 4th argument of False.

Set up a table in A1:B6 with the following values

0 1%
5 1%
7 2%
13 3%
19 4%
22 5%

Then use the formula
=D1 * VLOOKUP(D1,$A$1:$B$6,2)

Without the False argument, instead of giving a #N/A result, the formula
will return the value from column B of the last value found which is
less that the value in D1.
For this to work, it needs the 0 value to produce results fro any
possible value in D1 which is less than 5.

With the set of values used here, substituted in Bob's formula, the same
result would be achieved.