Pass or Fail problem

[Gaz_Royal]

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.

 

[Ian Mac]

Please could you provid some additional information.

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

 

[Gaz_Royal]

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

 

[Tim Francis-Wright]

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.

 

[Ian Mac]

What about,

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

any help?

 

[Dave Hawley]

I couldn't resist trying:

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

 

[Gaz_Royal]

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

 

[Gaz_Royal]

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.

 

[Tim Francis-Wright]

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

 

[Ian Mac]

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.