If NOT formula for Status

D. Turner

I am having trouble with a status formula, anyone have any suggestions?

=IF(NOT(ISBLANK([Bodorff interview recommended]@row)), "Quarter", IF(NOT(ISBLANK([Bright interview recommended]@row)), "Half", IF(NOT(ISBLANK([Kramer interview recommended]@row)), "Half", IF(NOT(ISBLANK([Suarez interview recommended]@row)), "Three Quarter", IF(NOT(ISBLANK([Wang interview recommended]@row)), "Full", "Empty"))))

John_Foster

Hi @D. Turner

The only issue with the formula above is a missing closing parenthesis.

Is the problem you have referring to that or referring to the data not showing what you would expect?

Hope this helps!

Thanks,
John

D. Turner

One last closure at the end? The cell is stating unparsable.

John_Foster

Hi @D. Turner,

I have it working on a sheet I created with the same column names.

Please find the formula copied from my sheet below.

=IF(NOT(ISBLANK([Bodorff interview recommended]@row)), "Quarter", IF(NOT(ISBLANK([Bright interview recommended]@row)), "Half", IF(NOT(ISBLANK([Kramer interview recommended]@row)), "Half", IF(NOT(ISBLANK([Suarez interview recommended]@row)), "Three Quarter", IF(NOT(ISBLANK([Wang interview recommended]@row)), "Full", "Empty")))))

Thanks,
John

D. Turner

I will give that a try, thanks!

D. Turner

That's weird. I added in the extra close parenthesis and also re-typed the whole formula in and still getting the unparsable error.

John_Foster

@D. Turner you are welcome.

Having used the formula on my sheet one thing to note is if the column [Bodorff interview recommended] has a value in it, the column the formula is in will only ever show as Quarter. If you want the column to become more full as the other columns contain values, the formula will need reversing. I have included the reversed formula below.

=IF(NOT(ISBLANK([Wang interview recommended]@row)), "Full", IF(NOT(ISBLANK([Suarez interview recommended]@row)), "Three Quarter", IF(NOT(ISBLANK([Kramer interview recommended]@row)), "Half", IF(NOT(ISBLANK([Bright interview recommended]@row)), "Half", IF(NOT(ISBLANK([Bodorff interview recommended]@row)), "Quarter", "Empty")))))

Thanks,
John

John_Foster

@D. Turner

If you want to share the sheet with me or a copy of it, I would be happy to take a look for you.

Thanks,
John

D. Turner

President & CEO candidates.xlsx

This is the sheet. I have it working as long as I don't use Bright or Bodorff. I am trying to figure out what the issue is why the formula won't take those two columns.

John_Foster

@D. Turner I believe the issue is with the column name, for example in the file you attached the Bordorff column has two spaces after the word Bordoff, but the formula does not.

When I created the field in my sheet I typed it with one space which is why it is working.

John_Foster

@D. Turner and having just checked the Bright column it has a trailing space after the word recommended and the formula does not.

D. Turner

I found the issue. There were a couple of spaces in the names of the columns that was throwing it off. Thanks for your assistance!

D. Turner

One more question. This also causes an issue if Wang goes first it will show as full. What if I just waned to count the rows filled 1-5? to show a full status? Is that possible?

John_Foster

@D. Turner try the following formula.

=IF(COUNT([Bodorff interview recommended]@row:[Wang interview recommended]@row) = 5, "Full", IF(COUNT([Bodorff interview recommended]@row:[Wang interview recommended]@row) = 4, "Three Quarter", IF(COUNT([Bodorff interview recommended]@row:[Wang interview recommended]@row) = 1, "Quarter", IF(COUNT([Bodorff interview recommended]@row:[Wang interview recommended]@row) = 0, "Empty", "Half"))))

I have assumed that when 2 or 3 options are completed it is "Half" as this is what the previous formula indicated.

Thanks,
John