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
--
smither fan

=MAX(A1,50)*1.1+MAX(0,(A1-50)*1.05)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Ross" wrote:

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
--
smither fan

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

"Ross" wrote:

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
--
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" wrote:

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
--
smither fan

It works! Thanks much
--
smither fan


"John C" wrote:

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

"Ross" wrote:

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
--
smither fan

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


"Satti Charvak" wrote:

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" wrote:

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
--
smither fan

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!
--
smither fan


"John C" wrote:

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

"Ross" wrote:

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
--
smither fan

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 A150, 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 A150, 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 **


"Ross" wrote:

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!
--
smither fan


"John C" wrote:

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

"Ross" wrote:

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
--
smither fan

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)

--
** John C **


"Ross" wrote:

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!
--
smither fan


"John C" wrote:

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

"Ross" wrote:

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
--
smither fan

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.
--
smither fan


"John C" wrote:

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)

--
** John C **


"Ross" wrote:

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!
--
smither fan


"John C" wrote:

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

"Ross" wrote:

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
--
smither fan

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


--
** John C **


"Ross" wrote:

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.
--
smither fan


"John C" wrote:

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)

--
** John C **


"Ross" wrote:

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!
--
smither fan


"John C" wrote:

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

"Ross" wrote:

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
--
smither fan