Multiply by percentages

I am working on a pricing sheet for quoting purposes. I have all the details ironed out except for one thing. I need to take the total price and multiply it by a surcharge of 9.5%. I have 2 cells 1 that says 9.5 and another that says 1.095. Right now I am having to multiply by the 1.095 to take effect but I want to be able to use the cell that says 9.5. One of two things is happening. The pricing is heavily inflated because it it taking the value and multiplying it by 9.5 the second thing is it is underpriced because it only shows 9.5% of the value. I want to be able to adjust this 9.5 cell in the future and use this cell as part of the formula. How do I do this? Is this possible?

Please read the yellow banner at the top of this page on how to attach a file.

Originally Posted by LVasquez

I need to take the total price and multiply it by a surcharge of 9.5%. I have 2 cells 1 that says 9.5 and another that says 1.095. Right now I am having to multiply by the 1.095 to take effect but I want to be able to use the cell that says 9.5

Originally Posted by LVasquez

I tried changing the cell from general to percentages but that changed to value of the cell from 9.5 to 950%

That tells us that the cell has the decimal value 9.5, not 0.095, which might appear to be 9.5% with the Percentage format.

Terminology is confusing. I call 9.5 "percentage points"; I call 9.5% "percentage"; and I call 0.095 "percentage as a decimal number". The last two are the same value, just with different appearances.

Ostensibly, if A1 contains the price and B1 contains the surcharge rate as "percentage points" (9.5) and you wanted to "multiply by the surcharge rate", you would write A1*B1/100 or A1*B1%.

(Many people are unaware that in Excel "%" is an operator that means "divide by 100".)

But if the correct calculation is A1*1.095, you are not simply "multiplying by the surcharge rate". Instead, you are "increasing A1 by the surcharge rate"; that is, you are "adding the surcharge to A1".

To that end, you would write A1*(1+B1/100) or A1*(1+B1%).

Be that as it may, I would encourage you to change from 9.5 "percentage points" to 9.5% or 0.095 formatted as Percentage, which is displayed as 9.5%.

Then you can simply write A1*B1 to "multiply by a surcharge rate", or A1*(1+B1) to "increase by the surcharge rate" or "add the surcharge".

The change also avoids the confusion that is causing you problems. It would cause most people problems.

PS.... And with all of these calculations, you probably should explicitly round the calculation to the cent (2 decimal places). Formatting alone only affects the appearance, not the actual value. For example, 12.34*(1+9.5%) might look like 13.51 when formatted to display 2 decimal places. But the actual value is still 13.5123. And the additional decimal fraction digits might adversely affect dependent calculations. On the other hand, ROUND(12.34*(1+9.5%),2) is exactly the same as 13.51.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.