Nested if statements - is there a better way?
 

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!

Nested if statements - is there a better way?
 

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

--
HTH

Bob Phillips

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

"masterbaker" wrote in message
...
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!

Nested if statements - is there a better way?
 

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


--
Regards

Roger Govier


"masterbaker" wrote in message
...
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!

Nested if statements - is there a better way?
 

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 a
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" wrote:

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


--
Regards

Roger Govier


"masterbaker" wrote in message
...
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!