Entries when fields are still blank

[Adam]

Hi

I'm using the following formula for my mail monitoring spreadsheet:

=IF(N13<=L13,"Yes",IF(N13>L13,"No", IF(J13=No,"")))

Column J is the "Response required?" column
Column L is the "Target Date" column
Column N is the "Actual Response Date" column
Column P is the "On target?" column

There are only 3 entries on my spreadsheet which were all responded to
within target. Therefore there is a "Yes" in column P in all three rows.

HOWEVER, the word "Yes" continues all the way down column P even though, as
I've said, there are only three entries.

Could anyone advise me how to keep column P blank unless there is an entry
on the row (without losing the formula!)

Sorry for the wordiness of this question.

Regards,
Adam

 

[Pete_UK]

I think you want something like this:

=IF(COUNTA(J13,L13,N13)=0,"",IF(J13="No","",IF(N13<=L13,"Yes","No")))

Hope this helps.

Pete

 

[David Biddulph]

A number of problems:

There's no point in testing for N13>L13, as you've already trapped the cases
where N13<=L13
If you could get to the test for J13 (which you can't, see above) it will
fail unless No is a defined name. If you want to test for the text string
"No", it needs to be in quotes.

To get back to your question, if you want your formula to return a blank if
N is empty, then try =IF(N13="","",IF(...
and then continue with whatever other conditions you want.

 

[Adam]

David

Thank you for your reply. However it created a circular reference and I'm no
further forward. Could you possibly type out in full what you propose so that
I can see where I am going wrong (please include your suggested change about
having already trapped the cases where N13<=L13).

Thanks,
Adam
P.S I know a David Biddulph in Edinburgh

 

[David Biddulph]

I don't know where your circular reference is coming from, Adam, as I
assumed that your formula was going into P13, and the formula doesn't refer
to P13.

It isn't clear what you want the priority of the various tests to be, but
perhaps
=IF(N13="","",IF(J13="No","",IF(N13<=L13,"Yes","No")))
or
=IF(OR(N13="",J13="No"),"",IF(N13<=L13,"Yes","No"))

You may wish to change the J13="No" criterion to J13<>"Yes", and then you'd
get a blank result either if J13 was No, or if J13 was blank.

[I'm a long way South of Edinburgh]