Rounding of whole numbers; not decimals 1

[BigH]

You can control the "accuracy" of a cell value using the decimal point, say round off 5.66 to 5.7. What if I have a number computed to be 126 and I want to "round" off to the nearest 10, or 25 (e.g., 130 or 125). How can I do that? - e.g., to keep the same number of significant digits.

Thanks in advance.

 

[Flareman]

For the cell, use a formula like +N*round(CELLREF/N,0)
or N*int(CELLREF/N) or N*int(CELLREF/N +1)where CELLREF is the variable to display and N is the number you want round to.
[SMILE]
David

 

[Flareman]

Sorry!
the last formula in my previous post is junk.
Disregard it.
David

 

[iken]

Here's something to work with.
The MOD, CEILING and FLOOR functions are all standard Excel functions.

=IF(MOD(B1,E1)>E1,CEILING(B1,E1),FLOOR(B1,E1))

Cell B1 is your data input
Cell E1 is what you round to (ie, 5 25, 132 etc

Have a play and enjoy


Iken

 

[iken]

Ooops,

Should be this,

=IF(MOD(B1,E1/2)>E1,CEILING(B1,E1),FLOOR(B1,E1))

Note the "/2" in MOD brackets, this will ensure it rounds up if number is greater than half of specified "round to paramiters" or rounds down if number is less than half of specfied "round to parameters".

Further to this, if your round to parameters are going to be constant all the time, you can just insert this number where you see "E1" in the above instead of a cell ref.

iken

 

[iken]

Sorry, this time!

=IF(MOD(B1,E1)>E1/2,CEILING(B1,E1),FLOOR(B1,E1))

 

[kayah]

Or you could install the "Analysis TookPak" Addin (Tools - Add-Ins... - Check "Analysis ToolPak" & Click "OK&quot - a standard excel add-in as far as I am aware.

Then you can use the function MROUND - which lets you round to multiples. eg. =MROUND(A1,25)

 

[Denial]

You should also bear in mind the distinction between how a number is STORED and how it is PRESENTED.

The original post talked about using formatting to control how the number was presented, but the actual number stored (and therefore the number that would actually be used in any calculations that used the cell concerned) would be unchanged.

On the other hand, use of the ROUND() function, and the various uses of the MOD() function suggested in later posts, will actually change the value that is stored in the cell.

Also be aware that is is perfectly valid to use a negative value as the second argument to the ROUND() function, in order to round the number to a multiple of ten. For example ROUND(123.45,-1) will return the value 120.

 

[BigH]

Denial - thanks for your comments. You are right; I don't want to change the number stored, only how presented.

Thanks for the other tips, though, all. So, still, how . . . ?

 

[yakpol]

The solution seems to be straight forward:
A_final = Precision*ROUND(A_original/Precision,0)

 

[lilliput1]

Go back to the 1st answer but let me present it clearer. Say the quantity is in cell B1 & you want to round it off to the nearest 5:

Use this formula:
=round(B1/5,0)*5

if you want it rounded to nearest 25 use:
=round(B1/25,0)*25
if you want it rounded up to the next 25 use:
=roundup(b1/25)*25

etc.

 

[wahmit]

BigH,

The previous correspondence refers to rounding a value to a specific number; the last part of your question refers to rounding to a number of significant figures. I use the following custom function to do this;

USE
RndSigFigs(n,i) Rounds a number to a number of significant figures

x = RndSigFigs(number, number of significant figures)

i.e. RndSigFigs(12345,3) = 12300
and RndSigFigs(1246800,4) = 1247000

CODE
' +--------------+
' | RndSigFigs |
' +--------------+
'
' Macro written by Tim Haw
' To round numbers to a specified number of significant figures

Function RndSigFigs(number As Double, SigFigs As Single) As Variant

Dim count As Integer, SigFigsTemp
Dim msg As String

If SigFigs <> Int(SigFigs) Then
SigFigs = Int(SigFigs + 0.5)

msg = &quot; Warning from RndSigFigs macro function.&quot; & Chr(10) & Chr(10)
msg = msg & &quot; Rounding to &quot; & SigFigs & &quot; significant figures&quot;
MsgBox (msg)
End If

count = SigFigs - Int(Log10(number)) - 1
RndSigFigs = Int(number * (10 ^ count) + 0.49999) / (10 ^ count)

End Function

 

[BigH]

Thanks, I'll try it but can you use, say a formula instead of a number, say &quot; @sum(c1.c23) &quot; or =b23*c14/a15?

i.e., RndSigFigs(@sum(c1.c23),4) ?
RndSigFigs(=b23*c14/a15,4) ?

 

[wahmit]

Yes you can use formulae in the function, i.e.

= RndSigFigs(SUM(C1:C21),4) or even

= RndSigFigs(C1/C2,INT(B1/B2))


I’ve just noticed that the RndSigFigs uses another custom function, Log10. You will also need to add this to the module.

' +---------+
' | Log10 |
' +---------+
'
' Macro writen 6.4.98 by Tim Haw
' To determine log to base 10
'

Static Function Log10(X As Double) As Double
Log10 = Log(X) / Log(10)
End Function

 

[lilliput1]

You can also use the Round(no,-integer) function.
Where values of the -interger =:
-1 to round in tens
-2 to round in hundreds
-3 to round in thousands
etc