Ignoring blanks

**jswainson** · 08-09-2012, 01:57 PM

I have 3 columns. The first has dates in and the 2nd and 3rd have returns in, both positive and negative. When the return in column m is positive and the return in column n is negative (or vice versa) i want the date in column c to be returned. So far so good. The problem is that some of the cells are blank due to dodgy data.

=IF(AND(M22<0,N22>0),C22," ")

When the above formula comes across a negative value in column m and a blank in column n, it still returns the date in column c. I want it to not return a date.

Any ideas?

And thank you so much to everyone who's been helping me on here - i only discovered these forums a few days ago and you've helped enormously!

Thanks
Jo

**CheshireCat** · 08-09-2012, 02:03 PM

How about:

Please Login or Register  to view this content.

**ChemistB** · 08-09-2012, 02:04 PM

First, a blank is "", not " ". You should modify your formulas to fix that. (You can use Find and replace; CNTRL H)
Is it only in column N that you're getting "blanks"?
Try;
=IF(AND(M22<0,N22>0, N22<>""),C22,"")

**K m** · 08-09-2012, 02:05 PM

=if(and(m22<0,n22>0,m22<>""),c22," ")

**jswainson** · 08-09-2012, 02:09 PM

Thanks all. Have used the 'isnumber' one as have blanks in both columns..

Which leads me to my next question..

The formula in the next column is looking at where m is negative and n is positive:

=IF(AND(M22>0,N22<0),C22," ") (will amend to the isnumber version).

I'm basically looking to see where the retruns deviate either pos/neg or neg/pos and can do this across 2 columns but would be neater if only in one. But to do that i think i need a if(and(or(... statement and i don't think you can nest that many statements in excel? Or can you?

Thanks very much

Jo