Excel formula question.?

Question

I need a formula to calculate a percentage of a number in another cell.

For example. if the number is $1000. I need the total of 30% for the first $500 and then 20% for the remaining amount. So the answer for $1000 should be $250. (if the initial amount was $1500 then the final answer I need is $350).

UPDATE: the number I need to get the percentage from will constantly be changing. So the formular needs to be general. With some sort of IF/THEN statement.

UPDATE: Sometimes the number will be less than $500

Answer 1

assume the number is in cell C8.

Then you want, in your target cell:

=(500*.3) + ((C8-500)*.2)

That'll work as long as the value in C8 is $500 or greater.

If it might be less than $500, it's more complex:

=IF(C8<500,(C8*0.3),((500*0.3)+((C8-500)*0.2)))

Answer 2

Let's say you want to calculate a sales tax for different states, compute a grade for a test score, or determine a percent change in sales between two fiscal quarters. There are several ways to calculate percentages.

Percentages are calculated by using the following equation:

amount/total = percentage

Where percentage is in decimal format.

To quickly display the result as a percentage, Click Percent Style on the Formatting toolbar.




from the help function of excel

Answer 3

=(500*0.3 + (A1-500)*0.2 )

if A1 cell contains 1000.

Edit: This only works if the value in cell A1 is greater than 500. If you aren't sure if the initial value will be greater than your 500 requirement, then it's more complicated.

=IF(A1<=500,A1*0.3,500*0.3+
(A1-500)*0.2)

(The editor keeps chopping off the last part. the above goes on one line)

Answer 4

TechnoRat's answer is very good.

But if you want to make it more robust in case you get an amount below 500 in column "A" then you'll need it like this

=IF(A1 < 500, A1*0.3, (500*0.3 + (A1-500)*0.2 ))

Answer 5

Just wanted to mention a mathematical option that does not require nasty IF nesting:

A1: $1,000.00
B1: 30% [1st percent; can be changed]
C1: $500.00 [limit; can be changed]
D1: 20% [2nd percent; can be changed]
E1: =MIN( A1, C1) * B1 + MAX( A1 - C1, 0) * D1

Answer 6

Cell A1 contains your number.

=IF(A1<=500,A1*0.3,((500*0.3)+((A1-500)*0.2)))

Answer 7

(500*30%)+((A1-500)*20%)