Formula to show absent (2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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 ?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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”

Reply | Quote

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”

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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=”Absent”,T7=”Absent”,),”Absent”,IF(OR(ISBLANK(L8),ISBLANK(N8),ISBLANK(P8),ISBLANK(R8),ISBLANK(T8)),”Absent”,IF(MIN(L8, N8, P8, R8, T8)<40,"FAIL","PASS")))

yoyo

Reply | Quote

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

Reply | Quote

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

Steve

Reply | Quote

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

Reply | Quote

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.

=IF(OR(ISBLANK(L7),ISBLANK(N7),ISBLANK(P7),ISBLANK(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")))

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

=IF(OR(ISBLANK(L7),ISBLANK(N7),ISBLANK(P7),ISBLANK(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")))

Reply | Quote

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

Reply | Quote

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

Steve

Reply | Quote

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

Reply | Quote

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=”Absent”,T7=”Absent”,),”Absent”,IF(OR(ISBLANK(L8),ISBLANK(N8),ISBLANK(P8),ISBLANK(R8),ISBLANK(T8)),”Absent”,IF(MIN(L8, N8, P8, R8, T8)<40,"FAIL","PASS")))

yoyo

Reply | Quote

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

Reply | Quote

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 ?

Reply | Quote