# Rounding numbers in microsoft excel

• Dell
In summary, the person is asking how to round numbers in a spreadsheet with 2 decimal points, but for a number with a.byX value, they need to round upward, for x<5, round downward, and for x=5, round up. They also mention that if y is odd, they round up and if y is even, they round down.
Dell
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,, can't seem to get it right
any ideas

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.

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

There is always the ROUND function. You can also do a user defined function.

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

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(A1,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.

Dell said:
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?

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

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

zgozvrm said:
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.

## What is rounding numbers in Microsoft Excel?

Rounding numbers in Microsoft Excel is a mathematical function that allows you to change the precision of a numerical value. It is often used to simplify data or make calculations easier.

## How do I round numbers in Microsoft Excel?

To round a number in Excel, you can use the ROUND, ROUNDUP, or ROUNDDOWN function. These functions allow you to specify the number of decimal places to round to, and whether to round up or down.

## Can I round numbers to a specific decimal place?

Yes, you can specify the number of decimal places to round to by adding a second argument to the ROUND, ROUNDUP, or ROUNDDOWN function. For example, to round a number to 2 decimal places, you would use the formula =ROUND(A1, 2), where A1 is the cell containing the number you want to round.

## What happens if I round a number that already has the desired number of decimal places?

If you round a number that already has the desired number of decimal places, the number will not change. For example, if you round 3.14 to 2 decimal places, it will remain 3.14.

## Can I undo rounding numbers in Microsoft Excel?

Yes, you can undo rounding numbers in Excel by using the original data or by using the UNROUND function. The UNROUND function returns the original number before it was rounded.

• Computing and Technology
Replies
5
Views
2K
• General Math
Replies
1
Views
1K
• General Math
Replies
12
Views
3K
• General Math
Replies
7
Views
2K
• Calculus and Beyond Homework Help
Replies
23
Views
1K
• Introductory Physics Homework Help
Replies
14
Views
2K
• Engineering and Comp Sci Homework Help
Replies
4
Views
2K
• Calculus and Beyond Homework Help
Replies
3
Views
2K
• MATLAB, Maple, Mathematica, LaTeX
Replies
4
Views
7K
• Linear and Abstract Algebra
Replies
16
Views
3K