IF function with > but < arguments

[martialtiger]

Hey Everyone,

I've been beating my brains out with this one. I am creating a that
automatically calculates a certain percentage depending on the amount
from a particular cell.

Example
cell A1 contains the value to compare
if it is less than 25, you multiply A1 by 5%
if it is greater than 25 but less than 100, do the 1st step and then
multiply the remaining difference (A1-25) by 3% and add the total
if it is greater than 100, do the previous step plus 2% of the
remaining difference (A1-100) and add the total

Hope this makes sense.

 

[Niek Otten]

See

http://www.cpearson.com/excel/pricing.htm

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[martialtiger]

I appreciate the help. I am not understanding however to use it for th
percentages. Is there a simpler way to go about it without having t
create a separate table

 

[Jazzer]

Hi,

try something like this:

=IF(A1<25,0.05*A1,1.25)+IF(AND(A1>25,A1<100),0.03*(A1-25),IF(A1>100,2.25,0))+IF(A1>100,0.02*(A1-100),0)

This looks rude, but it works.

- Asse

 

[Greg]

Try this:

=A1*5%-MAX(0,(A1-25)*2%)-MAX(0,(A1-100)*1%)

 

[Frank Kabel]

Hi
so if I understood you the first 25$ are always multiplied with 5%, the
amount from 25$ to 100$ with 3% and only the part above 100 with 2%
Try the following formula:
=MIN(A1,25)*0.05 + (A1>25)*MIN(A1-25,75)*0.03 + (A1>100)*(A1-100)*0.02

 

[Bob Phillips]

Yet another version

=A1*2%+MIN(A1,100)*1%+MIN(A1,25)*2%

shortest so far

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[uklobster]

=IF(AND(A1>0,A1<=25),A1*0.05,IF(AND(A1>25,A1<100),(25*0.05)+((A1-25)*0.03),IF(A1>=100,(25*0.05)+(75*0.03)+((A1-100)*0.02),"ERROR"))

 

[Guest]

Try this formula, hope it helps

=IF(A1<25,(0.05*A1),IF(A1<100,((0.05*25)+(0.03*(A1-25))),((0.05*25)+(0.03*75)+(0.02*(A1-100)))))

 

[martialtiger]

You guys are awesome. Thanks for all the help