CANNOT CALCULATE IF AND THEN FORMULA 1

[LZido]

I have a formula that scores 3 different criterias:

0 or 1 hydrant (with less than 1000 GPM) = 3
1 at 1000 GPM or over and 1 less than 1000 GPM = 2
1 hydrants at 1000 GPM or over = 1

In my data:
{@WaterSupply} is valued at 1293
{@WaterSupply2} is valued at 1435

Formula:
if {@WaterSupply} < 1000 then 3 else
if {@WaterSupply} > 1000 then 2 else
if {@WaterSupply2} < 1000 then 2 else
if {@WaterSupply} > 1000 and {@WaterSupply2} > 1000 then 1

Problem: It gives me back “3” instead of “1”.

I know there is a simply thing I am doing wrong - just cannot figure it out.

 

[kray4660]

Not sure why it is retuning a 3 instead of something else. Nested If's can be hard to debug. Have you tried using the Switch function?

Switch( {@WaterSupply} < 1000, 3, {@WaterSupply} > 1000,2, {@WaterSupply2} < 1000, 2, {@WaterSupply} > 1000 and {@WaterSupply2},1)

 

[LZido]

Never heard of a switch function. Sounds interesting!

I am getting back "A Boolean is required here" and the last {@WaterSupply} in the formula is highlighted.

 

[LZido]

I ended up putting the original formula in the group footer and the formula did work.
Thanks for your help and I am going to research the Switch Function.

It is great how people are willing to help and respond quickly.

THANKS!!