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
Comments
-
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
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 60 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 40 Webinars
- 7.3K Forum Archives