Member 328449 Prominent Member Joined Aug 31, 2007 Messages 3,464 Reaction score 346 Points 940 Location Kent Oct 23, 2015 #1 Hoping this may be quite simple. I have a list of numbers but I want them to be rounded to the nearest 0.5. Is this possible somehow? In format cell I can chose one decimal place but that does to the nearest 0.1
Hoping this may be quite simple. I have a list of numbers but I want them to be rounded to the nearest 0.5. Is this possible somehow? In format cell I can chose one decimal place but that does to the nearest 0.1
Q qwerty321 Distinguished Member Joined Mar 20, 2009 Messages 8,046 Reaction score 1,496 Points 1,410 Oct 23, 2015 #2 Try: =MROUND(<cell ref>, <amount>) So like: =MROUND(A1, 0.5)
Member 328449 Prominent Member Joined Aug 31, 2007 Messages 3,464 Reaction score 346 Points 940 Location Kent Oct 23, 2015 #3 Seems to work, only thing is the cells which are blank return the #Value error which I can't seem to get rid of.
Seems to work, only thing is the cells which are blank return the #Value error which I can't seem to get rid of.
Elrond Prominent Member Joined Apr 4, 2007 Messages 4,023 Reaction score 1,169 Points 906 Oct 23, 2015 #4 Use an IF statement to only carry out the rounding if the cell has a value, else output an empty string.
Use an IF statement to only carry out the rounding if the cell has a value, else output an empty string.
Member 328449 Prominent Member Joined Aug 31, 2007 Messages 3,464 Reaction score 346 Points 940 Location Kent Oct 23, 2015 #5 I thought that but can't work it out. Can you spell it out for me please? I'm a bit tired and can't get it right.
I thought that but can't work it out. Can you spell it out for me please? I'm a bit tired and can't get it right.
Elrond Prominent Member Joined Apr 4, 2007 Messages 4,023 Reaction score 1,169 Points 906 Oct 23, 2015 #6 Something like: =IF(<cell ref> != "", MROUND(<cell ref>, 0.5), "") Which basically means - IF( condition, [value_if_true], [value_if_false] ) That is based on the assumption that the cell you want to round is blank. If you want more explanation - https://support.microsoft.com/en-us/kb/214244, although bear in mind that the example evaluates an integer value. Or - MS Excel: IF Function (WS)
Something like: =IF(<cell ref> != "", MROUND(<cell ref>, 0.5), "") Which basically means - IF( condition, [value_if_true], [value_if_false] ) That is based on the assumption that the cell you want to round is blank. If you want more explanation - https://support.microsoft.com/en-us/kb/214244, although bear in mind that the example evaluates an integer value. Or - MS Excel: IF Function (WS)
Member 328449 Prominent Member Joined Aug 31, 2007 Messages 3,464 Reaction score 346 Points 940 Location Kent Oct 23, 2015 #7 Spot on, thanks.