countif function?

[denise]

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

 

[Kevin Vaughn]

It appears that you want one formula in K5 to populate cells in B5, D5, F5,
etc. That won't work (not without VBA,) but you could use individual
formulas in each of those cells along the lines of this (formatted as
Percent.)

In B5:
=(A5>0)*1

 

[Kevin Vaughn]

Hope this doesn't double post.

Perhaps I misunderstood you. Did you mean this maybe?

=AVERAGE(B8,D8,F8,H8,J8)

 

[Bob Phillips]

Denise, this calculates the individual rows

=AVERAGE(IF((MOD(COLUMN(B2:H2),2)=0),IF(B2:H2>0,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:I4>0,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]

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