MROUND to round down?
I am attempting to build a rounding formula to match FDA nutritional labeling guidelines. Using "calories" as an example, below are the rules I have to follow to build out the formula so that someone can type in the calculated value for calories in one column and get guidance on what value needs to be placed on the printed nutritional panel.
- <5 cal - express as 0
- </=50 - express to nearest 5 cal increment
- *>50 - express to nearest 10 cal increment
Let's say the input were 46, the output of the formula would need to be 45. If the input were 48, the output would need to be 50.
MROUND only rounds up to the nearest expressed increment. Is there any way around this so that I can account for instances where I need to round down to the nearest 5 or 10?
Best Answer
-
I think you were on the right track with MROUND. Here is the formula that I used to test: =IF([Primary Column]@row < 5, 0, IF([Primary Column]@row < 50, MROUND([Primary Column]@row, 5), MROUND([Primary Column]@row, 10)))
The summary for the MROUND formula is a little misleading because it only mentions that it rounds up. But what it actually does is divides your value by the selected multiple and then compares the remainder to the multiple - it rounds up if the remainder is more than half and down if the remainder is less than half. In other words, it does exactly what you are wanting it to do.
Hope this helps!
Answers
-
I think you were on the right track with MROUND. Here is the formula that I used to test: =IF([Primary Column]@row < 5, 0, IF([Primary Column]@row < 50, MROUND([Primary Column]@row, 5), MROUND([Primary Column]@row, 10)))
The summary for the MROUND formula is a little misleading because it only mentions that it rounds up. But what it actually does is divides your value by the selected multiple and then compares the remainder to the multiple - it rounds up if the remainder is more than half and down if the remainder is less than half. In other words, it does exactly what you are wanting it to do.
Hope this helps!
-
@Deric I suppose I could've tested to confirm that was the case! Thank you so much.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!