IF function driving me insane - HELP

[kitty]

hi - i am having a really hard time with trying to find an
appropriate formula.

I basically wish to be able to enter a price and receive a
commission fee based on the price level.

here are the variables
if price from $0-$25 = 5.25% of value

if price from $25 - $1000 = 5.25% of initial $25 ($1.31) +
2.75% of remaining value

if price above $1000 = 5.25% of initial $25 ($1.31)+ 2.75%
of value from $25.01 - $1000 ($26.81) + 1.5% of remaining
value

this is the formula I last tried:
note (value is cell U27, f9 is 1000,f8 is 25, f7 is 0)

=IF(U27>$F$9,"=sum(u27-1000-25)*1.5%+sum(26.81+1.31)",IF
(U27>$F$8,"=sum(u27-25)*2.75%+1.31", IF(U27>$F$7,"=sum
(u27*5.25%)","0")))

thank you so much and please reply to my e-mail - Kitty

 

[JulieD]

Hi Kitty

=IF(A1<25,A1*5.25%,IF(A1<1000,1.31+(A1-25)*2.75%,28.12+(A1-1000)*1.5%))

hope this helps

Cheers
JulieD

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(U27>{0,25,1000}),--(U27-{0,25,1000}),
{0.0525,-0.025,-0.0125})

See:

http://www.mcgimpsey.com/excel/variablerate.html

for an explanation of how this works, along with some more flexible ways
to accomplish the same thing.

 

[Ron Rosenfeld]

hi - i am having a really hard time with trying to find an
appropriate formula.

I basically wish to be able to enter a price and receive a
commission fee based on the price level.

here are the variables
if price from $0-$25 = 5.25% of value

if price from $25 - $1000 = 5.25% of initial $25 ($1.31) +
2.75% of remaining value

if price above $1000 = 5.25% of initial $25 ($1.31)+ 2.75%
of value from $25.01 - $1000 ($26.81) + 1.5% of remaining
value

this is the formula I last tried:
note (value is cell U27, f9 is 1000,f8 is 25, f7 is 0)

=IF(U27>$F$9,"=sum(u27-1000-25)*1.5%+sum(26.81+1.31)",IF
(U27>$F$8,"=sum(u27-25)*2.75%+1.31", IF(U27>$F$7,"=sum
(u27*5.25%)","0")))

thank you so much and please reply to my e-mail - Kitty

One solution would be a lookup table.

You construct the table as follows:

0 0 5.25%
25 1.3125 2.75%
1000 28.125 1.50%


The middle row shows extra decimals as I computed the values

5.25%*25 = 1.3125

28.125 = 1.3125 + (1000-25)*2.75%

And then use the formula:

=VLOOKUP(A1,tbl,2)+VLOOKUP(A1,tbl,3)*(A1-VLOOKUP(A1,tbl,1))

This has an advantage in being relatively easy to maintain if the commission
system changes, and being easy to extend also. It is also adaptable to things
like tax rate schedules.


--ron