How do I round numbers to the nearest multiple of 10 or 5?
 

Using Excel Office 2000, I'm calculating the quantity and value of vaccines
remaining in end-of-year inventory according to 3 different funding
categories (from which vaccine was purchased) and according to package size.
Package sizes are multiples of 5 or multiples of 10, depending on the
vaccine. For example, there are a total of 400 doses of DT in inventory.
Using percentages, 133 doses of DT were purchased using fund #2 and 267 doses
were purchased using fund #3 (fund #1 was not used for purchase of DT). How
do I round to the nearest multiple of 10 in Excel so that the ending result
still equals 400 doses when added together?

Answer: How do I round numbers to the nearest multiple of 10 or 5?
 

1. First, determine the multiple of 10 or 5 that you want to round to. In your case, you want to round to the nearest multiple of 10.
2. Divide the number you want to round by the multiple you determined in step 1. In your example, you would divide 400 by 10, which gives you 40.
3. Use the ROUND function to round the result of step 2 to the nearest whole number. In your example, you would use the formula =ROUND(40,0), which gives you 40.
4. Multiply the result of step 3 by the multiple you determined in step 1. In your example, you would multiply 40 by 10, which gives you 400.

To apply this to your specific scenario, you can use the following formula to round the number of doses purchased with fund #2 to the nearest multiple of 10:

=ROUND(133/10,0)*10

This formula divides 133 by 10 to get 13.3, rounds it to the nearest whole number (which is 13), and then multiplies it by 10 to get 130.

You can use a similar formula to round the number of doses purchased with fund #3 to the nearest multiple of 10:

=ROUND(267/10,0)*10

This formula divides 267 by 10 to get 26.7, rounds it to the nearest whole number (which is 27), and then multiplies it by 10 to get 270.

When you add these two rounded numbers together (130 + 270), you get 400, which is the total number of doses you started with.

How do I round numbers to the nearest multiple of 10 or 5?
 

To round to nearest 10

=ROUND(A1,-1)

to nearest 5

=ROUND(A1/5,0)*5

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Wasatch" wrote in message
...
Using Excel Office 2000, I'm calculating the quantity and value of
vaccines
remaining in end-of-year inventory according to 3 different funding
categories (from which vaccine was purchased) and according to package
size.
Package sizes are multiples of 5 or multiples of 10, depending on the
vaccine. For example, there are a total of 400 doses of DT in inventory.
Using percentages, 133 doses of DT were purchased using fund #2 and 267
doses
were purchased using fund #3 (fund #1 was not used for purchase of DT).
How
do I round to the nearest multiple of 10 in Excel so that the ending
result
still equals 400 doses when added together?

How do I round numbers to the nearest multiple of 10 or 5?
 

10 =ROUND(A1,-1)
5 ==ROUND(2*A1,-1)/2

if your value is in a1

regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=558461

How do I round numbers to the nearest multiple of 10 or 5?
 

Hi, here are some tips regarding this

http://www.j-walk.com/ss/excel/usertips/tip011.htm

maybe it could help you

regards


"Wasatch" escreveu:

Using Excel Office 2000, I'm calculating the quantity and value of vaccines
remaining in end-of-year inventory according to 3 different funding
categories (from which vaccine was purchased) and according to package size.
Package sizes are multiples of 5 or multiples of 10, depending on the
vaccine. For example, there are a total of 400 doses of DT in inventory.
Using percentages, 133 doses of DT were purchased using fund #2 and 267 doses
were purchased using fund #3 (fund #1 was not used for purchase of DT). How
do I round to the nearest multiple of 10 in Excel so that the ending result
still equals 400 doses when added together?

How do I round numbers to the nearest multiple of 10 or 5?
 

What do y'all have against MROUND?

=MROUND(A1,10)
=MROUND(A1,5)

Die_Another_Day


Dav wrote:
10 =ROUND(A1,-1)
5 ==ROUND(2*A1,-1)/2

if your value is in a1

regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=558461