Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding up or down in the same formula
How do I create a single formula to either round up or down the result of my
formula to the nearest "9"? i.e. 843 = 839 845 = 849 etc., etc. Thanks, Jack |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding up or down in the same formula
Are you number always integers? If so try:
=ROUND(A1,-1)-1 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Jack L. Hyman" <Jack L. wrote in message ... How do I create a single formula to either round up or down the result of my formula to the nearest "9"? i.e. 843 = 839 845 = 849 etc., etc. Thanks, Jack |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding up or down in the same formula
Try
=ROUND(A1/10,0)*10-1 problem will occur with a value smaller than 5 so you might want to use an IF function for that or simply use =MAX(ROUND(A1/10,0)*10-1,0) which will return zero if a value is less than 5 -- Regards, Peo Sjoblom "Jack L. Hyman" <Jack L. wrote in message ... How do I create a single formula to either round up or down the result of my formula to the nearest "9"? i.e. 843 = 839 845 = 849 etc., etc. Thanks, Jack |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding up or down in the same formula
Doh!
-- Regards, Peo Sjoblom "Sandy Mann" wrote in message ... Are you number always integers? If so try: =ROUND(A1,-1)-1 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Jack L. Hyman" <Jack L. wrote in message ... How do I create a single formula to either round up or down the result of my formula to the nearest "9"? i.e. 843 = 839 845 = 849 etc., etc. Thanks, Jack |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding up or down in the same formula
Thank you!
"Peo Sjoblom" wrote: Try =ROUND(A1/10,0)*10-1 problem will occur with a value smaller than 5 so you might want to use an IF function for that or simply use =MAX(ROUND(A1/10,0)*10-1,0) which will return zero if a value is less than 5 -- Regards, Peo Sjoblom "Jack L. Hyman" <Jack L. wrote in message ... How do I create a single formula to either round up or down the result of my formula to the nearest "9"? i.e. 843 = 839 845 = 849 etc., etc. Thanks, Jack |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding up or down in the same formula
Thank you very much!
"Sandy Mann" wrote: Are you number always integers? If so try: =ROUND(A1,-1)-1 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Jack L. Hyman" <Jack L. wrote in message ... How do I create a single formula to either round up or down the result of my formula to the nearest "9"? i.e. 843 = 839 845 = 849 etc., etc. Thanks, Jack |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding up or down in the same formula
You might want to use Sandy's formula that will do the same with less effort
-- Regards, Peo Sjoblom "Jack L. Hyman" wrote in message ... Thank you! "Peo Sjoblom" wrote: Try =ROUND(A1/10,0)*10-1 problem will occur with a value smaller than 5 so you might want to use an IF function for that or simply use =MAX(ROUND(A1/10,0)*10-1,0) which will return zero if a value is less than 5 -- Regards, Peo Sjoblom "Jack L. Hyman" <Jack L. wrote in message ... How do I create a single formula to either round up or down the result of my formula to the nearest "9"? i.e. 843 = 839 845 = 849 etc., etc. Thanks, Jack |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding up or down in the same formula
Thank you...both do the job well.
"Peo Sjoblom" wrote: You might want to use Sandy's formula that will do the same with less effort -- Regards, Peo Sjoblom "Jack L. Hyman" wrote in message ... Thank you! "Peo Sjoblom" wrote: Try =ROUND(A1/10,0)*10-1 problem will occur with a value smaller than 5 so you might want to use an IF function for that or simply use =MAX(ROUND(A1/10,0)*10-1,0) which will return zero if a value is less than 5 -- Regards, Peo Sjoblom "Jack L. Hyman" <Jack L. wrote in message ... How do I create a single formula to either round up or down the result of my formula to the nearest "9"? i.e. 843 = 839 845 = 849 etc., etc. Thanks, Jack |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a formula with rounding up & rounding down to the nearest . | Excel Worksheet Functions | |||
Rounding Formula | Excel Worksheet Functions | |||
Rounding up Formula | Excel Worksheet Functions | |||
Rounding with the sum formula | Excel Discussion (Misc queries) | |||
rounding formula | Excel Worksheet Functions |