Pass or Fail problem
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Pass or Fail problem

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Please could you provid some additional information.

    The formula you currently have.
    The values that it's looking at.
    Any expected results.
    "Have a good time......all the time"
    Ian Mac

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What about,

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

    any help?
    "Have a good time......all the time"
    Ian Mac

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I couldn't resist trying:

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

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #9
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"))
    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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.
    "Have a good time......all the time"
    Ian Mac

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com