Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding up or down to specific decimal digits.
Folks,
I am trying to do the following: I am looking at MSRPs for our products ..for example Product A comes with a Base price of $71.16..Product B comes with a price of $54.78. I am trying to round the end number to a 7 or 9...therefore, Prod A becomes $71.17 and Product B becomes $54.79.....it needs to be somewhat "intuitive" i.e. $54.78 doesn't become $54.77 but rather $54.79 and so on..... i am thinking I could use an ODD and some variant of ROUND function..not sure how to combine them for the last decimal place...is this feasible? Thanks for your help. Regards, Shams. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding up or down to specific decimal digits.
I hope there is a better way
=A21+(IF(ROUND(MOD(A21,0.1),2)0.07,0.09,0.07)-ROUND(MOD(A21,0.1),2)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shams" wrote in message ... Folks, I am trying to do the following: I am looking at MSRPs for our products ..for example Product A comes with a Base price of $71.16..Product B comes with a price of $54.78. I am trying to round the end number to a 7 or 9...therefore, Prod A becomes $71.17 and Product B becomes $54.79.....it needs to be somewhat "intuitive" i.e. $54.78 doesn't become $54.77 but rather $54.79 and so on..... i am thinking I could use an ODD and some variant of ROUND function..not sure how to combine them for the last decimal place...is this feasible? Thanks for your help. Regards, Shams. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding up or down to specific decimal digits.
Bob,
Thank you very much for your input. This rather complicated formula works brilliantly! I just have to spend some time to understand what it's doing ....need to understand this intuitively "Bob Phillips" wrote: I hope there is a better way =A21+(IF(ROUND(MOD(A21,0.1),2)0.07,0.09,0.07)-ROUND(MOD(A21,0.1),2)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shams" wrote in message ... Folks, I am trying to do the following: I am looking at MSRPs for our products ..for example Product A comes with a Base price of $71.16..Product B comes with a price of $54.78. I am trying to round the end number to a 7 or 9...therefore, Prod A becomes $71.17 and Product B becomes $54.79.....it needs to be somewhat "intuitive" i.e. $54.78 doesn't become $54.77 but rather $54.79 and so on..... i am thinking I could use an ODD and some variant of ROUND function..not sure how to combine them for the last decimal place...is this feasible? Thanks for your help. Regards, Shams. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel is rounding off more than 16 digits ? | Excel Discussion (Misc queries) | |||
How to truncate to tenths digits without rounding | Excel Worksheet Functions | |||
30 decimal digits | Excel Discussion (Misc queries) | |||
Can I stop Excel from seeing more than 2 digits after decimal? | Excel Worksheet Functions | |||
Displaying only digits after the decimal point | Excel Worksheet Functions |