Round to nearest 0.5

Smartsheet@DevonC

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

Mehmet Zengin

Hi,

Try this,

=IF(ROUND(Dropdown7, 0) > Dropdown7, ROUND(Dropdown7, 0) - 0.5, ROUND(Dropdown7, 0) + 0.5)

CanadaJim

You can also multiply the result by two, round, and then divide by two.

Mike Wilday

@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? 

 

Mehmet Zengin

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

CanadaJim

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.

Mehmet Zengin

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.

Smartsheet@DevonC

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. 

 

 

Mike Wilday

Jim, that is a simple, excellent solution.