Excel Question - Rounding Up

  Evolution 09:13 15 Mar 2006
Locked

Hi,

I'm using Excel 2000.

I need to use a rounding up function to display accurate calculations to 0 decimal places.

I can use the ROUND syntax but I have to apply this to each individual cell.

Is there a way of applying this to the whole worksheet or blocks of cells?

Thanks,

Evo

Place the following routine in the appropriate worksheets code page

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = WorksheetFunction.RoundUp(Target.Value, 0)
End Sub

This will always roundup the answer to the next integer value even if the value entered is 6.00001 the answer displayed will be 7

HTH

  Simsy 18:10 15 Mar 2006

of what ROUND does, as opposed to formatting to zero decimal places....

Say you have a figure of 25.45

If you format the cell to be zero decimal places it will show 25

If you multiply this cell by 2, the answer is 50.9, (as it is actually multiplying 25.45 by 2). If this cell is also formated to be zero decimal places, the answer shown will be 51


If, intead of formatting you use the ROUND function, then the ACTUAL value of the cell will be 25, (not just the displayed value). The multiplying be 2 will then give an ACTUAL value of 50, instead of 51...

Furthermore... in your original post you mention "Rounding up"... If you use the ROUNDUP function the 25.45 becomes 26, and multiplying by 2 it becomes 52...

Hence for a simple bit of arithmetic, (Multiplying by 2), we can have 50, 51 or 52 given as the answer. If the correct answer should be 50, and we've achieved 52, that's an increase of 4%... things can easily escalate and go very wrong!


Note that I'm not suggesting what Whisperer has provided is wrong... just that you need to be sure it's what you want.



You use the phrase "display accurate calculations". I suspect you don't want the ROUND function, but rather you need to format the cells accordingly.

You can format them all at once by selection all the relevant cells while holding the ctl key down.

Then, on the menu bar, select Format>Cells>Number and choose 0 decimal places.


My apologies if I'm teaching you to suck eggs here... but it might be important!

Good luck,

Regards,

Simsy

I was also a little concerned about "display accurate calculations" and "roundup" which are, of course, contradictory terms

BW

  VoG II 22:36 15 Mar 2006

They are both correct of course, being Excel gurus.

I was going to keep my nose out but also spotted the inconsistency in your post, Evolution.

Do you want to round entered values or display the entered values as rounded?

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

Aardman's new YouTube channel to share the work of independent animators

iPhone X vs Samsung Galaxy Note 8

Les meilleurs navigateurs internet 2017