ROUND function

Hello all!

I’m having a very difficult time figuring out the ROUND function syntax.

USE CASE: I have a static spreadsheet in Excel where I use the MROUND function to make medication volumes end in either a zero (X.0), 5/10ths (X.5) or 5/100ths (X.X5) for easy administration. That formula is “=MROUND(“cell #”), 0.X” with the X having as many or as few zeroes as needed to round where I need it to. I’ve tried multiple ways to get this to work in the Editor but I can’t quite get it right. I’m attempting to emulate the spreadsheet with a live calculator in my app but I’m losing this battle.

Can someone please please help??? :pray:

Thank you!

Dustin

can you share your sheet data

Haven’t encountered MROUND before, so I had to look it up.
According to the doc, its function is slightly different from how you describe it.

That is, the 0.X in your example has nothing to do with the number of zeros, but is rather the multiple that the number should be rounded to. So for example, if you specify 0.07 then the number should be rounded to the nearest multiple of 0.07.

The ROUND function in Glide just allows you to specify the number of decimal places to round the result to, so it’s not quite the same.

To get the same as you would with MROUND will probably require a slightly more complex formula combining both MOD and ROUND.

I’ll have a fiddle with it…

I think I actually have this same problem to solve by the end of the month for my app. It might ultimately be best done with a javascript column, but I’m going to see if I can figure it out with a match column.

EDIT
So this might not be too bad, at least in my case. I’m basically doing the same thing as the javascript function in the link above, but in a math column.

A standard round should work for your X.0 case
round(value)

This should round to the nearest quarter
25*round(value/25,2)

This should round to the nearest half and would work for your X.5 case.
5*round(value/5,1)

This should work for your X.X5 case.
5*round(value/5,2)

Still wrapping my head around it, but looks promising.

1 Like

I tried just converting that JavaScript example to a math formula, and this is what I got:

Factor * Round(Number/Factor)

Screen Shot 2022-09-22 at 12.55.15 PM

Looks correct, I think? :thinking:

2 Likes

Yeah, I think I like @Darren_Murphy’s method better. Seems to make more sense than the way I did it.

1 Like

@Jeff_Hager and @Darren_Murphy - you dudes are the best!! Thank you for always coming up with the right answers, quickly!! :pray:

2 Likes

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.