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.
Round to nearest 0.5
Hello,
We are building a time management sheet based upon a simple Smartsheet feeding information from a Form.
After we've received the information we want to round the calculated results to the nearest 0.5 (see screen capture)
The data we receive are collected and calculated in plain text/number columns.
The Round formula only allows to round up + or down to - the nearest decimal.
fx =Round([columnx]1, + 0.2 , 1)
Can someone please help me move forward?
Thanks
Comments
-
Hi,
Try this,
=IF(ROUND(Dropdown7, 0) > Dropdown7, ROUND(Dropdown7, 0) - 0.5, ROUND(Dropdown7, 0) + 0.5)
-
You can also multiply the result by two, round, and then divide by two.
-
@Mehmet. I'm not sure that your formula would work as requested. He want's the nearest .05. So that if its 9.3 it would round up to 9.5 but if its 9.1 it would round down to 9.0. See my example screenshot. The nearest .05 for 1.1 would be 1.0 and for 9.9 it would 10. Do you have a suggestion for making this work with .2 and .1 rounding down to .0 and .7, .8, or .9 rounding up to the next 0?
-
Hi Mike,
I just checked his screen shot and it looks like he is asking for the .5 all the time. For example for 7.7, desired result is 7.5 not 8.0. and for 1.3 desired result is 1.5 not 1.0.
Maybe we should wait for him to clarify this first.
And for your question I think this should work
=SUM(VALUE(LEFT(Dropdown7, FIND(".", Dropdown7, 1) - 1)), IF(OR(MID(Dropdown7, FIND(".", Dropdown7, 1) + 1, 1) = 7, MID(Dropdown7, FIND(".", Dropdown7, 1) + 1, 1) = 8, MID(Dropdown7, FIND(".", Dropdown7, 1) + 1, 1) = 9), 1, 0))
-
The simplest formula by far to round to 0.5 is the following:
=ROUND(ColumnName1 * 2) / 2
You double the number (I.e. 1.3 * 2 is 2.6) Round it (2.6 ==> 3) then you divide by 2 (3 ==> 1.5)
It will work no matter the number you are working with, and is very simple.
-
Yes Jim, I agree. Thanks for the formula.
I wrote down my formula because Mike was asking for those specific numbers. Maybe he was trying to get an idea for something else, I don't know.
-
Thanks guys,
A bit ashamed
to admit that I did not figure this out by myself but this is exactly what I'm looking for. -
Jim, that is a simple, excellent solution.
