Combination of IF, AND, and COUNTIF; need help
in [Functions]
|
From: KL on 12 May 2010 15:07 Hi, I am trying to make a calculation that will take Pass/Fail values of P or F and give me an overall value of P or F. There is also an issue if a value is Not Tested, NT. For example, I have three levels of requirements (parent, child, and grandchild). Parent is level 1, Child is level 2, and Grandchild is level 3. For each grandchild requirement multiple tests can be completed with a P, F, or NT result. I have come up with a formula that will give me an overall result of P or F: =IF(COUNTIF(H8:H9,"F")>0,"F","P") to say If any value in the range is "F" the overall value is "F". This value would be put in cell H7. This repeats for the next grandchild requirement that would be in say cells H10:H12. Now I can make the same formula for Not Tested as =IF(COUNTIF(H8:H9,"NT")>0,"NT","") to give me a blank overall result if there are not tested values. The problem I am having is how to combine the two formulas. I would like a formula that says: If there are any Fs give me an overall F. If there are any NTs ignore them. If ALL are Ps give me an overall of P. I tried to do the following formula but it isn't working and I know I am missing something I just can't figure out what it is: =IF(AND(COUNTIF(H8:H10,"F")>0,"F","P"),(COUNTIF(H8:H10,"NT")>0,"NT",""),"P","") On top of all of that I also need the same type of formula to be in a cell that can't use a range for the COUNTIF. They would need to be individual cells. For example, Row 1's (parent) calculation needs to be based off of the overall pass/fail status of rows 2 and 15. Row 2's (child) calculations needs to be based off of the overall pass/fail status of rows 3 and 7. Row 3's (grandchild) calcuations are based off of the cell range. I tried the following but it didn't work: =IF(AND(COUNTIF(H3, H7, H10,"F")>0,"F","P"),(COUNTIF(H3, H7, H10,"NT")>0,"NT",""),"P","") I know this is a lot in one post but I thought it would be best to get everything out there. I would appreciate any and all help with this! Thank you!
From: Max on 13 May 2010 02:50 To satisfy this > If there are any Fs give me an overall F. > If there are any NTs ignore them. > If ALL are Ps give me an overall of P. One way: =IF(COUNTIF(H8:H10,"F")>0,"F",IF(COUNTIF(H8:H10,"P")=3,"P","")) Test it out, hit YES below for a thumbs up The "=3" bit in the check for "P" is the number of cells in the range H8:H10 Note that you can sequence the IF checks to suit as it will be evaluated from left to right -- Max Singapore ---
From: KL on 13 May 2010 07:28 Thanks! Max, That formula works great! > =IF(COUNTIF(H8:H10,"F")>0,"F",IF(COUNTIF(H8:H10,"P")=3,"P","")) > Test it out, hit YES below for a thumbs up > The "=3" bit in the check for "P" is the number of cells in the range H8:H10 > Note that you can sequence the IF checks to suit as it will be evaluated > from left to right However, now I need a formula for a group of cells (not in a range) that does the same thing. For example, H5 calculates the same thing but is based on cells H6, H14, and H18. The cells aren't in a range. Is there a way this can be done. When I try to replace a cell range in this formula with "H6,H14,H18" I get an error. Thanks for your help (and anyone help who can help)! I appreciate it! Karen :)
From: Max on 13 May 2010 11:05 For non contiguous cells, this is the equivalent of the earlier: =IF(OR(H6="F",H14="F",H18="F"),"F",IF(AND(H6="P",H14="P",H18="P"),"P","")) You could also use simple links to "place" all non contiguous cells into a contiguous range in an empty adjacent part of the sheet, then just use the earlier COUNTIF checks repointing to the new contiguous range. Or, if the intervening cells (ie H7:H13 and H15:H17) will not contain anything which could disturb the required checks, then you could just extend the earlier range to H6:H18. Joy? hit the YES below -- Max Singapore --- "KL" wrote: > Thanks! Max, That formula works great! > > =IF(COUNTIF(H8:H10,"F")>0,"F",IF(COUNTIF(H8:H10,"P")=3,"P","")) > However, now I need a formula for a group of cells (not in a range) that > does the same thing. > > For example, > > H5 calculates the same thing but is based on cells H6, H14, and H18. > > The cells aren't in a range. Is there a way this can be done. When I try to > replace a cell range in this formula with "H6,H14,H18" I get an error.
From: KL on 13 May 2010 12:09
Max you are awesome! I have to ask was I even close to being on the right track with the formula I had come up with? Ok so my next, and I believe last question, is how do I ignore if the cell is blank? For example: H1 would use the formula: =IF(OR(H6="F",H14="F",H18="F"),"F",IF(AND(H6="P",H14="P",H18="P"),"P","")) But say H6=P, H14=P, but H18=NT (or blank). Currently the formula gives a calue of Blank. But I need it to be able to result a P, as the blank would be ignored. The same would happen if H6=F, H14=P, and H18=NT (blank). The result would be H1=F, the blank in H18 would be ignored. Does that make sense? I do appreciate your help. I am pretty good at figuring out formulas in excel but I have been at a loss for this one. Karen :) "Max" wrote: > For non contiguous cells, this is the equivalent of the earlier: > =IF(OR(H6="F",H14="F",H18="F"),"F",IF(AND(H6="P",H14="P",H18="P"),"P","")) > > You could also use simple links to "place" all non contiguous cells into a > contiguous range in an empty adjacent part of the sheet, then just use the > earlier COUNTIF checks repointing to the new contiguous range. Or, if the > intervening cells (ie H7:H13 and H15:H17) will not contain anything which > could disturb the required checks, then you could just extend the earlier > range to H6:H18. Joy? hit the YES below > -- > Max > Singapore > --- > "KL" wrote: > > Thanks! Max, That formula works great! > > > =IF(COUNTIF(H8:H10,"F")>0,"F",IF(COUNTIF(H8:H10,"P")=3,"P","")) > > > However, now I need a formula for a group of cells (not in a range) that > > does the same thing. > > > > For example, > > > > H5 calculates the same thing but is based on cells H6, H14, and H18. > > > > The cells aren't in a range. Is there a way this can be done. When I try to > > replace a cell range in this formula with "H6,H14,H18" I get an error. > |