countif function?
Denise, this calculates the individual rows
=AVERAGE(IF((MOD(COLUMN(B2:H2),2)=0),IF(B2:H20,1, 0),FALSE))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
For K5, I would use
=AVERAGE(IF((MOD(COLUMN(B2:I45),2)=0),IF(B2:I40,1 ,0),FALSE))
again an array formula, but I get 58%, not 33%
--
HTH
Bob Phillips
(remove nothere from the email address if mailing direct)
"denise" wrote in message
...
Hello,
I have a tracking spreadsheet where I am getting myself confused trying to
develop a formula that will calculate the month to date total
"participation
rate" of a group, where each person has a goal to sell 1 item per week. If
they make 1 sell per week, then they are counted as participating for the
week. It doesn't matter how many they sell during the week but it must be
at
least 1 each and every week during the month.
Here's an example with the correct percentages. Does anyone have a
suggestion on what formula/function I can use in the K5 cell?
(Col A B C D E F G H
I J K)
Person Wk1 Part% Wk2 Part% Wk3 Part% Wk4 Part% MTD Part%
A 1 100% 1 100% 1 100% 1 100%
4 100%
B 1 100% 0 0% 2 100% 0 0%
3 50%
C 4 100% 0 0% 0 0% 0
0%
4 25%
GrpTot 6 100% 1 33% 3 66% 1 33%
11 33%
Thanks!
Denise
|