Thanks:  0
Likes:  0

Thread: Pass or Fail problem

1. Ok firstly thanks for reading this post I really do need some help

I have a row of 5 codes for individual marks - P for Pass and F for Fail. In the last column I have the overall result - P if all individual results were P and F if any of the 5 results were an F.

My problem is that if no data has been entered the final result defaults to an F for Fail when in fact I would like it to show blank or space.

I think this problem is something to do with NULL value but I'm not sure what to do to resolve this. Any help or guidance will be greatly appreciated. Cheers.

2. Please could you provid some additional information.

The formula you currently have.
The values that it's looking at.
Any expected results.

3. Ok here goes Ian

current formula
---------------

=IF(H2="P",IF(I2="P",IF(J2="P",IF(K2="P",IF(L2="P",IF(M2="P",IF(N2="P",IF(O2="P","P","F"),"F"),"F"),"F"),"F"),"F"),"F"),"F")

values that its looking at
--------------------------

9 cells, 8 containing either P for Pass, F for fail and a third data type being Null i think. The final cell is an end result cell (see below)

Expected results
----------------

If all 8 cells contain P then the end result should be P. If any cell contains F then the end result should be F. My problem is that if no data has been entered in any cell the end result defaults to F when I would like it to default to " " (space)

Cheers

4. On 2002-03-11 06:02, Gaz_Royal wrote:
Ok here goes Ian

current formula
---------------

=IF(H2="P",IF(I2="P",IF(J2="P",IF(K2="P",IF(L2="P",IF(M2="P",IF(N2="P",IF(O2="P","P","F"),"F"),"F"),"F"),"F"),"F"),"F"),"F")

values that its looking at
--------------------------

9 cells, 8 containing either P for Pass, F for fail and a third data type being Null i think. The final cell is an end result cell (see below)

Expected results
----------------

If all 8 cells contain P then the end result should be P. If any cell contains F then the end result should be F. My problem is that if no data has been entered in any cell the end result defaults to F when I would like it to default to " " (space)

Cheers
Try this control-shift-enter formula:

=IF(OR(H2:O2="",H2:O2=" ")," ",IF(AND(H2:O2="P"),"P","F"))

It should do what you want.

5. What about,

=IF(COUNTA(H2:O2)<8,"",IF(COUNTIF(H2:O2,"P")<8,"F","P"))

any help?

6. I couldn't resist trying:

=IF(COUNTA(H2:O2)<1,"",CHOOSE(COUNTIF(H2:O2,"P"),"F","F","F","F","F","F","F","P"))

7. Thanks for the assistance guys...will check each one right away and let you know the outcome.

8. Tim:

=IF(OR(H5:O5=" ",H5:O5=" ")," ",IF(AND(H5:O5="P"),"P","F"))

couldn't get this to work at first but then realised I'd forgotten to hit CNTRL+SHIFT+ENTER. This put a {} swirly bracket around the formula and bingo worked great. Not sure what the swirly thing does but its great

Ian:

=IF(COUNTA(H2:O2)<8," ",IF(COUNTIF(H2:O2,"P")<8,"F","P"))

This worked fine for F's and P's but didnt like my NULL cells - still defaulted to F until I physically deleted the contents of one of the 8 cells.

Dave:

=IF(COUNTA(H2:O2)<1," ",CHOOSE(COUNTIF(H2:O2,"P"),"F","F","F","F","F","F","F","P"))
Again this worked fine for F's and P's but returned "value" error for cells containing my NULL values until I deleted ALL cell contents.

Once again guys, thanks for the response - fantastic help and my problem has been sorted. Cheers.

9. There's a way to do this without the array formula. It's longer but faster if you have
a large spreadsheet. (You also don't have to remember a special entry method if you
edit it.)

=IF(COUNTIF(H5:P5,"P")+COUNTIF(H5:P5,"F")<8,"",IF(COUNTIF(H5:P5,"P")=8,"P","F"))

10. On 2002-03-11 07:38, Gaz_Royal wrote:
Tim:

=IF(OR(H5:O5=" ",H5:O5=" ")," ",IF(AND(H5:O5="P"),"P","F"))

couldn't get this to work at first but then realised I'd forgotten to hit CNTRL+SHIFT+ENTER. This put a {} swirly bracket around the formula and bingo worked great. Not sure what the swirly thing does but its great

Ian:

=IF(COUNTA(H2:O2)<8," ",IF(COUNTIF(H2:O2,"P")<8,"F","P"))

This worked fine for F's and P's but didnt like my NULL cells - still defaulted to F until I physically deleted the contents of one of the 8 cells.

Dave:

=IF(COUNTA(H2:O2)<1," ",CHOOSE(COUNTIF(H2:O2,"P"),"F","F","F","F","F","F","F","P"))
Again this worked fine for F's and P's but returned "value" error for cells containing my NULL values until I deleted ALL cell contents.

Once again guys, thanks for the response - fantastic help and my problem has been sorted. Cheers.

My formula should work fine for NULL values as should Dave's, I can only think you have the " "'s in the cells you wanted in the first place. OR are the results coming from an IF statement or LOOKUP where your return is " ", if so change to "" without the space.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•