Calculate Percentage Based On Number

[GoodTrouble]

I tried asking once, but I think I was misunderstood...so I will try again...

Cell A1 has a number, we'll say $4,000

Cells A3:A6 will contain the calculations as follows:

The figure in A1 will need to have various percentages taken of it...

From 0-2,500 the percentage is 25%
From 2,501 - 3,500 the percentage is 30%
From 3,501 + the percentage is 35%

Then all three resulting figures will be added up, so in this example it
should come out as follows:

A3=$625
A4=$299.7
A5=$174.65
A6=$1,099.35

It is possible that A1 could be less than 2,500, or in between any of those
figures...

Is this at all possible?

 

[MrAcquire]

Assuming that A1 contains your number, try entering the following formulas in
A3, A4, A5, and A6.

A3: =MIN(A1,2500)*0.25
A4: =MAX(0,MIN(A1-2500,999))*0.3
A5: =MAX(0,A1-3501)*0.35
A6: =SUM(A3:A5)

Is that what you were trying to accomplish?

 

[GoodTrouble]

YES! Perfect! Thank You So Much!

Can I ask that the "999" is for in the A4 formula?

Thanks again!

 

[MrAcquire]

999 is the maximum that you'd ever multiply by 0.3.

Just out of curiosity, I'm wondering if your thinking is flawed and that A5
should really be =MAX(0,A1-3500)*0.35 instead of =MAX(0,A1-3501)*0.35.
Otherwise an A1 of 3500 or 3501 will produce the same result. Thus for A1 of
4000, A5 would be 175 instead of the 174.65 that you claim it should be.

Since I'm not sure what your application is for this calculation, you'll
have to decide which one is best.

 

[GoodTrouble]

You are correct again, that does seem to be a better way of doing it...I
changed a couple of the other ones after going back and trying some different
scenarios...thanks for pointing that out!

I have another one for you if you don't mind...

Say Cell A16 contains a percentage...(average)
And A17 contains another percentage...(actual)

Now in cell A18 I would like excel to enter one of three numbers based on
evaluating A16 and A17...If A17 is <A16 then enter 100, if A17 is =A16 then
enter 150, and if A17 is >A16 then enter 200

For instance...
A16 is 97
A17 is 100

Therefore A17 is higher and 200 would be entered into A18

Can you think of a way to do this?
Thank you again for all your help!

 

[MrAcquire]

Actually, upon further consideration, I think A4 should be changed also to
=MAX(0,(MIN(A1-2501,1000))*0.30 so that 3499 and 3500 don't produce the same
result either.

 

[MrAcquire]

=IF(A17<A16,100,IF(A17=A16,150,200))

You are correct again, that does seem to be a better way of doing it...I
changed a couple of the other ones after going back and trying some different
scenarios...thanks for pointing that out!

I have another one for you if you don't mind...

Say Cell A16 contains a percentage...(average)
And A17 contains another percentage...(actual)

Now in cell A18 I would like excel to enter one of three numbers based on
evaluating A16 and A17...If A17 is <A16 then enter 100, if A17 is =A16 then
enter 150, and if A17 is >A16 then enter 200

For instance...
A16 is 97
A17 is 100

Therefore A17 is higher and 200 would be entered into A18

Can you think of a way to do this?
Thank you again for all your help!

 

[GoodTrouble]

Yes, you are right, that was one of the others I changed...

Did you see my other question?

"Say Cell A16 contains a percentage...(average)
And A17 contains another percentage...(actual)

Now in cell A18 I would like excel to enter one of three numbers based on
evaluating A16 and A17...If A17 is <A16 then enter 100, if A17 is =A16 then
enter 150, and if A17 is >A16 then enter 200

For instance...
A16 is 97
A17 is 100

Therefore A17 is higher and 200 would be entered into A18

Can you think of a way to do this?
Thank you again for all your help! "

 

[MrAcquire]

=IF(A17<A16,100,IF(A17=A16,150,200))

Yes, you are right, that was one of the others I changed...

Did you see my other question?

"Say Cell A16 contains a percentage...(average)
And A17 contains another percentage...(actual)

Now in cell A18 I would like excel to enter one of three numbers based on
evaluating A16 and A17...If A17 is <A16 then enter 100, if A17 is =A16 then
enter 150, and if A17 is >A16 then enter 200

For instance...
A16 is 97
A17 is 100

Therefore A17 is higher and 200 would be entered into A18

Can you think of a way to do this?
Thank you again for all your help! "