#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Linked cell skewing formula result

Options
✭✭✭✭
edited 06/22/17

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

Tags:

• Employee
edited 06/23/17
Options

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")

• ✭✭✭✭
edited 06/26/17
Options

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

Bill

• ✭✭✭✭
Options

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?

Bill

• Employee
Options

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.

• ✭✭✭✭
Options

That was it!  Many thanks.

Bill

This discussion has been closed.