Single-cell formula to figure multiple percentages

[Ross]

Please help! I have an amount in cell A1 and I need a formula in B1 that will
give me 10% of the first 50, then 5% of anything over that and sum the total.
If the original amount is $100, I should get an answer of $107.50. Any
ideas??

Thanks

 

[John C]

=MAX(A1,50)*1.1+MAX(0,(A1-50)*1.05)

 

[John C]

Whoops....make that
=MIN(A1,50)*1.1+MAX(0,(A1-50)*1.05)

 

[Satti Charvak]

to help you understand i 'll try:

=IF(A1<=50,A1+A1*10%,A1+(50*10%)+((A1-50)*5%))

or

in shorter form

=IF(A1<=50,A1+A1*10%,A1+5+((A1-50)*5%))

 

[Ross]

It works! Thanks much
--
smither fan

Whoops....make that
=MIN(A1,50)*1.1+MAX(0,(A1-50)*1.05)

 

[Ross]

This works, also. Thanks!!
--
smither fan

to help you understand i 'll try:

=IF(A1<=50,A1+A1*10%,A1+(50*10%)+((A1-50)*5%))

or

in shorter form

=IF(A1<=50,A1+A1*10%,A1+5+((A1-50)*5%))

--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India

 

[Ross]

John-I left feedback, although I don't see it yet. Can you explain the
formula to me as I am very new.

Thanks again!

 

[John C]

Sure.
MIN(arg1,arg2,arg3,...) comes up with the Minimum value for the argument(s)
for it, likewise, MAX will come up with the Maximum arguments for it.
Your request was to take an amount, and increase it by 10% for the first 50,
and by 5% above and beyond 50.
Well, my formula:
MIN(A1,50) will take the minimum, of A1 or 50, and multiply it by 1.1 (or
itself and 10%). If A1>50, then 50 is the minimum, and this portion will
calculate to MIN(A1,50)*1.1 = 50*1.1 = 55. Likewise, if A1<50, then = 34*1.1
= 37.40. So that handles the first part of your request.
Next up, MAX(0,(A1-50)*1.05)
Well, if A1<50, such as 34 earlier, then we are looking for the maxium value
between 0 and (34-50)*1.05. The second argument will evaluate to a negative
number, and therefore, 0 is the maximum amount. However, if A1>50, then the
second argument will evaluate to a positive amount, and since I subtract it
by 50, then I multiply it by 1.05 (or itself and 5%). Therefore, your
original example of 100
=MIN(100,50)*1.1+MAX(0,(100-50)*1.05)
=50*1.1+MAX(0,50*1.05)
=55+52.5=107.50
Hope that helps

 

[John C]

One more side note. My formula assumes that A1 will never be a negative
number. If it is possible for A1 to be a negative number, but you don't want
to modify the negative number by 10%, you would need to nest the initial MIN
statement with a MAX statement, and your final formula would be:
=MAX(0,MIN(A1,50)*1.1)+MAX(0,(A1-50)*1.05)

 

[Ross]

John-Thanks for the explanation! I'm wondering if there is a way to get the
formula to return whatever's in the cell when it's a negative?? Right now,
it's returning a 0.

 

[John C]

=MAX(0,MIN(A1,50)*1.1)+MAX(0,(A1-50)*1.05)+(A1<0)*A1