Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rounding cell value to nearest thousand in excel

I have been trying to round a cell value up or down to the nearest thousand but can't get it to work. I'm trying to calculate my hourly rate based on the current exchange rate from USD to VND But if the resulting total is something like 22,325 then it should round down to 22,000 and likewise if the hundreds are 500 or more it should round up to 23,000

Calculation sample

So where the hourly rate says 527,325 it should round down to 527,000. The cell already contains a formula to multiply the the current exchange rate by the USD.

like image 430
user2037250 Avatar asked Mar 24 '14 00:03

user2037250


People also ask

How do you round to the nearest 1000?

To round a number to the nearest 1000, look at the hundreds digit. If the hundreds digit is 5 or more, round up. If the hundreds digit is 4 or less, round down. The hundreds digit in 4559 is 5.

How do I show 1000 as 1k in Excel?

STEP 1: Select Column D in the data below. STEP 2: Right-Click and then Select Format Cells. STEP 3: In the Format Cells dialog box, Under Number Tab select Custom. STEP 4: In the Type section, type format – 0.0, “K” and click OK.

How do you make a cell round up in Excel?

To use the ROUNDUP function in Excel, type the =ROUNDUP(number, num_digits) function into a cell, where number is the number you want to round up and num_digits is the number of digits you want to round the number up to. For example, the function =ROUNDUP(5.5,1) rounds 5.5 up to 6.

How do you round off cell value?

The following method is used to round down ex: 3.14159 to three decimal places: =ROUNDDOWN(3.14159,3), which will result in 3.141.


1 Answers

Using the ROUND function:

=ROUND(A1,-3)

Where A1 is the cell containing the number you wish to round. The negative number specifies digits to the left of the decimal point to replace with zeros (the number of zeros at the end of the number).

Like so:

=ROUND( 34528, -3 ) = 35000

As for the OP's example:

=ROUND( 22325, -3 ) = 22000

The OP also stated:

likewise if the hundreds are 500 or more it should round up to 23,000

=ROUND( 22500, -3 ) = 23000

ROUND Function

See ROUND Function Office support

like image 178
bjbk Avatar answered Sep 21 '22 10:09

bjbk