×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Rounding of whole numbers; not decimals

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.

RE: Rounding of whole numbers; not decimals

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.

David

RE: Rounding of whole numbers; not decimals

Sorry!
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

Or you could install the "Analysis TookPak" Addin (Tools - Add-Ins... - Check "Analysis ToolPak" & Click "OK") - 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)

RE: Rounding of whole numbers; not decimals

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.

RE: Rounding of whole numbers; not decimals

(OP)
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 . . . ?

RE: Rounding of whole numbers; not decimals

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

RE: Rounding of whole numbers; not decimals

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.

RE: Rounding of whole numbers; not decimals

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 = "  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

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

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

RE: Rounding of whole numbers; not decimals

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

RE: Rounding of whole numbers; not decimals

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources