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