Attendance file with conditions

[Newb10]

Good day everyone,

I don't know if this is possible with purely excel. What I am trying to accomplish is an attendance file with the following conditions :

Sick leave, unplanned leave, and No call no show per instance 1 is 8% or .5 is 4% deducted to 100%, this is what is got so far =IF(SUM(F4:J4)<=2.5,100%-(SUM(F4:J4)*8%),80%)

The passing rate is 80%. If a person goes beyond 80% then SL = 10%, Unplanned = 20% and NCNS = 30% deduction on the remaining 80%.

Date from feb 1 to feb 14
SL = 1 EL = 0 NCNS = 1


This would make it 84%

Date from feb 1 to feb 17
SL = 1 EL = 1 NCNS = 1

Since EL came last - the score should be 4% deducted still to 84% making it 80%. then beyond it would make it Unplanned = 20% * .5 = 10% - 80% = 70%

I hope this make sense.

(By the way, wanted to thank you guys as these past few months, I have gained a lot of knowledge not only with excel but also powerBI)

 

[sijpie]

<quote>Sick leave, unplanned leave, and No call no show per instance 1 is 8% or .5 is 4% deducted to 100% </quote>
I dont understand that sentence, particularly following your calculations where in the formula you subtract 8%, but the you say only 4% gets deducted for the instance of EL. How can you have half an instance? or is it half a day? please clarify