[Closed] Excel Help - Rounding

Someone who actually knows the proper Excel way of doing it will be along soon but I seem to recall getting around a similar problem with a nested IF function...

=IF(0.125<x<0.375,0.250,IF(0.375<x<0.625,0.500...etc

Does that make any sense...? (And it's not "x" you use, it's the cell reference)

As I say, someone with some real knowledge will come along and be less speculative soon!

A better way to do it would be to divide your cell by your multiple, round it to 0 decimal places, then multiply it back out.

So, if you have your value of 0.25 in cell B1 and your value in A2 then your result in B2 would be:

=ROUND(A2/B$1,0)*B$1

Clearly you could omit the $ if you don't want/need to drag the formula down. If you didn't want/need the 0.25 to be changeable then you could hard code this into the formula instead.

Hi There,

How about:
=IF(X-FLOOR(X,0.25)>0.125,FLOOR(X,0.25)+0.25,FLOOR(X,0.25))

The Floor function: Rounds number down(not necessarily to the nearest 0.25, hence the check), to the nearest multiple of significance.

Hope that its ok.

Chesrs

I'd divide the figure into multiples of .25, round then multiply back up.

eg (round(A1/.25))*.25

=MROUND(REF, 0.25)

Multiply your value by 4, round it to the nearest whole number, then divide it by 4 again.

=(ROUND((4*[i]cellref[/i]),0)/4)

or use MROUND... 🙄

the "M" stands for "Multiple". The function rounds any number to the desired multiple. The code monkey's who wrote Excel are smart like that 😉

MROUND is version specific. IIRC it's only part of the standard install in Office 2007 and newer?

it's in the 2003 Im using now. You need to turn on Analysis Toolpack in Addins though. But then thats where all the best functionality is anyway.

There you go.

Yoshimi - get a decent version of Excel!

analysis toolpack is a standard feature, just needs turning on.

Tools>Add-Ins>
then check mark the boxes for: Analysis ToolPak and Analysis ToolPak VBA

this unlocks loads of useful functions like:
ACCRINT DEC2BIN HEX2OCT ISEVEN SERIESSUM
ACCRINTM DEC2HEX IMABS ISODD SQRTPI
BESSELI DEC2OCT IMAGINARY LCM TBILLEQ
BESSELJ DELTA IMARGUMENT MDURATION TBILLPRICE
BESSELK DISC IMCONJUGATE MROUND TBILLYIELD
BESSELY DOLLARDE IMCOS MULTINOMIAL WEEKNUM
BIN2DEC DOLLARFR IMDIV NETWORKDAYS WORKDAY
BIN2HEX DURATION IMEXP NOMINAL XIRR
BIN2OCT EDATE IMLN OCT2BIN XNPV
COMPLEX EFFECT IMLOG10 OCT2DEC YEARFRAC
CONVERT EOMONTH IMLOG2 OCT2HEX YIELD
COUPDAYBS ERF IMPOWER ODDFPRICE YIELDDISC
COUPDAYS ERFC IMPRODUCT ODDFYIELD YIELDMAT
COUPDAYSNC FACTDOUBLE IMREAL ODDLPRICE
COUPNCD FVSCHEDULE IMSIN ODDLYIELD
COUPNUM GCD IMSQRT PRICE
COUPPCD GESTEP IMSUB PRICEDISC
CUMIPMT HEX2BIN IMSUM PRICEMAT
CUMPRINC HEX2DEC INTRATE RECEIVED

it's in the 2003 Im using now. You need to turn on Analysis Toolpack in Addins though. But then thats where all the best functionality is anyway.

For something as simple as this my feeling is that it's best kept generic so that it's portable without the user(s) having to do anything (like wondering why their spreadsheet is suddently full of #NAME? cells). This makes ROUND a better option than MROUND.

(round(A1/.25))*.25

ROUND also needs the number of decimal places to be specified, so would be:

=ROUND(A1/.25,0)*.25

any Co IT policy that doesnt install toolpack as a matter of course should be given the long walk and a last cigarette IMO! They're probably the same kind of companies who let their secretaries use Excel for doing tables 😉

any Co IT policy that doesnt install toolpack as a matter of course should be given the long walk and a last cigarette IMO

😆

Sorted

Stoner, I had looked at that MROUND but couldn't get it to work and was left confused so I used -m-'s =ROUND(A2/B$1,0)*B$1

But after coming back here I turned on those add-ins and now MROUND works and looks much neater 🙂

Thanks all

Prego.

😛 @ -m-