attendace %

[vividpresence]

I have a work sheet with Two colums
1-member ID
2- attendance

in the attendance column there are two discribtions
1-checkedin
2-canceled

there are many members in the member id column

i need to get a list of all members that have less than 70% checked i
rate how can i do that

Thank

 

[Frank Kabel]

Hi
not sure how you calculate the percentage?. Is this depending on the
time they attend?. And if yes what are 100%?

 

[vividpresence]

the time range for the appointments is two months
each member is listed multiple times in the member id column, each tim
the status could be chekedin or cancele

 

[Frank Kabel]

Hi
still not sure how you calculate the percentage. Could you give some
examples (plain text - ´no attachment please)

 

[vividpresence]

Date Member ID Attendance

1/1 44444 checked in
1/1 55555 checked in
1/1 66666 canceled

1/3 44444 checked in
1/3 55555 canceled
1/3 66666 canceled

1/4 44444 checked in
1/4 55555 cecked in
1/4 66666 canceled

1/6 44444 Cheked in
1/6 55555 canceled
1/6 66666 checked in

44444 would be 100%
55555 would be 50%
66666 would be 25

 

[Frank Kabel]

Hi
now I see. Try the following formula (in this case for ID '44444')
=SUMPRODUCT(--(A1:A100="44444"),--(B1:B100="checked
in"))/COUNTIF(B1:B100,"44444")
and format this cell as percentage

 

[vividpresence]

Thanks i will try it

but is there a way to automatically do this for all member IDs witou
specifying them one by one?

 

[vividpresence]

any idea why i am getting (#DIV/0! )error when i used the formula

 

[Frank Kabel]

Hi
if your IDs are stored as values/numbers try
=SUMPRODUCT(--(A1:A100=44444),--(B1:B100="checked
in"))/COUNTIF(B1:B100,44444)

 

[Thomas Ramel]

Grüezi vividpresence

vividpresence schrieb am 24.05.2004

Date Member ID Attendance

1/1 44444 checked in
1/1 55555 checked in
1/1 66666 canceled
1/3 44444 checked in
1/3 55555 canceled
1/3 66666 canceled
1/4 44444 checked in
1/4 55555 cecked in
1/4 66666 canceled
1/6 44444 Cheked in
1/6 55555 canceled
1/6 66666 checked in

44444 would be 100%
55555 would be 50%
66666 would be 25%

You could use a pivot-table.

Set the cellmarker in your data-range --> Menu: 'Data' --> Pivot-Table..
--> [Continue>] --> Range: (should be marked now, otherwise dot it by
yourself) --> [Continue>] --> [Layout..] --> Drag the field 'Member ID'
from the right side of the window to the 'Rows'-area and the field
'Arrendance' once to the 'Columns'-area and once to the 'Data'-area.

Now doubleclick the field 'Attendance' in the 'Datas'-area --> [Options...]
--> Show as: '% of Row' --> [OK] -->[OK] --> [Finish]

All your Member-IDs now are listed with their %-amont of each
attendance-code

--
Regards

Thomas Ramel
- MVP for Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]