rounding a number up to a certain ending digit

S

stumjumper

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!

Thank
 
C

Claus Busch

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
 
M

Mazzaropi

stumjumper;1601490 said:
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

<<<<< *HELP from BRAZIL* >>>>>

Dear *stumjumper*, Good Afternoon.

-Try this one-:
C1[/B] -->
=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

isabelle

hi,

=--(LEFT(A1,LEN(A1)-1)&9)

--
isabelle



Le 2012-05-04 15:15, stumjumper a écrit :
 
R

Ron Rosenfeld

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%
 
I

isabelle

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 :
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top