Cost vs retail

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?

Maybe...

=ROUNDUP(A1*1.5,0)+0.99



cochum wrote:
>
> 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?

--

Dave Peterson

Will this work:

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

Change A1 to match your cell reference.

HTH,
Elkar

"cochum" wrote:

> 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?

Thanks this one worked perfect!

"Dave Peterson" wrote:

> Maybe...
>
> =ROUNDUP(A1*1.5,0)+0.99
>
>
>
> cochum wrote:
> >
> > 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?
>
> --
>
> Dave Peterson
>

"cochum" wrote:
> "Dave Peterson" wrote:
> > cochum wrote:
> > > So I want everthing to have a 50% mark up,
> > > but to always end the retail in a .99 ending.
> >
> > Maybe...
> > =ROUNDUP(A1*1.5,0)+0.99
>
> Thanks this one worked perfect!

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.

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.




[email protected] wrote:
>
> "cochum" wrote:
> > "Dave Peterson" wrote:
> > > cochum wrote:
> > > > So I want everthing to have a 50% mark up,
> > > > but to always end the retail in a .99 ending.
> > >
> > > Maybe...
> > > =ROUNDUP(A1*1.5,0)+0.99
> >
> > Thanks this one worked perfect!
>
> 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.

--

Dave Peterson

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?

"Dave Peterson" wrote:

> 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.
>
>
>
>
> [email protected] wrote:
> >
> > "cochum" wrote:
> > > "Dave Peterson" wrote:
> > > > cochum wrote:
> > > > > So I want everthing to have a 50% mark up,
> > > > > but to always end the retail in a .99 ending.
> > > >
> > > > Maybe...
> > > > =ROUNDUP(A1*1.5,0)+0.99
> > >
> > > Thanks this one worked perfect!
> >
> > 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.
>
> --
>
> Dave Peterson
>

how I would add a button that when clicked
> would change the control numbers in a cell?

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....
--
paul
remove nospam for email addy!



"cochum" wrote:

> 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?
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> >
> >
> >
> > [email protected] wrote:
> > >
> > > "cochum" wrote:
> > > > "Dave Peterson" wrote:
> > > > > cochum wrote:
> > > > > > So I want everthing to have a 50% mark up,
> > > > > > but to always end the retail in a .99 ending.
> > > > >
> > > > > Maybe...
> > > > > =ROUNDUP(A1*1.5,0)+0.99
> > > >
> > > > Thanks this one worked perfect!
> > >
> > > 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.
> >
> > --
> >
> > Dave Peterson
> >