|
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? |
Cost vs retail
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 |
Cost vs retail
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? |
Cost vs retail
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 |
Cost vs retail
"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. |
Cost vs retail
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. 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 |
Cost vs retail
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. 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 |
| All times are GMT +1. The time now is 07:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com