Looking for a formula for two scenarios, please help

C

central1010

New Member
Joined
Jan 6, 2016
Messages
16
Hi everyone,

I am a staff of a small private training establishment, am producing an attendance sheet by using excel.

I have 2 scenarios which I have been looking for formulas to sort them out, here is the story:

In the attendance sheet, we have some markings for being late, half-day, absent... etc. For each data, I set a value for them in order to calculate the student's attendance percentage.

For being Late (L) = 1, Half-day (HD) = 0.5, Absent (A) = 0, and our school has a policy of the attendance.

2 times of L = 1 time of A; 2 times of HD = 1 time of A

For instance, the student was late twice and being on time for the rest of last week, the attendance of last week will show 4 out of 5 days, because we marked 1 of the 2 Ls, so there will be 1L 1A 3P (Present). For the 2HD = 1A works the same.

Sorry if I confused you all, but please help me with this complicated scenarios. I have been struggling with this for a week.

It will be appreciated if you could help me with this.

Thank you so much!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Caribeiro77 said:
Welcome can you post a small sample of your data?
Click to expand...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Attendance[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]4/5 (supposed to be 5/5)[/TD]
[/TR]
[TR]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]3.5/5 (supposed to be 4/5)[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]4/5 (supposed to be 5/5)[/TD]
[/TR]
[TR]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]P[/TD]
[TD]2/5 (supposed to be 3/5)[/TD]
[/TR]
</tbody>[/TABLE]

Above are the scenarios, only every 2 times of either L or HD appears will become 1A.

The attendance will still how the actual legends, but in the attendance will be calculated as A.

Hope I explained it well :(
 
Upvote 0
Hi,

You stated 2 L = 1A, but according to your sample, the Late (L)s are not counted at all?
 
Upvote 0
Hi there,

One of the every two Ls won't be counted, like this:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Attendance[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]L (this one is actually A)[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]4/5 (supposed to be 5/5)[/TD]
[/TR]
[TR]
[TD]HD[/TD]
[TD]HD (this one is actually A)[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]3.5/5 (supposed to be 4/5)[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]L (this one is actually A)[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]4/5 (supposed to be 5/5)[/TD]
[/TR]
[TR]
[TD]HD[/TD]
[TD]HD (this one is actually A)[/TD]
[TD]HD[/TD]
[TD]HD (this one is actually A)[/TD]
[TD]P[/TD]
[TD]2/5 (supposed to be 3/5)[/TD]
[/TR]
</tbody>[/TABLE]


Cheers! :)
 
Upvote 0
Where do you want to put the results, and in your example what are the results?
Can you put the same example but this time put it with desired results where you want them to show...
 
Upvote 0
Hi Caribeiro77,

It will be like this:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Attendance[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]4/5[/TD]
[/TR]
[TR]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]3.5/5[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]4/5[/TD]
[/TR]
[TR]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]P[/TD]
[TD]2/5[/TD]
[/TR]
</tbody>[/TABLE]

Legends:

L = 1, HD = 0.5, A = 0

Remarks:

2L = 1A
2HD = 1A

And, I would like have the desired results shown under the column of Attendance, please.

Thank you!
 
Upvote 0
central1010 said:
Hi Caribeiro77,

It will be like this:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Attendance[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]4/5[/TD]
[/TR]
[TR]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]3.5/5[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]4/5[/TD]
[/TR]
[TR]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]P[/TD]
[TD]2/5[/TD]
[/TR]
</tbody>[/TABLE]

Legends:

L = 1, HD = 0.5, A = 0

Remarks:

2L = 1A
2HD = 1A

And, I would like have the desired results shown under the column of Attendance, please.

Thank you!
Click to expand...
Please explain in detail how you get those results for the 2nd (3.5/5) and 4th (2/5) rows.
 
Upvote 0
Hi Peter_SSs,

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Attendance[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]4/5 (supposed to be 5/5)[/TD]
[/TR]
[TR]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]3.5/5 (supposed to be 4/5)[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]4/5 (supposed to be 5/5)[/TD]
[/TR]
[TR]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]P[/TD]
[TD]2/5 (supposed to be 3/5)[/TD]
[/TR]
</tbody>[/TABLE]

For the 2nd row, I would like to change HD into A, which is from:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]HD (0.5)[/TD]
[TD]HD (0.5)[/TD]
[TD]P (1) [/TD]
[TD]P (1)[/TD]
[TD]P (1)[/TD]
[TD]4/5[/TD]
[/TR]
</tbody>[/TABLE]

Into:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]HD (0.5)[/TD]
[TD]A (0)[/TD]
[TD]P (1)[/TD]
[TD]P (1)[/TD]
[TD]P (1)[/TD]
[TD]3.5/5[/TD]
[/TR]
</tbody>[/TABLE]

But I would like to show the A as HD in the table.

For the 4th row, it is the same, from:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]HD (0.5)[/TD]
[TD]HD (0.5)[/TD]
[TD]HD (0.5)[/TD]
[TD]HD (0.5)[/TD]
[TD]P (1)[/TD]
[TD]3/5[/TD]
[/TR]
</tbody>[/TABLE]

To:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]HD (0.5)[/TD]
[TD]A (0)[/TD]
[TD]HD (0.5)[/TD]
[TD]A (0)[/TD]
[TD]P (1)[/TD]
[TD]2/5[/TD]
[/TR]
</tbody>[/TABLE]

Hope it makes sense. Thank you so much!
 
Upvote 0
See if this does what you want. If not please provide more examples with expected results & explanations.

Excel Workbook
ABCDEF
1MonTueWedThuFriAttendance
2LLPPP4
3HDHDPPP3.5
4LLLPP4
5HDHDHDHDP2
Attendance
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Formula or VBA to track pro-rated attendance hours
Replies
8
Views
323
bh24524
B
M
  • Question Question
Formula to calculate team attendance %
Replies
7
Views
816
Fluff
Fluff
L
  • Question Question
Count if consecutive value is repeated 3 times.
Replies
2
Views
1K
Fluff
Fluff
W
  • Question Question
Employees per hour calculations
Replies
2
Views
352
Wgoldbaum
W
F
  • Question Question
Formula Help: Summing numbers from text cells, that occur before/after specific characters
Replies
3
Views
769
ExceLoki
ExceLoki

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top