
#1
Dec1009, 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 



#2
Dec1009, 12:32 PM

Mentor
P: 7,291

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.




#3
Dec1009, 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




#4
Dec1009, 01:45 PM

Sci Advisor
P: 5,095

rounding numbers in microsoft excel
There is always the ROUND function. You can also do a user defined function.




#5
Dec1009, 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 



#6
Dec1009, 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. 



#7
Dec1009, 06:10 PM

P: 10





#8
Dec1009, 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 



#9
Dec1009, 06:18 PM

P: 10

I see. sorry. In hindsight I see that it was perfectly explained all along.




#10
Dec1009, 06:45 PM

P: 754

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 