Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif function?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif function?
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: =(A50)*1 -- Kevin Vaughn "denise" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif function?
Hope this doesn't double post.
Perhaps I misunderstood you. Did you mean this maybe? =AVERAGE(B8,D8,F8,H8,J8) -- Kevin Vaughn "Kevin Vaughn" wrote: 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: =(A50)*1 -- Kevin Vaughn "denise" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif function?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can you use countif function for noncontiguous cells | Excel Worksheet Functions | |||
CountIf function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
COUNTIF and then SUM in Same Function | Excel Worksheet Functions | |||
hOW TO USE THE AND OPERATOR WITH THE COUNTIF FUNCTION | Excel Worksheet Functions |