IF Statement question

[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

[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.


"Patrick Simonds" <ordnance1@comcast.net> wrote in message
news:uLEmnnyUFHA.3544@TK2MSFTNGP10.phx.gbl...
> 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
>

[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



"Patrick Simonds" <ordnance1@comcast.net> wrote in message
news:uLEmnnyUFHA.3544@TK2MSFTNGP10.phx.gbl...
> 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
>
>

[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



"Patrick Simonds" <ordnance1@comcast.net> wrote in message
news:Owzw2qyUFHA.228@TK2MSFTNGP12.phx.gbl...
> I should have said I want the IF Statement to return a blank cell if there
> is text in any of the referenced cells.
>
>
> "Patrick Simonds" <ordnance1@comcast.net> wrote in message
> news:uLEmnnyUFHA.3544@TK2MSFTNGP10.phx.gbl...
> > 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
> >
>
>

[Harlan Grove]

"CLR" <croberts@tampabay.rr.com> wrote...
>Well then..........maybe this.......
>
>=IF(OR(ISTEXT(BN23),ISTEXT(BO23),ISTEXT(BP23),ISTEXT(BQ23),ISTEXT(BX23)),""
,
>PutYourFormulaHere)

This works.

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

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,"")

[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



"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:eGclfS1UFHA.1384@TK2MSFTNGP09.phx.gbl...
> "CLR" <croberts@tampabay.rr.com> wrote...
> >Well then..........maybe this.......
> >
>
>=IF(OR(ISTEXT(BN23),ISTEXT(BO23),ISTEXT(BP23),ISTEXT(BQ23),ISTEXT(BX23)),""
> ,
> >PutYourFormulaHere)
>
> This works.
>
> >or this
> >
> >=IF(COUNTA(BN23:BQ23,BX23)>COUNT(BN23:BQ23,BX23),"",PutYourFormulaHere)
> ...
>
> 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,"")
>
>

[Harlan Grove]

"CLR" <croberts@tampabay.rr.com> wrote...
....
>=IF(COUNTA(BN23:BQ23,BX23)>COUNT(BN23:BQ23,BX23),"",IF((COUNT(BN23:BQ23)
>+COUNT(BX23))=5,BX23-BQ23-BP23-BO23-BN23,""))

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,""))
....

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.

[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 ?
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.
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.
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