Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF Statement question
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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,PutYourFormu laHere,"NotAll5CellsAreNum bers") Vaya con Dios, Chuck, CABGx3 "Patrick Simonds" wrote in message ... 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 |
#4
|
|||
|
|||
Well then..........maybe this.......
=IF(OR(ISTEXT(BN23),ISTEXT(BO23),ISTEXT(BP23),ISTE XT(BQ23),ISTEXT(BX23)),"", PutYourFormulaHere) or this =IF(COUNTA(BN23:BQ23,BX23)COUNT(BN23:BQ23,BX23)," ",PutYourFormulaHere) Vaya con Dios, Chuck, CABGx3 "Patrick Simonds" wrote in message ... 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" wrote in message ... 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 |
#5
|
|||
|
|||
"CLR" wrote...
Well then..........maybe this....... =IF(OR(ISTEXT(BN23),ISTEXT(BO23),ISTEXT(BP23),IST EXT(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,PutYourF ormulaHere,"") |
#6
|
|||
|
|||
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(OPr ange)=5,BX23-BQ23-BP23-BO2 3-BN23,"")) Vaya con Dios, Chuck, CABGx3 "Harlan Grove" wrote in message ... "CLR" wrote... Well then..........maybe this....... =IF(OR(ISTEXT(BN23),ISTEXT(BO23),ISTEXT(BP23),IST EXT(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,PutYourF ormulaHere,"") |
#7
|
|||
|
|||
"CLR" 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(OP range)=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. |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement question | Excel Worksheet Functions | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
How to calculate a sum as one outcome of an IF statement | Excel Worksheet Functions | |||
If Statement Question | Excel Worksheet Functions | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions |