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

--
Message posted from http://www.ExcelForum.com

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

--
Regards
Frank Kabel
Frankfurt, Germany


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
in
rate how can i do that

Thanks


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Regards
Frank Kabel
Frankfurt, Germany


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


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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


--
Regards
Frank Kabel
Frankfurt, Germany


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%


---
Message posted from http://www.ExcelForum.com/

Thanks i will try it

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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


--
Regards
Frank Kabel
Frankfurt, Germany


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


---
Message posted from http://www.ExcelForum.com/

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]