Tricky Formula

  • Thread starter Thread starter Duncan
  • Start date Start date
D

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 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?
 
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,

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?
Click to expand...

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

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

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200610/1
 
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,

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,

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?
Click to expand...

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

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

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200610/1
Click to expand...
 
Hi Duncan

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

--
Regards

Roger Govier


Duncan said:
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,

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,

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?
Click to expand...

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

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

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200610/1
Click to expand...
Click to expand...
 
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


Roger said:
Hi Duncan

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

--
Regards

Roger Govier


Duncan said:
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,

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

Duncan wrote:
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?

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

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

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200610/1
Click to expand...
Click to expand...
Click to expand...
 
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.

--
HTH

Bob Phillips

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

Roger Govier said:
Hi Duncan

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

--
Regards

Roger Govier


Duncan said:
Hi There Simon,

I also got
Click to expand...
=--(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,

Many thanks

Duncan

Hi,

This will work if the cell will either be blank or have a value in it
(whether date or anything else):
Click to expand...
C4 said:
The If false of the second IF will catch any anomolys

HTH

Thanks,

Simon

Duncan wrote:
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?

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

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

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200610/1
Click to expand...
Click to expand...
Click to expand...
 
Hi Bob
How dare you come up with a better formula than me.
Click to expand...
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


--
Regards

Roger Govier


Bob Phillips said:
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.

--
HTH

Bob Phillips

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

Roger Govier said:
Hi Duncan

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

--
Regards

Roger Govier


Duncan said:
Hi There Simon,

I also got
Click to expand...
=--(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,

Many thanks

Duncan


smw226 via OfficeKB.com wrote:
Hi,

This will work if the cell will either be blank or have a value in
it
(whether date or anything else):
Click to expand...
C4 said:
The If false of the second IF will catch any anomolys

HTH

Thanks,

Simon

Duncan wrote:
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?

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

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

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200610/1
Click to expand...
Click to expand...
Click to expand...
 
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)
Click to expand...
....

Excessively verbose.

=(COUNT(A8,C8)=2)+(COUNT(A8:C8)=0)
 
and here was us thinking that you liked verbosity Harlan <g>

--
HTH

Bob Phillips

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

tricky formula 8
Excel Help with dates 2
Formula to display Count of Days in given month, using list of Startand End Dates 1
Can't change the cell back to blank when there is a date in it. 0
SUMIF where the IF is a variable date from data in a column HOW? 3
Same formula but isnt working 4
I copied Visible Cells Only, but it pastes only the first cell 1
is Retrieving a Cell address from a cell containing a SUMPRODUCTformula a problem ? 5

Back
Top