Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CANNOT CALCULATE IF AND THEN FORMULA 1

Status
Not open for further replies.

LZido

Technical User
Aug 24, 2006
21
US
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.
 
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)
 
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.

 
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!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top