# Thread: Formula to show absent (2000)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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?

7. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Formula to show absent (2000)

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

13. ## 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>

14. ## 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
•