ExCel Data Paste Issue

[Steveym]

Hello, I would like some help on a weird ExCel snag I am experiencing.

I have setup a formula for a specific list of data held within Column W, that works great! However, if I paste in new data to Columns J to R the formula no longer works as it should.

I think the issue is that when I paste the new data the formula reads the empty cell value as a zero, whereas previously it did not?

See images below; Top is the original that correctly picks up 'Unit 2' based on the formula, bottom is the newly pasted data which now reverts to 'Unit 4' as if there is a zero value within the cells of Column R.

I can delete the dataset, Paste Special and 'Skip Blanks' but the dataset has the same issue.

I must note that the same cells are not always populated so I cannot Paste Special and 'Skip Blanks' over the top of the existing data as some cells not previously blank remain.

Thanks Steven

 

[KillerOfGiants]

There are probably a few better ways to do the overall formula than a lot of nested if statements. So the overall formula might be able to help in that regard.
That being said, assuming the expected type of data in column N is the same as columns K and Q, you could try the following:
...AND(K1079="",N1079="",Q1079=""))...
or
=IF(LEN(K1079&N1079&Q1079)<=3),"Unit 1",...