formulas, percentage

[Aaron Holmes]

I am trying to input a formula that will work out an amount added together.
The only thing that is different is that I would like to work out different
part of the amount entered. For example if 600 is entered then in the
formula colum it works out the first 29.99 @ 5.2%, 30-99.99@3%,
[email protected]%, 200-299.99@2%, [email protected]% and over 600@1%. I also
need it the work it out accuratley if for exaple 150 is entered then the cell
would match it up to the 100 to 199.99 and work out that it needs to
calculate it and give 2.5% of 150 as the answer. Anyone help,
please!

 

[JE McGimpsey]

See

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

 

[emccorm1]

See

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



- Show quoted text -

Or modify this to fit your needs
=IF(C4<30,C4*0.052,IF(AND(C4>=30,C4<=99.99),C4*0.03,IF(AND(C4>=100,C4<=199.99),C4*0.025,IF(AND(C4>=200,C4<=299.99),C4*0.015,IF(AND(C4>=300,C4<=599.99),C4*0.015,IF(C4>=600,C4*0.01))))))

 

[JE McGimpsey]

That won't work, as it applies one percentage to the entire amount
(rather than 5.2% to the first 29.99, 3% to the next 70, etc.)

FWIW, if it DID work, there's really no reason to use the AND()s. The
only time the second IF statement would be executed if if the first
criterion returns FALSE, so

=IF(C4<30, C4*5.2%, IF(AND(C4>=30, C4<=99.99), C4*3%, ...


reduces to

=IF(C4<30, C4*5.2%, IF(C4<=99.99, C4*3%, ...

 

[emccorm1]

That won't work, as it applies one percentage to the entire amount
(rather than 5.2% to the first 29.99, 3% to the next 70, etc.)

FWIW, if it DID work, there's really no reason to use the AND()s. The
only time the second IF statement would be executed if if the first
criterion returns FALSE, so

   =IF(C4<30, C4*5.2%, IF(AND(C4>=30, C4<=99.99), C4*3%, ...

reduces to

   =IF(C4<30, C4*5.2%, IF(C4<=99.99, C4*3%, ...






- Show quoted text -

Thats what I thought, but his statement "I also
need it the work it out accuratley if for exaple 150 is entered then
the cell
would match it up to the 100 to 199.99 and work out that it needs to
calculate it and give 2.5% of 150 as the answer. Anyone help,
please! " made me think that is the way he wanted it, he kind of
contradicts himself between the first and last statements. Thanks for
shortening thast for me, I use to be pretty good with this stuff but
have been out of it for 6 years and its kinda like learning all over
again lol.