Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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


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
    J. Craig Williams ✭✭✭✭✭✭
    edited 12/21/15



    The easy answser's are (for row 23)


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




    =[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)




    =[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. 





  • Meagan Rodriguez
    edited 12/21/15



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

This discussion has been closed.