Solved

# Formulae for rounding off numbers

Posted on 2011-02-20
811 Views
Folks,

I have a column that contains a calculated result, e.g

Field_1 = \$50, Field_2 = \$2.7122 & Field_3 = \$135.61

Field_1 * Field_2 = Field_3

Now I want to have a new field where I have Field_3 rounding off to \$140.00 so that i have an even number..
0
Question by:greeneel
• 2

LVL 50

Accepted Solution

barry houdini earned 400 total points
ID: 34939837
so what's the rounding criterion, are you rounding to the nearest \$10? If so then with \$135.61 in A3 you can use this formula in A4

=ROUND(A3,-1)

If you always want to round up then change ROUND to ROUNDUP. For you example that would make no difference, but \$133.33, for instance, will ROUND to \$130.00 but ROUNDUP to \$140.00.

regards, barry
0

LVL 16

Assisted Solution

sjklein42 earned 100 total points
ID: 34939849
Use ROUND

ROUND(135.61, -1) equals 140

``````ROUND

Rounds a number to a specified number of digits.

Syntax

ROUND(number,num_digits)

Number   is the number you want to round.

Num_digits   specifies the number of digits to which you want to round number.

If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.

If num_digits is 0, then number is rounded to the nearest integer.

If num_digits is less than 0, then number is rounded to the left of the decimal point.

Examples

ROUND(2.15, 1) equals 2.2

ROUND(2.149, 1) equals 2.1

ROUND(-1.475, 2) equals -1.48

ROUND(21.5, -1) equals 20
``````
0

LVL 16

Expert Comment

ID: 34939851
too slow
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Title # Comments Views Activity
Excel VBA 10 39
VBA copy column paste as value 5 21
Rather Simple Formatting Question 6 23
Clear Filter 8 35
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

#### 776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.