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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 08:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com