# Can you help with an "IsNull" formula to limit an action?

• 0
• Question
• Updated 3 years ago

I have a field that figures an average of two other fields, it works great.

Now I need to add a line above it, telling it to only make the calculation

if both fields are populated. Tried several variations of this:

If(Not(IsNull([Average IBC Avg List Price]) AND Not(IsNull([Average LSI Avg US List Price]),

Average(ToNumber([Average IBC Avg List Price]),ToNumber([Average LSI Avg US List Price]))

All I get is a "parenthesis is missing" error.

Any/all help deeply appreciated,

Ryan

• 0 Points

Posted 3 years ago

• 0

dwhawe, Champion

• 908 Points
Try taking the ( out from between Not and IsNull
• 0 Points
That resulted in a new error, "Please check the syntax of your entire formula"

dwhawe, Champion

• 908 Points
Maybe you need to test for 0 rather than Null.  Or switch your Then and Else so you test for IsNull

dwhawe, Champion

• 908 Points
I just noticed your fields appear to be text.  IsNull cannot be used for text, use "" to test for empty text fields
• 70 Points
Try the following

If(
Not IsNull([Average IBC Avg List Price])
AND
Not IsNull([Average LSI Avg US List Price]),
Average(ToNumber([Average IBC Avg List Price])
,ToNumber([Average LSI Avg US List Price])
)
)

NealPatil @ gmx.com
• 0 Points
Thanks to all, funny thing . . . the person I'm doing this for just informed me he is NOT looking for an average of the two fields, but something all together different. Funny isn't it? There's two hours of my life I'll never get back. I'm sorry to have bothered everyone and apologize.
• 0 Points
Thanks to all, funny thing . . . the person I'm doing this for just informed me he is NOT looking for an average of the two fields, but something all together different. Funny isn't it? There's two hours of my life I'll never get back. I'm sorry to have bothered everyone and apologize.

dwhawe, Champion

• 908 Points
That's why there is a pillow in my office.  Not for naps but to dampen the screams of frustration.
• 0 Points
Wouldn't bother me except the written directions clearly stated ". . . . an average of . . . " arghhhhh!