Results 1 to 14 of 14
Thread: Formula to show absent (2000)

20040521, 02:59 #1
 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

20040521, 03:57 #2
 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.

20040521, 10:18 #3
 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 ?

20040521, 10:27 #4
 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

20040521, 10:54 #5
 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

20040521, 13:58 #6
 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

20040521, 21:44 #7
 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

20040522, 10:03 #8
 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"

20040522, 10:09 #9
 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

20040522, 10:13 #10
 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

20040522, 12:37 #11
 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

20040522, 13:14 #12
 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

20040522, 15:16 #13
 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

20040522, 16:29 #14
 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