Help with Nested If Statement

[jstarr]

I'm trying to come up with an IF statement with 3 criteria. Looking for the boards help.

The table is pulling data from another worksheet on the spreadsheet.

1. IF Column J is not blank=Yes
2. If Column J is blank=No
3. If Column J is 0=Not yet

I came up with this formula, but the columns that should be returning Not yet, are returning yes.

=IF([Column J]<>"","Yes",IF([Column J]="","No",IF([Column J]=0,"Not Yet")))

Thanks!

 

[Fishboy]

Hi Jstarr, the reason it is returning yes instead of not yet is because the presence of a 0 in column J means the column is not blank as per <>""

 

[jstarr]

How do I fix?

 

[Fishboy]

Try this:

=IF(AND(J:J<>"",J:J<>0),"Yes",IF(AND(J:J=0,J:J<>""),"Not Yet","No"))

J:J can be replaced with your named range

 

[jstarr]

Thanks so much. Is there a way for me to run a pivot table from this dataset that only counts the "Yes" values? I'm trying to count what percent of clients we retain.

 

[Fishboy]

You're welcome. Feel free to hit the Like button on my previous post to show it was the right answer.

With regards to your second query, do you have some example data we can look at? That would help immensely to visualise your data set and allow us to try and help you further. If you google MrExcel HTML Maker you will be able to copy / paste a snippet of your workbook directly from Excel straight onto the forums.