Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Ignore blanks in If Statement - Calculated Column

Hello Guys,

 

I have a Column as Coverage and i need to Consolidate the Coverage into 3 categories. Below Formula works but the challange is for "Blank" rows, it consider as below 80 and returns the Coverage % as 80%. But it should not consider the Blanks. 

Can anybody help me please.

Calculated Column: Coverage % = IF('Summary'[Coverage]<0.80,"80%",IF('Summary'[Coverage]<0.90,"80% - 90 %","90%"))
CoverageCoverage %
1080%
080%
 80%
9390%
8080% - 90 %
 
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario.

     

    Then you could create a calculated column as follows.

     

    Coverage % = 
IF(
    ISBLANK(Summary[Coverage]),
    BLANK(),
    IF(
        Summary[Coverage]<0.8,
        "80%",
        IF(
            Summary[Coverage]<0.9,
            "80%-90%",
            "90%"
        )
    )
)

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

     

    Best Regards

    Allan

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi judspud 

       

      I Tried in below way by using AND, but again it takes blank into consideration. Looks like i am missing something but unable to find it.

       

      Coverage % = IF(AND('Summary'[Coverage]>=0,'Summary'[Coverage< 0.80),"80%",IF(AND('Summary'[Coverage>=0.80, Summary'[Coverage<0.90),"80% - 90 %",IF(AND('Summary'[Coverage>=0.90, 'Summary'[Coverage<100.90),"90%","")))
  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario.

     

    Then you could create a calculated column as follows.

     

    Coverage % = 
IF(
    ISBLANK(Summary[Coverage]),
    BLANK(),
    IF(
        Summary[Coverage]<0.8,
        "80%",
        IF(
            Summary[Coverage]<0.9,
            "80%-90%",
            "90%"
        )
    )
)

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

     

    If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

     

    Best Regards

    Allan