Home |
Search |
Today's Posts |
#1
|
|||
|
|||
rounding a number up to a certain ending digit
I use this formula to come up with the prices for my retail products.
A1 cost of product B1 percent markup, 100% C1 =MROUND(A1*(1+(B1)),5) this would give me my price I have been doubling my cost then rounding that to the nearest multiple of 5 to come up with my price. What I would like to do is instead of rounding to nearest multiple of 5 is round the number up to the next number that ends with a 9. Example 176 round up to 179 173 round up to 179 161 round up to 169 Is there a way to do this in excel? Any help would be greatly appreciated! Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
rounding a number up to a certain ending digit
Hi,
Am Fri, 4 May 2012 19:15:07 +0000 schrieb stumjumper: Example 176 round up to 179 173 round up to 179 161 round up to 169 try: =ROUNDUP(A1*(1+B1)/10,0)*10-1 Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
|
|||
|
|||
Quote:
Dear stumjumper, Good Afternoon. Try this one: C1 -- =IF((A1*(1+(B1)))(MROUND(A1*(1+(B1)),10)),(MROUND (A1*(1+(B1)),10))+9,(A1*(1+(B1)))) Tell me if it worked for you. Have a nice day.
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
rounding a number up to a certain ending digit
hi,
=--(LEFT(A1,LEN(A1)-1)&9) -- isabelle Le 2012-05-04 15:15, stumjumper a écrit : I use this formula to come up with the prices for my retail products. A1 cost of product B1 percent markup, 100% C1 =MROUND(A1*(1+(B1)),5) this would give me my price I have been doubling my cost then rounding that to the nearest multiple of 5 to come up with my price. What I would like to do is instead of rounding to nearest multiple of 5 is round the number up to the next number that ends with a 9. Example 176 round up to 179 173 round up to 179 161 round up to 169 Is there a way to do this in excel? Any help would be greatly appreciated! Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
rounding a number up to a certain ending digit
On Fri, 4 May 2012 19:15:07 +0000, stumjumper wrote:
I use this formula to come up with the prices for my retail products. A1 cost of product B1 percent markup, 100% C1 =MROUND(A1*(1+(B1)),5) this would give me my price I have been doubling my cost then rounding that to the nearest multiple of 5 to come up with my price. What I would like to do is instead of rounding to nearest multiple of 5 is round the number up to the next number that ends with a 9. Example 176 round up to 179 173 round up to 179 161 round up to 169 Is there a way to do this in excel? Any help would be greatly appreciated! Thanks To ROUNDUP to the next highest number ending in 9, you can use: =ROUNDUP(A1+1,-1)-1 where A1 contains the number you want to roundup. or, in your case, if A1 contains your cost, and you are first marking it up 100%, you might consider: =ROUNDUP(A1*2+1,-1)-1 If the markup is 100% |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
rounding a number up to a certain ending digit
you may also have greater flexibility with this one
=--((LEFT(A1,LEN(A1)-1)*10)+9) -- isabelle Le 2012-05-04 17:22, isabelle a écrit : hi, =--(LEFT(A1,LEN(A1)-1)&9) |
#7
|
|||
|
|||
Thanks guys for the responses. They gave me what I needed to know.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
12 digit number rounding down why? | Excel Discussion (Misc queries) | |||
How do I keep a 17 digit number from ending in "0" in excel? | Excel Discussion (Misc queries) | |||
How to validate a cell to insert from 9 digit number to 13 digit number. | Excel Worksheet Functions | |||
How to validate a cell to insert from 9 digit number to 13 digit number. | Excel Worksheet Functions | |||
20 digit wont stop ending in zeros | Excel Discussion (Misc queries) |