How do I create a formula that will subtract a percentage from another cell

Meagan Rodriguez

If cell B2 in column titled "Total Cost" is $25,000.00, and I need to subtract 6.35%, what formula would help me do this?

J. Craig Williams

Meagan,

 

The easy answser's are (for row 23)

 

=[Total Cost]23 - ([Total Cost]23 * 0.0635)

 

or

 

=[Total Cost]23* 0.9365)

 

Both work, the first should be easier to read and understand where the number came from. 

 

Or in other words

 

Discounted_Cost = Total_Cost - (Total_Cost * 0.0635) 

Discounted_Cost = Total_Cost * (1 - 0.0635) = Total_Cost * (0.09365)

 

Longer, more flexible answer:

 

However, if the discount is not always 6.35%, then you could add a column for the Discount Percentage rate.

 

Then the formulas would be:

 

=[Total Cost]23 - ([Total Cost]23 * [Discount Percentage]23)

 

or 

 

=[Total Cost]23 * (1 - [Discount Percentage]23)

 

Note: If the Discounted Percentage column is formated for % (using the % icon), then the value is 1 when the percentage is 100%.

If you enter 6.35 and it shows as 6.35%, then you are using the % formatting.

If not, then it shows at 6.35 -- then the value is 6.35 and not 0.0635.

So then you need to be aware that your 100% is the value 100, not 1.

 

That is, entering 6.35 in a column with normal number formatting results in 6.35.

Entering 6.35 in a column with % formatting results in 0.0635

You need to know which formatting you are using to get the formula right

Hope that helps and is clear.

It is pretty easy, but I'm not sure I explained it clearly. 

 

Craig

 

 

Meagan Rodriguez

Craig,

 

Thank you very much for your help with this.  It's much appreciated, and the boss is happy  I used the first formula at the top.  Also, thank you for including tips for if the percentage ever happens to change.