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
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.
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.
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.
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.
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.
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
See ROUND Function Office support
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With