asked on

Crystal Rounding

I need help with a formula to look up the product family for a given product.

Then if that

If Product family field is "LSS" calculate "AVPS field" times 2 and round up to nearest 1,
If product family field is, "Soda" calculate "AVPS field " times 1.5 and Round to nearest 6,
If product family field is "candy" calculate "avps field" time 2.25 and round down to nearest even,

Thanks in advance for your help!

Mike McCracken

Try this

If {ProductField} = "LSS" then
RoundUp({AVPSField} * 2)
Else if {ProductField} = "Soda" then
(ROUNDUP({AVPSField}*1.5) / 6) * 6
Else if {ProductField} = "Candy" then
(RoundUp({AVPSField}*2.25 / 2)*2
Else
{AVPSField}

mlmcc

evend

ASKER

Thanks I had to adjust ()'s to ROUNDUP(({@Average}*{?LSS SFP}) / 6) * 6 for it to work.
I forgot one part of the formula can you help? If the formula for each line ends with a # higher then capacity then the result should equal Capacity.

How would I make that happen?

Thanks!

Mike McCracken

Try this

Local NumberVar AdjustedAmount;

If {ProductField} = "LSS" then
    AdjustedAmount := RoundUp({AVPSField} * 2)
Else if {ProductField} = "Soda" then
    AdjustedAmount := ROUNDUP({AVPSField}*1.5 / 6) * 6
Else if {ProductField} = "Candy" then
    AdjustedAmount := RoundUp({AVPSField}*2.25 / 2)*2
Else
    AdjustedAmount := {AVPSField};

If AdjustedAmount > {CapacityField} then
    AdjustedAmount := {CapacityField};

AdjustedAmount

Open in new window

mlmcc

evend

ASKER

Mlmcc I am getting a variable name error against the firt line with {@NewPar};

Local NumberVar {@NewPar};

If {product_families.description} = "20z Carb Soft Drinks" then
{@NewPar} := RoundUp({@Average} * 2)
Else if {product_families.description} = "20z Non Carb Bev" then
{@NewPar} := ROUNDUP({@Average}*1.5 / 6) * 6
Else
{@NewPar} := {@Average};

If {@NewPar} > {Vend_Visit_Items.capacity} then
{@NewPar} := {Vend_Visit_Items.capacity};

{@NewPar}

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial