Tricky Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Duncan - 10 Oct 2006 09:41 GMT

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 but it will probably go out of alignment so will also
attempt to explain.

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?

Reply to this Message

smw226 - 10 Oct 2006 10:10 GMT

Hi,

This will work if the cell will either be blank or have a value in it
(whether date or anything else):

=IF(OR(AND(A4="",B4="",C4=""),AND(A4<>"",B4="",C4<>""),AND(A4<>"",B4<>"",
C4<>"")),1,IF(OR(AND(A4<>"",B4="",C4=""),AND(A4<>"",B4<>"",C4="")),0,"Error"))

The If false of the second IF will catch any anomolys

HTH

Thanks,

Simon

>Hi Guys,
>
[quoted text clipped - 18 lines]
>
>Any ideas?

Signature

--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Reply to this Message

Duncan - 10 Oct 2006 10:18 GMT

Hi There Simon,

I also got

=--(OR(COUNTIF(A10:C10,"")=3,AND(AND(ISNUMBER(A10),ISNUMBER(C10)),OR(ISNUMBER(B10),ISBLANK(B10)))))

from Bob Phillips, but yours also works perfectly,

Many thanks

Duncan

> Hi,
>
[quoted text clipped - 43 lines]
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-new/200610/1

Reply to this Message

Roger Govier - 10 Oct 2006 11:01 GMT

Hi Duncan

alternatively
=--(AND(ISNUMBER(A1),ISNUMBER(C1)))+(--(COUNT(A1:C1)=0))

Signature

Regards

Roger Govier

> Hi There Simon,
>
[quoted text clipped - 58 lines]
>> Message posted via OfficeKB.com
>> http://www.officekb.com/Uwe/Forums.aspx/excel-new/200610/1

Reply to this Message

Duncan - 10 Oct 2006 12:46 GMT

Thank you Roger,

Yours also works perfectly,

I wonder if anyone would have the time to break the formula down to me
and explain how it works?

Many thanks

Duncan

> Hi Duncan
>
[quoted text clipped - 68 lines]
> >> Message posted via OfficeKB.com
> >> http://www.officekb.com/Uwe/Forums.aspx/excel-new/200610/1

Reply to this Message

Bob Phillips - 10 Oct 2006 12:56 GMT

How dare you come up with a better formula than me. For that I will take you
to task for an unnecessary use of --

=(AND(ISNUMBER(A1),ISNUMBER(C1)))+(COUNT(A1:C1)=0)

the + does the coercing for you <G>

Mine needed it as it was all in one logical test.

Signature

HTH

Bob Phillips

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

> Hi Duncan
>
[quoted text clipped - 4 lines]
> >
> > I also got

=--(OR(COUNTIF(A10:C10,"")=3,AND(AND(ISNUMBER(A10),ISNUMBER(C10)),OR(ISNUMBE
R(B10),ISBLANK(B10)))))

> > from Bob Phillips, but yours also works perfectly,
> >
[quoted text clipped - 6 lines]
> >> This will work if the cell will either be blank or have a value in it
> >> (whether date or anything else):

=IF(OR(AND(A4="",B4="",C4=""),AND(A4<>"",B4="",C4<>""),AND(A4<>"",B4<>"",

C4<>"")),1,IF(OR(AND(A4<>"",B4="",C4=""),AND(A4<>"",B4<>"",C4="")),0,"Error"
))

> >> The If false of the second IF will catch any anomolys
> >>
[quoted text clipped - 38 lines]
> >> Message posted via OfficeKB.com
> >> http://www.officekb.com/Uwe/Forums.aspx/excel-new/200610/1

Reply to this Message

Roger Govier - 10 Oct 2006 13:51 GMT

Hi Bob

> How dare you come up with a better formula than me.

One of those very rare occasions<vbg> and you are quiet right about the
coercion (of course).

Duncan it works as follows.
From your sample,
If A and C both contain Dates, then you want a 1.
You also want a 1 if A, B and C contain dates, but that is taken care of
by the above, so doesn't require a separate condition.
so
=(AND(ISNUMBER(A1),ISNUMBER(C1)))
returns either True or False.
When we operate upon the result, (adding 0 to it or using the double
unary minus -- ) this coerces the value to 1 for True or 0 for False.

The other condition where you want a 1, is if A, B and C are all empty
so
=(COUNT(A1:C1)=0 returns True where this is the case, and False if it
isn't

Since both conditions can't be met at the same time (all three cells
can't be empty, if A1 and C1 contain a date) adding the two conditions
together produces the correct result.
in your 5 rows we have
0 + 1 =1
1 + 0 = 1
1 + 0 = 1
0 + 0 = 0
0 + 0 = 0

Signature

Regards

Roger Govier

> How dare you come up with a better formula than me. For that I will
> take you
[quoted text clipped - 81 lines]
>> >> Message posted via OfficeKB.com
>> >> http://www.officekb.com/Uwe/Forums.aspx/excel-new/200610/1

Reply to this Message

Harlan Grove - 10 Oct 2006 19:56 GMT

Bob Phillips wrote...

>How dare you come up with a better formula than me. For that I will take you
>to task for an unnecessary use of --
>
>=(AND(ISNUMBER(A1),ISNUMBER(C1)))+(COUNT(A1:C1)=0)

...

Excessively verbose.

=(COUNT(A8,C8)=2)+(COUNT(A8:C8)=0)

Reply to this Message

Bob Phillips - 10 Oct 2006 20:08 GMT

and here was us thinking that you liked verbosity Harlan <g>

Signature

HTH

Bob Phillips

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

> Bob Phillips wrote...
> >How dare you come up with a better formula than me. For that I will take you
[quoted text clipped - 6 lines]
>
> =(COUNT(A8,C8)=2)+(COUNT(A8:C8)=0)

Reply to this Message

Roger Govier - 10 Oct 2006 22:13 GMT

Neat!

Signature

Regards

Roger Govier

> Bob Phillips wrote...
>>How dare you come up with a better formula than me. For that I will
[quoted text clipped - 7 lines]
>
> =(COUNT(A8,C8)=2)+(COUNT(A8:C8)=0)

Reply to this Message