tricky formula

[Duncan]

Hi Guys,

been a while since ive been on here!

I am trying to work out a tricky formula...I will attempt to paste an
example below.


Header1 Header2 Header3 Formula Values
1
Date Date 1
Date Date Date 1
Date Date 0
Date 0


Now if header 1 2 and 3 are blank then I want a result of 1 from my
formula, if there are dates in all columns then I want a result of 1,
and same if only B column is blank (or header2), but then if 3 is blank
or 3 + 2 are blank then I want a result of 0.


Any ideas?

 

[Bob Phillips]

=--(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNUMBER(C2)),OR(ISNUMBER(B2
),ISBLANK(B2)))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Duncan]

Hi Bob,

Thanks for your swift reply, but I pasted it straight in and came up
with an error, am i missing something?

Many thanks

Duncan

 

[Duncan]

Like the novice that I am I changed it to

=(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNUMBER(C2)),OR(ISNUMBER(B2),ISBLANK(B2)))))

But that brings up true or false and i want it to bring up a 1 or a 0,
also doesnt bring up the true or false in the right contexts..... I
think I am being stupid.....

 

[Duncan]

Ok, I was being stupid, I pasted it in again correctly and it reads

=--(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNUMBER(C2)),OR(ISNUMBER(B2),ISBLANK(B2)))))

But it works for when all 3 are balnk, but shows 0 for all other
combinations....

 

[Duncan]

Ok, I am really embarressed now, Bob's suggestion worked perfectly, I
have got tuesday morning blues and had an error in my tests and not
with the formula,

I am extremely sorry Bob, I should have know that it would work coming
from you!

Many thanks again,

Duncan

 

[Bob Phillips]

Luckily I went out Duncan, so I didn't see these until you had resolved it
all <BG>

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Duncan]

Hi Bob,

I wonder if you have the time to break this formula down for me and
explain how it works? that way I might be able to recreate it at
another time with the knowledge instead of just copying the formula.

I do have a basic understanding of formulas and maths but working out
how your formula works is giving me a headache!

Many many thanks

Duncan

 

[Bob Phillips]

Duncan,

It is quite straight-forward, I just put all of your logic into separate
groups

OR(COUNTIF(A2:C2,"")=3 will test fro all blank

AND(ISNUMBER(A2),ISNUMBER(C2)) tests for column A and C being a date
(actually just a number, but Excel doesn't have a date type)

OR(ISNUMBER(B2),ISBLANK(B2)) tests for B being a date or blank

we join the last two with an AND,

AND(AND(ISNUMBER(A2),ISNUMBER(C2)),OR(ISNUMBER(B2),ISBLANK(B2))))

so we effectively test for A being a date, B being a date OR blank, and C
being a date

Combining this with OR

OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNUMBER(C2)),OR(ISNUMBER(B2),IS
BLANK(B2))))

tests for all blank, OR A being a date, B being a date OR blank, and C being
a date

This would just return TRUE or FALSE, so we need to coerce it to 1 or 0,
this is what the leading -- does.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)