If question

[BradVogl]

I have a range of products that need to be assigned package quantities accotding to unit cost.

Under $5.00 each would resuilt in 100
$5.00 to $10.00 Range " " 50
$10.00 to $25.00 Range " " 25

I tried this:

If([AVG(Resale)]<=5,100,0)+
If([AVG(Resale)]>5<10,50,0)+
If([AVG(Resale)]>=10,25,0)

It results in prices less than 10 as 150 rather than 100 picking up the first 2 If statements.
What should I do?
Any help?

Appreciate any response.

Brad Vogl

[OllyInMunich]

Hello Brad,

You can get the result you want using:

If([AVG(Resale)]<=5,100,0)
If(([AVG(Resale)]>5 .and [AVG(Resale)]<10),50,0)+
If([AVG(Resale)]>=10,25,0)

You might want to consider making a calculated lookup field if the number of options grows to be unwieldy...

HTH

Olly

[Data Kruncher]

Alternatively, you could truly nest the IFs and resequence things a little to help with the logic, like so:


If([AVG(Resale)]>=10,25,
If([AVG(Resale)]>5,50,100))


Monarch should process this by order of operations, so if Avg(Resale) is >10 you'll get 25, otherwise if >5 you get 50, and in all other cases, you get 100. I find that working from the largest value down the chain works best for a small number of possible choices, such as this. Otherwise, lookup tables are more convenient to maintain.

Kruncher

[BradVogl]

Thanks for both ideas.
It was a great help.
Works perfect.

Brad Vogl