IF function driving me insane - HELP

  • Thread starter Thread starter kitty
  • Start date Start date
K

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
 
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
 
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
Click to expand...

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
 
Back
Top