Expand my Community achievements bar.

SOLVED

If expression for missing values in Calculated field?

Avatar

Level 10

Hello to All,

I started to learn & work upon calculated expression, tried from my side but no success.

Goal is if SV1 is less and equal to 15, then Type A. If SV1 is more than 15, then Type B. Else, the empty field must show "Missing Value" if any of the field from VA / VB / VC / VD / VE is left blank. Then SV1 should show as "Missing value".

I tried to create expression with:

IF(SV1=0, "Missing Values",(IF(SV1>15, "Type A", IF(SV1<16, "Type B"))))

Attached overview for your kind reference.

Looking for your guidance.

Best regards,

Kundan.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi Kundan,

The following expression should do it:

IF(ISBLANK(SV1),"Missing Values",IF(SV1<=15,"Type A",IF(SV1>15,"Type B")))

Couple of pointers to help you along :

  • The 'IF(ISBLANK(Field),True,False' expressionis best when you want to output something when there is no value. Your current expression is looking for a '0' which is a value and will cause your expression to fail.
  • When you are using a less than or equal to operator you need to use '<='. '<' only looks at values that are less than

Hope that helps!

Best regards,

Rich.

View solution in original post

3 Replies

Avatar

Correct answer by
Community Advisor

Hi Kundan,

The following expression should do it:

IF(ISBLANK(SV1),"Missing Values",IF(SV1<=15,"Type A",IF(SV1>15,"Type B")))

Couple of pointers to help you along :

  • The 'IF(ISBLANK(Field),True,False' expressionis best when you want to output something when there is no value. Your current expression is looking for a '0' which is a value and will cause your expression to fail.
  • When you are using a less than or equal to operator you need to use '<='. '<' only looks at values that are less than

Hope that helps!

Best regards,

Rich.

Avatar

Level 10

Thanks a lot @Richard Leek‚ . I really appreciate your help in this regard.

I will check and confirm you soon.

Thank you once again.

Have a nice day!

Best regards,

Kundan.

Avatar

Level 10

Hi @Richard Leek‚

I tried the above and also added few more in. Then I reached the below one;

IF(SV1=0, "Missing Values",(IF(VA=0,"Field Value Missing",(IF(VB=0,"Field Value Missing",(IF(VC1=0,"Field Value Missing",(IF(VD=0,"Field Value Missing",(IF(VE=0,"Field Value Missing",IF(SV1>15, "Type A",IF(SV1<16, "Type B")))))))))))))

Then I achieved the result as attached.

I was just wondering, is it possible to reduce the above expression, its looks long expression but smaller meaning.

Thank you a again.

If you have to recommend for above then please suggest and recommend to reduce the size of above expression.

With regards,

Kundan.