rounding numbers in microsoft excel


by Dell
Tags: excel, microsoft, numbers, rounding
Dell
Dell is offline
#1
Dec10-09, 11:52 AM
P: 590
i need to round numbers in a spreadsheet 2 decimal points, in a way that:

for a number with a.byX

for x>5 i need to round upwards
for x<5 i need to round downwards
for x=5 i need to round up if y is odd and round down if y is even

for example

0.563~0.56
0.566~0.57
0.575~0.58
0.565~0.56

i tried using if, roundup, rounddown, floor,, cant seem to get it right
any ideas
Phys.Org News Partner Science news on Phys.org
Cougars' diverse diet helped them survive the Pleistocene mass extinction
Cyber risks can cause disruption on scale of 2008 crisis, study says
Mantis shrimp stronger than airplanes
Integral
Integral is offline
#2
Dec10-09, 12:32 PM
Mentor
Integral's Avatar
P: 7,292
Just set the cell format to show the number of digits you want. Setting the precision in the cell format does exactly what you need.
Dell
Dell is offline
#3
Dec10-09, 12:34 PM
P: 590
no, i need it to round the numbers for me, i need them for calculations and i need them to be exacly the way i stated

FredGarvin
FredGarvin is offline
#4
Dec10-09, 01:45 PM
Sci Advisor
FredGarvin's Avatar
P: 5,095

rounding numbers in microsoft excel


There is always the ROUND function. You can also do a user defined function.
Dell
Dell is offline
#5
Dec10-09, 01:51 PM
P: 590
but the round fuction always rounds 5 upwards,
0.5->1
1.5->2
2.5->3
3.5->4.....

i need
0.5->0
1.5->2
2.5->2
3.5->4
4.5->4

always rounding to the even number,
only that i need it to the 2nd decimal point no to the whole number
zgozvrm
zgozvrm is offline
#6
Dec10-09, 04:13 PM
P: 754
As long as you're only dealing with rounding numbers with 3 decimal places down to 2 decimal places, this will work:

=IF((RIGHT(A1,1)<>"5"),ROUND(A1,2),IF(ISODD(A1*100),ROUND(A1,2),TRUNC(A 1,2)))

Enter this formula in any cell except A1.
Enter your number in cell A1.

To make this work for other types of numbers, I would write a user defined function in VBA.
edvinf
edvinf is offline
#7
Dec10-09, 06:10 PM
P: 10
Quote Quote by Dell View Post

0.563~0.56
0.566~0.57
0.575~0.58
0.565~0.56
Your example seems to be flawed. Shouldn't 0.566~0.56 , according to your specification?
zgozvrm
zgozvrm is offline
#8
Dec10-09, 06:14 PM
P: 754
The example's not flawed, the explanation is a bit unclear though.

"b" refers to the 1st digit to the right of the decimal point
"y" refers to the 2nd
"x" refers to the 3rd

His rules state that for "x" > 5, round upward, therefore in 0.566,
"x" is equal to 6 which is greater than 5, so round upward to 0.57
edvinf
edvinf is offline
#9
Dec10-09, 06:18 PM
P: 10
I see. sorry. In hindsight I see that it was perfectly explained all along.
zgozvrm
zgozvrm is offline
#10
Dec10-09, 06:45 PM
P: 754
Quote Quote by zgozvrm View Post
To make this work for other types of numbers, I would write a user defined function in VBA.
I've already written VBA code for this.
Let me know if you're interested.


Register to reply

Related Discussions
Formula for Microsoft ExcelŽ's FV function?? General Math 5
Microsoft VBA Excel help! (Please) Engineering, Comp Sci, & Technology Homework 1
microsoft excel help Programming & Computer Science 2
Microsoft Excel Question General Engineering 8
Microsoft Excel Factorials Question Computing & Technology 1