Author |
Message |
Corey99
Newbie
Joined: 17 Aug 2011
Location: United States
Online Status: Offline
Posts: 3
|
Topic: Round Price Formula with Conditions Posted: 17 Aug 2011 at 4:57am |
Hello,
I am looking for a formula to round a calculated price and subtract a cent. I want it to calculate a new price based off of gross margin and using the new cost.
So I use:
New Cost / (1-GM%) = New Price (Works fine)
I want it to calculate and then do this:
If New Price < $5.00 Round Up to the nearest (10 cents) and subtract 1 cent. I. E. (If $4.22) then New Price rounds up to $4.30-.01=$4.29
If New Price > $5.00 Round Up to the nearest (dollar) and subtract 1 cent. I. E. (If $5.65) then New Price rounds up to $6.00-.01=$5.99
How can I do this all in one formula and have it calculate the original formula but print the new price based on the 2 IF statement?
Any direction towards this would be great.
I use Crystal Reports 9.
Thanks,
Corey
Edited by Corey99 - 17 Aug 2011 at 5:09am
|
IP Logged |
|
FrnhtGLI
Senior Member
Joined: 22 May 2009
Online Status: Offline
Posts: 347
|
Posted: 17 Aug 2011 at 6:48am |
You should be able to achieve this with a local variable. Something like:
whileprintingrecords;
local numbervar nNewPrice; local numbervar nRoundedPrice;
local numbervar nNewPrice:=New Cost / (1-GM%);
local numbervar nRoundedPrice:=if nNewPrice <5 then RoundUp(nNewPrice,1)-.01 else RoundUp(nNewPrice)-.01;
nRoundedPrice;
|
|< /\ '][' ( )
|
IP Logged |
|
Corey99
Newbie
Joined: 17 Aug 2011
Location: United States
Online Status: Offline
Posts: 3
|
Posted: 17 Aug 2011 at 7:45am |
Thanks for the quick response.
Can I use it in the formula workshop? Or what way can I do this.
I have placed this in the formula workshop for New Price
whileprintingrecords;
local numbervar nNewPrice; local numbervar nRoundedPrice;
local numbervar nNewPrice:= {Sheet1_.Cost} / (1-{@GM%});
local numbervar nRoundedPrice:=if nNewPrice <5 then RoundUp(nNewPrice,1)-.01 else RoundUp(nNewPrice)-.01;
nRoundedPrice;
Says I am missing a number, currency amount, boolean, date, time, date-time, or string. Highlights the RoundUp after the then.
What am I missing?
|
IP Logged |
|
Corey99
Newbie
Joined: 17 Aug 2011
Location: United States
Online Status: Offline
Posts: 3
|
Posted: 17 Aug 2011 at 7:52am |
Works. I guess RoundUp doesn't work in CR 9? So, I just used Round and it works. Thanks for all your help.
|
IP Logged |
|
FrnhtGLI
Senior Member
Joined: 22 May 2009
Online Status: Offline
Posts: 347
|
Posted: 17 Aug 2011 at 7:53am |
hmmmm...could possibly be that the RoundUp function isn't available in CR9. I am using CR2008 so that could have something to do with it. While in the Formula Editor, look under The Math Function and see if RoundUp is there. If not, we may have to look for another solution.
|
|< /\ '][' ( )
|
IP Logged |
|
|