Rounding of whole numbers; not decimals
Rounding of whole numbers; not decimals
(OP)
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.
Thanks in advance.





RE: Rounding of whole numbers; not decimals
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.
David
RE: Rounding of whole numbers; not decimals
the last formula in my previous post is junk.
Disregard it.
David
RE: Rounding of whole numbers; not decimals
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
RE: Rounding of whole numbers; not decimals
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
RE: Rounding of whole numbers; not decimals
Sorry, this time!
=IF(MOD(B1,E1)>E1/2,CEILING(B1,E1),FLOOR(B1,E1))
RE: Rounding of whole numbers; not decimals
Then you can use the function MROUND - which lets you round to multiples. eg. =MROUND(A1,25)
RE: Rounding of whole numbers; not decimals
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.
RE: Rounding of whole numbers; not decimals
Thanks for the other tips, though, all. So, still, how . . . ?
RE: Rounding of whole numbers; not decimals
A_final = Precision*ROUND(A_original/Precision,0)
RE: Rounding of whole numbers; not decimals
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.
RE: Rounding of whole numbers; not decimals
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 = " Warning from RndSigFigs macro function." & Chr(10) & Chr(10)
msg = msg & " Rounding to " & SigFigs & " significant figures"
MsgBox (msg)
End If
count = SigFigs - Int(Log10(number)) - 1
RndSigFigs = Int(number * (10 ^ count) + 0.49999) / (10 ^ count)
End Function
RE: Rounding of whole numbers; not decimals
i.e., RndSigFigs(@sum(c1.c23),4) ?
RndSigFigs(=b23*c14/a15,4) ?
RE: Rounding of whole numbers; not decimals
= 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
RE: Rounding of whole numbers; not decimals
Where values of the -interger =:
-1 to round in tens
-2 to round in hundreds
-3 to round in thousands
etc