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 2 years ago
dwhawe, Champion
 722 Points
 0 Points
That resulted in a new error, "Please check the syntax of your entire formula"
dwhawe, Champion
 722 Points
Maybe you need to test for 0 rather than Null. Or switch your Then and Else so you test for IsNull
dwhawe, Champion
 722 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
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
 722 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!
Related Categories

Formulas & functions
 2836 Conversations
 66 Followers

Tables & fields
 7106 Conversations
 162 Followers