Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula to show absent (2000)

    Hello,

    I use below formula to detect whether my student pass or fail in exams..

    I use only 1 condition only to detect it..that is when a student get the mark

    below 40 , they will get FAIL..

    The problem is , recently many of my people absent and dont sit for the exams..

    So i cant put them on my database.. i mean their result..

    But I have to put their name in my list..and after thinking..i want their result to be ABSENT

    So how i modify below to show ABSENt when a student didnt sit for their exams..

    Thanks

    This is the formula


    =IF(OR(ISBLANK(L7),ISBLANK(N7),ISBLANK(P7),ISBLANK (R7),ISBLANK(T7)),"",IF(OR(L7<40,N7<40,P7<40,R7<40 ,T7<40),"FAIL","PASS"))

    Thanks

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to show absent (2000)

    Try

    =IF(OR(ISBLANK(L7),ISBLANK(N7),ISBLANK(P7),ISBLANK (R7),ISBLANK(T7)),"Absent",IF(MIN(L7, N7, P7, R7, T7)<40,"FAIL","PASS"))

    This assumes that the absence of a mark in any of the L7, N7, P7, R7, T7 columns indicates an absence.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to show absent (2000)

    Thanks for the help kieran..

    Unfortunately..it still shows "fail" instead of "absent" although i put absent along the row..

    any idea what went wrong ?

  4. #4
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to show absent (2000)

    Let me clarify this situation to make it more clear....

    The student needs to get all 40 and above(L7,N7,P7,R7,T7) in order to get PASS

    Along the row , if there are 39 and below(L7,N7,P7,R7,T7) , it will indicates FAIL

    Along the row also , if there is ABSENT (L7,N7,P7,R7,T7) , it will show ABSENT

    in the result

    I use vlookup to indicate the grade..40 and above is A , B , or C (PASS )

    39 and below D and E ( FAIL)

    if absent , i will put "-" and the vlookup will show ABSENT

    If there is ABSENT along the row , i need the formula to show ABSENt..thanks again

    Thanks

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula to show absent (2000)

    That formula does what you ask:
    If any of the scores are <40 the formula yields "FAIL"
    If any are empty it yields "Absent"
    Otherwise it yields "PASS"

    Are your scores PERCENTAGES? if so they are all <=1. Then perhaps you should use:

    =IF(OR(ISBLANK(L7),ISBLANK(N7),ISBLANK(P7),ISBLANK (R7),ISBLANK(T7)),"Absent",IF(MIN(L7, N7, P7, R7, T7)<0.40,"FAIL","PASS"))

    or

    =IF(OR(ISBLANK(L7),ISBLANK(N7),ISBLANK(P7),ISBLANK (R7),ISBLANK(T7)),"Absent",IF(MIN(L7, N7, P7, R7, T7)<40%,"FAIL","PASS"))

    since you want a score <0.40 as a failure not <40.

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to show absent (2000)

    What do the cells (L7,N7,P7,R7,T7) contain if the person is absent? Is the cell empty (which is what the formula is checking for), or does it contain something else like the word Absent or is there a formula in the cell? It sounds like the cell is not empty.

    Do you really want to show "Absent" if any one of the cells (L7,N7,P7,R7,T7) is empty?
    Legare Coleman

  7. #7
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to show absent (2000)

    Are you putting the word ABSENT in place of a grade? If you are then try this adjustment to Kieran's formula. It will look for the work ABSENT first.

    =IF(OR(L7="Absent",N7="Absent",P7="Absent",R7="Abs ent",T7="Absent",),"Absent",IF(OR(ISBLANK(L8),ISBL ANK(N8),ISBLANK(P8),ISBLANK(R8),ISBLANK(T8)),"Abse nt",IF(MIN(L8, N8, P8, R8, T8)<40,"FAIL","PASS")))

    yoyo

  8. #8
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to show absent (2000)

    Nope..my table is filling marks and frades..when ever i put marks, it will turn to grade

    when i put "-" in the marks , the grade will show"absent"

  9. #9
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to show absent (2000)

    Thanks legare..

    range (L7,N7,P7,R7,T7) is the range i put marks in..beside of all the cells , is grade

    so when ever i put marks 40 and above , the grades will indicates C , B and A..in the results that i suppose to use the formula will show PASS
    *reminder student have to get at least get 5C in order to PASS

    so when ever i put marks 39 and below , the grades will indicates D and E..in the results that i suppose to use the formula will show FAI:
    *reminder.. if student get only 1D also , they will be count as FAIL

    so when ever i put " - " , the grades will shows ABSENT in the grades..in the results that i suppose to use the formula will show ABSENT
    *reminder.. if student get only ABSENT also , they will be count as ABSENt

  10. #10
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to show absent (2000)

    Yup. absent is a sort of grade..when there are not marks enter , i will put "-" in the marks and grade will show "ABSENT"

    thanks for your formula but it doesnt works..although i put all "-" in my marks and the grade show "absent " along the range , it still shows PASS in the result

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula to show absent (2000)

    Could you post a demo file showing some of the different contingencies and where the formula fails?

    Steve

  12. #12
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to show absent (2000)

    Of course i will...this is the sample file..thanks

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to show absent (2000)

    Very long formula split into several lines by HansV because it caused horizontal scrolling. It should be entered as a single formula, though.

    The workbook you uploaded did not look like it had any "Absent" marks in it, so I am still guessing at what it would look like in that case. However, the formula below might do what you want. If not, upload another workbook that shows the failing condition.

    <pre>=IF(OR(ISBLANK(L7),ISBLANK(N7),ISBLANK(P7),IS BLANK(R7),ISBLANK(T7)),"",
    IF(OR(L7="-",N7="-",P7="-",R7="-",T7="-"),"Absent",
    IF(OR(L7<40,N7<40,P7<40,R7<40,T7<40),"Fail","PASS" )))
    </pre>

    Legare Coleman

  14. #14
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to show absent (2000)

    Wow...i'm impressed..the formula works great..legare..thanks a zillion

Posting Permissions

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