Linked cell skewing formula result

Bill Lafferty

I am trying to write a formula that checks for 4 criteria;

a. That the result in cell [Transition]38 = "Transition"

b. That cell [NPS %]38 is not blank

c. That cell [BG KPI]38 is not blank

d. That cell [LE CM %]38  is not blank

If all 4 criteria are met, then YES, otherwise NO.

Of the cells referenced above, only [NPS %]38 and [BG KPI]38 are linked from another sheet.

Example:

=IF(Transition38 = "Transition", IF(ISBLANK([NPS %]38), IF(ISBLANK([BG KPI]38), IF(ISBLANK([LE CM %]38), "NO", "YES"))))

Problem:

The cells [NPS %]38) and BG KPI]38) are linked fields but contain no values. I think they are causing the formula above to generate no result (the cell looks empty).

I also tried the following:

=IF(Transition38 = "Transition", IF(ISNUMBER([NPS %]38),IF(ISNUMBER([BG KPI]38), IF(ISNUMBER([LE CM %]38), "YES", "NO")))

This resulted in the same 'blank' result as described above.

However, when I removed the reference to [NPS %]38 (as below), this formula worked. .

=IF(Transition38 = "Transition", IF(ISNUMBER([BG KPI]38), IF(ISNUMBER([LE CM %]38), "YES", "NO")))

Can anyone think of a way to solve for this?

Thanks.

Bill

Shaine Greenwood

Hi William,

Tie your criteria together with the AND() function and use NOT(ISBLANK()), e.g.:

=IF(AND(Transition38 = "Transition", NOT(ISBLANK(NPS%38)), NOT(ISBLANK([BG KPI]38)), NOT(ISBLANK([LE CM %]38))), "YES", "NO")

Bill Lafferty

Hi Shaine - Thanks for the idea.  When I copied your suggestion verbatim I got an  #UNPARSEABLE error.  Any ideas?

Bill

Bill Lafferty

Shaine - I found the issue with the syntax above.  The column referred to in the formula was missing a space in the name.  It should have been [NPS %] 38.  It returns Yes/No with that fix.  



However, see the below screen shot. When copied to rows where there are no values in the "NPS %", "BG KPI", or "LE CM %" columns, it returns a false positive result. Any other ideas?



Thanks in advance.

Bill



 

Shaine Greenwood

Hi Bill,

Looks like you've got the return values flipped to be "NO", "YES" versus what's in my formula.

Try flipping those to ..."YES", "NO") instead: 

=IF(AND(Transition38 = "Transition", NOT(ISBLANK([NPS %]38)), NOT(ISBLANK([BG KPI]38)), NOT(ISBLANK([LE CM %]38))), "YES", "NO")

The formula logic that I'm going with is (sorry if this sounds repetitive): if Transition contains the string "Transition" and NPS % is not blank and BG KPI is not blank and LE CM % is not blank, return the string "YES" in the cell. Otherwise, return the string "NO" in the cell.

Bill Lafferty

That was it!  Many thanks.

Bill