rounding to nearest .5 in vba
in [Programming]
|
From: Gary Keramidas on 3 Mar 2010 10:07 i'm wondering if there is a single formula that will round the following examples in vba. i can do it with if statements, but looking for a more compact solution to enter the value in a cell. if it's < .5 it rounds to .5. if it's > .5 it rounds to the nearest .5. 4.571428571 5 0.571428571 0.5 0.575428571 1 0.285714286 0.5 0.214285714 0.5 57.03571429 57 -- Gary Keramidas Excel 2003
From: JLGWhiz on 3 Mar 2010 10:39 Hi Gary, have you looked at the MROUND function. I did a quick test with it and it will round to the nearest .5 of an integer. Formula in cell B2: =MROUND(a1, .5) "Gary Keramidas" <gkeramidas(a)XXMSN.com> wrote in message news:%23HZO5MuuKHA.6064(a)TK2MSFTNGP02.phx.gbl... > i'm wondering if there is a single formula that will round the following > examples in vba. i can do it with if statements, but looking for a more > compact solution to enter the value in a cell. > > if it's < .5 it rounds to .5. if it's > .5 it rounds to the nearest .5. > > 4.571428571 5 > 0.571428571 0.5 > 0.575428571 1 > 0.285714286 0.5 > 0.214285714 0.5 > 57.03571429 57 > > > -- > > > Gary Keramidas > Excel 2003 > >
From: Rick Rothstein on 3 Mar 2010 10:42 Why do you show 0.571428571 as rounding down to 0.5 rather than rounding to 1 (which I think is then nearest .5)... this would be equivalent to your first number rounding to 5 and not 4.5. -- Rick (MVP - Excel) "Gary Keramidas" <gkeramidas(a)XXMSN.com> wrote in message news:%23HZO5MuuKHA.6064(a)TK2MSFTNGP02.phx.gbl... > i'm wondering if there is a single formula that will round the following > examples in vba. i can do it with if statements, but looking for a more > compact solution to enter the value in a cell. > > if it's < .5 it rounds to .5. if it's > .5 it rounds to the nearest .5. > > 4.571428571 5 > 0.571428571 0.5 > 0.575428571 1 > 0.285714286 0.5 > 0.214285714 0.5 > 57.03571429 57 > > > -- > > > Gary Keramidas > Excel 2003 > >
From: Paul C on 3 Mar 2010 11:01 you can call the worksheet function MROUND like this ActiveCell = WorksheetFunction.MRound(ActiveCell, 0.5) of course you can use any variables you wantand do more stuff NewNumber = WorksheetFunction.MRound(CurrentNumber, 0.5) -- If this helps, please remember to click yes. "Gary Keramidas" wrote: > i'm wondering if there is a single formula that will round the following > examples in vba. i can do it with if statements, but looking for a more > compact solution to enter the value in a cell. > > if it's < .5 it rounds to .5. if it's > .5 it rounds to the nearest .5. > > 4.571428571 5 > 0.571428571 0.5 > 0.575428571 1 > 0.285714286 0.5 > 0.214285714 0.5 > 57.03571429 57 > > > -- > > > Gary Keramidas > Excel 2003 > > > . >
From: Paul C on 3 Mar 2010 11:09
you will likely need to use a couple of statments since your conditions as described are not exactly the MROUND conditions. MRound (.212485714,.5) would = 0 Your example also appears somwhat inconsistant since .571428571 yields .5 and .555428571 yields 1. -- If this helps, please remember to click yes. "Paul C" wrote: > you can call the worksheet function MROUND like this > > ActiveCell = WorksheetFunction.MRound(ActiveCell, 0.5) > > of course you can use any variables you wantand do more stuff > > NewNumber = WorksheetFunction.MRound(CurrentNumber, 0.5) > > -- > If this helps, please remember to click yes. > > > "Gary Keramidas" wrote: > > > i'm wondering if there is a single formula that will round the following > > examples in vba. i can do it with if statements, but looking for a more > > compact solution to enter the value in a cell. > > > > if it's < .5 it rounds to .5. if it's > .5 it rounds to the nearest .5. > > > > 4.571428571 5 > > 0.571428571 0.5 > > 0.575428571 1 > > 0.285714286 0.5 > > 0.214285714 0.5 > > 57.03571429 57 > > > > > > -- > > > > > > Gary Keramidas > > Excel 2003 > > > > > > . > > |