Cost vs retail

C

cochum

I am creating a spread sheet to make marking up cost easier and standard.
The only thing is that I want to always have a .99 cent ending ont he retail.
So I want everthing to have a 50% mark up, but to always end the retail in a
..99 ending. How do I do that?
 
D

Dave Peterson

Maybe...

=ROUNDUP(A1*1.5,0)+0.99


I am creating a spread sheet to make marking up cost easier and standard.
The only thing is that I want to always have a .99 cent ending ont he retail.
So I want everthing to have a 50% mark up, but to always end the retail in a
.99 ending. How do I do that?
Click to expand...
 
E

Elkar

Will this work:

=ROUNDUP((A1*1.5),0)-0.01

Change A1 to match your cell reference.

HTH,
Elkar
 
J

joeu2004

cochum said:
Thanks this one worked perfect!
Click to expand...

If by "works", you mean that it produces an answer
with ".99" at the end, I would agree. But I believe
it gives the wrong answer in most cases. By "wrong",
I mean that result will be slightly higher than necessary.
Of course, you might not care, since it merely mean
more profit. But I believe the "correct" answer is
ROUNDDOWN, not ROUNDUP.

Proof: If 1.5*R is ccc.99, ROUNDDOWN()+0.99 with
result in ccc.99. Otherwise, 1.5*R is less than ccc.99,
and ROUNDDOWN()+0.99 results in 1.5*R rounded up
to ".99".

Note: I believe that is also true for ROUNDIP()-0.01,
which someone else suggested.

In contrast, ROUNDUP()+0.99 results in 1.00+ccc.99
-- that is, an extract $1 -- in all cases where 1.5*R is
ccc.01 or more.
 
D

Dave Peterson

I agree with you.

And with an example:

Cost is 1.00:
=ROUNDUP(A1*1.5,0)+0.99 returns 2.99
=ROUNDUP((A1*1.5),0)-0.01 returns 1.99
=ROUNDDOWN(A1*1.5,0)+0.99 returns 1.99

Cost is 2.00:
=ROUNDUP(A1*1.5,0)+0.99 returns 3.99
=ROUNDUP((A1*1.5),0)-0.01 returns 2.99
=ROUNDDOWN(A1*1.5,0)+0.99 returns 3.99

So the only formula that really works is the =rounddown() version.

Here's hoping the OP comes back.
 
C

cochum

I came back and already fixed it but thanks too you both!
Don't suppose either of you know how I would add a button that when clicked
would change the control numbers in a cell?
 
P

paul

how I would add a button that when clicked
would change the control numbers in a cell?
Click to expand...

you will need to be a wee bit more specific than that...do you want say a
dropdown box(es) to select markups and or rounding ammounts,ie 95 55 etc....
 
Top