• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can I ignore text from an IF statement?

ClaireNewski

New Member
I am trying to create a dashboard which summarises performance over the year.

Performance is calculated against 2 measures (target and minimum service level - TSL/MSL) and there are 3 outcomes - achieve TSL, achieve MSL, failed.

I need to display the % achievement and whether this meets the TSL, MSL or is a fail. To do this I have used conditional formatting to change the cell colour based on the outcome.

In addition to displaying the % achievement, there are 3 abbrieviations that may be used - NA, NV and NL. This is where the complication comes in.

On my dashboard I want to total how many criteria has met TSL, MSL and failed and how many have been classified as NA, NV and NL by month.

I have tried using the following formula to convert the achievement to either TSL, MSL or failed but it does not recognise the NA, NV and NL entries and counts these as achieving TSL.

=IF(G3>=$E3,"TSL",IF(AND(G3<$E3,G3>=$F3),"MSL",IF(G3<$F3,"Failed")))

How can I amend or rewrite this formula to ignore the NA, NV and NL entries?

I have tried adding a VBA to count the coloured cells but it made everything run too slowly. The workbook hangs for 60 seconds everytime I entered a new cell.

Thanks
 

Attachments

  • Chandoo 01.05.2014.xlsx
    32.1 KB · Views: 6
couldnt you just add an additional IF statement at the beginning?

first have it look to see if the cell = OR("NA","NV",NL"), if its true, have it do whatever it is you want and if its fales, then this is where your current formula would be....

hope im following you


EDIT: =IF(OR(K3="NV",K3="NL",K3="NA"),"",IF(K3>=$E3,"TSL",IF(AND(K3<$E3,K3>=$F3),"MSL",IF(K3<$F3,"Failed"))))

maybe something like this
 
also, in your row 49 you have "nil"... you'll need to add that to the new If function if thats going to be another option

Depending on what you want to be returned if N/A, NV, or NM is found you'd just need to edit this part of the formula
=IF(OR(K3="NV",K3="NL",K3="NA"),"",IF(K3>=$E3,"TSL",IF(AND(K3<$E3,K3>=$F3),"MSL",IF(K3<$F3,"Failed"))))

EDIT: new formula
=IF(OR(I3="blank",ISBLANK(I3)),"",IF(OR(I3="NV",I3="NL",I3="N/A"),"--",IF(I3>=$E3,"TSL",IF(AND(I3<$E3,I3>=$F3),"MSL",IF(I3<$F3,"Failed")))))

I added a new IF(OR)) to address the event of a cell containing "Blank" or being actually blank so that a value of TSL, MSL, or Failed is not returned.
 
Last edited:
Hi Claire ,

Can you not use a simple IF statement , on the lines of :

=IF(ISNUMBER(G3),IF(G3>=$E$3,"TSL",IF(G3>=$F$3,"MSL","Failed")),"No Result Possible")

This checks if G3 is a numeric quantity ; if not , it outputs "No Result Possible".

If G3 is a number , it checks it against the TSL and the MSL , and outputs the appropriate result.

Narayan
 
Hi ClaireNewski,

What the cell should display in case of a text entry like NA,NV, etc?

Regards,

I hadn't really thought of that. There are a number of different options, some which have been covered in this thread.

Do you know how I would display the original text?

Thanks.
 
couldnt you just add an additional IF statement at the beginning?

first have it look to see if the cell = OR("NA","NV",NL"), if its true, have it do whatever it is you want and if its fales, then this is where your current formula would be....

hope im following you


EDIT: =IF(OR(K3="NV",K3="NL",K3="NA"),"",IF(K3>=$E3,"TSL",IF(AND(K3<$E3,K3>=$F3),"MSL",IF(K3<$F3,"Failed"))))

maybe something like this

Thanks Json - that is so simple but I just couldn't see it.
 
Hi Claire ,

Can you not use a simple IF statement , on the lines of :

=IF(ISNUMBER(G3),IF(G3>=$E$3,"TSL",IF(G3>=$F$3,"MSL","Failed")),"No Result Possible")

This checks if G3 is a numeric quantity ; if not , it outputs "No Result Possible".

If G3 is a number , it checks it against the TSL and the MSL , and outputs the appropriate result.

Narayan

This is much better than my original formula, and thanks for the explanation, it helps me understand and learn a little more.
 
@ClaireNewski

ClaireNewski said:
I hadn't really thought of that. There are a number of different options, some which have been covered in this thread.

Do you know how I would display the original text?

Going forward with @NARAYANK991 Sir formula this can be done as show below:

=IF(ISNUMBER(G3),IF(G3>=$E$3,"TSL",IF(G3>=$F$3,"MSL","Failed")),G3)

Here blue part will put the original text. Just check the formula on your file.

Regards,
 
Back
Top