Sorry, I didn't explain the situation very well. I don't want an average and
I don't want the MTD participation rate (K5) to populate the weekly rates
(B5, etc). In fact, I see now that my example layout is not correct. Let me
try to explain better.

Each week stands independent of every other week. If a person sells at least
1 item during a week, they have participated, thus their participation rate
is 100% (whether they sell 1 or 100 items). Here is what I'm using to figure
a person's participation rate for the week (don't know if this is the most
efficient way but it works): =IF(COUNTIF(M10,"=1"),100%,0%) where M is the
Weekly Total column and 10 is the person's row.

The weekly group total is dependent on how many people in the group
participated (meaning they sold at least 1 item giving them a participation
rate of 100%). This is a straight percentage, i.e., 3 out 4 people sold 1
item during the week, the group total participation rate is 75%. I don't have
any problem with figuring the participation rate for the group total by week.

I run into trouble when I move to the MTD totals. I don't think I mentioned
this in my first post, but the month is all or nothing. Each person has
either sold 1 item per week or they haven't. The example I set up is hard to
read because it wrapped, but basically, it's saying that person A made their
participation rate each week for 4 weeks and thus has made their
participation for the month (100%). Person B made it the 1st and 3rd weeks
and I originally showed their participation as 50% for the month when it
actually should have been 0% because they didn't make their sale each week
(sorry for the mistake). Same for person C, they didn't make it for the
month. 1 out of 3 made it for each week the entire month and that's where I
come up with a group total participation rate of 33%.

My question is what formula can I use in the monthly participation rate cell
to look at each person each week and figure how many of the total group made
at least 1 sale each and every week during the month?

Thanks for all your help with this!
Denise


"Bob Phillips" wrote:

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