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

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

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

Photo of Ryan

Ryan

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of dwhawe

dwhawe, Champion

  • 908 Points 500 badge 2x thumb
Try taking the ( out from between Not and IsNull
Photo of Ryan

Ryan

  • 0 Points
That resulted in a new error, "Please check the syntax of your entire formula"
Photo of dwhawe

dwhawe, Champion

  • 908 Points 500 badge 2x thumb
Maybe you need to test for 0 rather than Null.  Or switch your Then and Else so you test for IsNull
Photo of dwhawe

dwhawe, Champion

  • 908 Points 500 badge 2x thumb
I just noticed your fields appear to be text.  IsNull cannot be used for text, use "" to test for empty text fields
Photo of Neal

Neal

  • 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
Photo of Ryan

Ryan

  • 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.
Photo of Ryan

Ryan

  • 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.
Photo of dwhawe

dwhawe, Champion

  • 908 Points 500 badge 2x thumb
That's why there is a pillow in my office.  Not for naps but to dampen the screams of frustration.
Photo of Ryan

Ryan

  • 0 Points
Wouldn't bother me except the written directions clearly stated ". . . . an average of . . . " arghhhhh!