MROUND to round down?

Katy H

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?

Deric

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 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!

Katy H

@Deric I suppose I could've tested to confirm that was the case! Thank you so much.