IF Statement question

  • Thread starter Patrick Simonds
  • Start date
P

Patrick Simonds

How can I construct an IF Statement that would perform the calculation below
only if all the referenced cells contained numbers? If any cell contains
text I do not want the IF Statement to return a blank cell.

=BX23-BQ23-BP23-BO23-BN23
 
P

Patrick Simonds

I should have said I want the IF Statement to return a blank cell if there
is text in any of the referenced cells.
 
C

CLR

I'm not sure exactly what all you want........but this formula may help get
you started..........

=IF(COUNT(BN23,BO23,BP23,BQ23,BX23)=5,PutYourFormulaHere,"NotAll5CellsAreNum
bers")

Vaya con Dios,
Chuck, CABGx3
 
C

CLR

Well then..........maybe this.......

=IF(OR(ISTEXT(BN23),ISTEXT(BO23),ISTEXT(BP23),ISTEXT(BQ23),ISTEXT(BX23)),"",
PutYourFormulaHere)

or this

=IF(COUNTA(BN23:BQ23,BX23)>COUNT(BN23:BQ23,BX23),"",PutYourFormulaHere)

Vaya con Dios,
Chuck, CABGx3
 
H

Harlan Grove

CLR said:
Well then..........maybe this.......

=IF(OR(ISTEXT(BN23),ISTEXT(BO23),ISTEXT(BP23),ISTEXT(BQ23),ISTEXT(BX23)),"" ,
PutYourFormulaHere)
Click to expand...

This works.
or this

=IF(COUNTA(BN23:BQ23,BX23)>COUNT(BN23:BQ23,BX23),"",PutYourFormulaHere)
Click to expand...
....

This may not work if the OP wants to exclude cells containing boolean or
error values, which aren't text but would be counted by COUNTA. For that
matter, it fails when all cells are blank.

Try

=IF(COUNTIF(BN23:BQ23,"*")+ISTEXT(BX23)=5,PutYourFormulaHere,"")
 
C

CLR

Thanks Harlan for drawing to my attention that I had not fully tested my
formulas, and left a couple of holes in them. I answered too quickly
without studying the whole criteria(s). I respectfully apologize to the OP
for that. After re-reading both of the OP's posts in this thread and doing
additional testing, I now submit the following.........

=IF(COUNTA(BN23:BQ23,BX23)>COUNT(BN23:BQ23,BX23),"",IF((COUNT(BN23:BQ23)+COU
NT(BX23))=5,BX23-BQ23-BP23-BO23-BN23,""))

of, if the cells of interest were collected into a Non-contiguious RangeName
"OPrange"........

=IF(COUNTA(OPrange)>COUNT(OPrange),"",IF(COUNT(OPrange)=5,BX23-BQ23-BP23-BO2
3-BN23,""))

Vaya con Dios,
Chuck, CABGx3
 
H

Harlan Grove

CLR said:
=IF(COUNTA(BN23:BQ23,BX23)>COUNT(BN23:BQ23,BX23),"",IF((COUNT(BN23:BQ23)
+COUNT(BX23))=5,BX23-BQ23-BP23-BO23-BN23,""))
Click to expand...

In the 2nd IF call, why (COUNT(BN23:BQ23)+COUNT(BX23))=5 rather than just
COUNT(BN23:BQ23,BX23)=5 ?
of, if the cells of interest were collected into a Non-contiguious RangeName
"OPrange"........

=IF(COUNTA(OPrange)>COUNT(OPrange),"",IF(COUNT(OPrange)=5,BX23-BQ23-BP23
-BO23-BN23,""))
Click to expand...
....

If the OP's original specs are correct, then

=IF(COUNT(OPrange)=5,formula,"")

would be sufficient. If COUNT(OPrange)=5 were true, COUNTA(.)>COUNT(.) would
always be FALSE, so the outer IF() serves no purpose.

If the supplemental specs really mean only return "" when any of the cells
in OPrange are text (not booleans or errors), your formulas above don't do
that.
 
C

CLR

Hi Harlan........

First off.........since both the original and the supplemented specs from
the OP were not the clearest, I think we have given him sufficient options
to decide if any one of them will work for him or at least enough ammo to be
able to come back with more specifics of his problem.

Next.............I truely appreciate you taking your time to help give me a
better understanding of these functions. I learn more and more each time I
come to these newsgroups, either to post an original question, or when I try
to help others. Given the opportunity to have constructive dialogue with
one of your stature is a real honor.

"Harlan Grove" said:
In the 2nd IF call, why (COUNT(BN23:BQ23)+COUNT(BX23))=5 rather than just
COUNT(BN23:BQ23,BX23)=5 ?
Click to expand...
Chuck's comment: No good reason at all, it's just what came to mind........
your suggestion is obviously better

"Harlan Grove" said:
If the OP's original specs are correct, then
=IF(COUNT(OPrange)=5,formula,"") would be sufficient.
If COUNT(OPrange)=5 were true, COUNTA(.)>COUNT(.) would
always be FALSE, so the outer IF() serves no purpose.
Click to expand...
Chuck's comment: I stand in awe, this is really really neat!


"Harlan Grove" said:
If the supplemental specs really mean only return "" when any of the cells
in OPrange are text (not booleans or errors), your formulas above don't do
that.
Click to expand...
Chuck's comment: Uh-huh, in retrospect I can see how one might conclude
that, but I didn't really take it that way. I figured he wanted an answer
only if he had numbers in all five cells, else "".


Once again, thank you Harlan for your time and patience in helping me along.

Vaya con Dios,
Chuck, CABGx3
 
Top