| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help w/ calculating weekly Avg
Hello,
I am not sure what formula to use to calculate the weekly average, which is the range of the week data (Monday to Sunday). Example: date range from 2/4 to 2/10/08 week is the sum of data from 2/4 to 2/10 divided by 5 (weekday). Below is a sample of data and results I want to achieve. Data: Type1 Type2 WeekAvg Date ProdA ProdB ProdA ProdB ProdA ProdB 2/4/08 30 10 20 10 61 37 2/5/08 10 15 10 15 61 37 2/6/08 50 15 25 10 61 37 2/7/08 20 10 20 20 61 37 2/8/08 10 10 30 20 61 37 2/9/08 10 15 40 15 2/10/08 20 10 10 10 Result column: WeekAvg for ProdA = 61 (30+10+50+20+10+10+20+20+10+25+20+30+40+10) / 5 Result column: WeekAvg for ProdB = 37 (10+15+15+10+10+15+10+10+15+10+20+20+15+10) / 5 NOTE: 2/9 and 2/10 are weekend so does not need to show value. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help w/ calculating weekly Avg
Something like this should do the trick...
=IF(AND(WEEKDAY($A3)1, WEEKDAY($A3)<7), (SUMPRODUCT(--($A$3:$A$9=$A$11), --($A$3:$A$9<=$A$12), B$3:B$9)+SUMPRODUCT(--($A$3:$A$9=$A$11), --($A$3:$A$9<=$A$12), D$3:D$9))/5, "") My source data looked like this A B C D E F G 2 Date ProdA ProdB ProdA ProdB 3 4-Feb-08 30 10 20 10 61 37 4 5-Feb-08 10 15 10 15 61 37 5 6-Feb-08 50 15 25 10 61 37 6 7-Feb-08 20 10 20 20 61 37 7 8-Feb-08 10 10 30 20 61 37 8 9-Feb-08 10 15 40 15 9 10-Feb-08 20 10 10 10 10 11 4-Feb-08 12 10-Feb-08 -- HTH... Jim Thomlinson "Cam" wrote: Hello, I am not sure what formula to use to calculate the weekly average, which is the range of the week data (Monday to Sunday). Example: date range from 2/4 to 2/10/08 week is the sum of data from 2/4 to 2/10 divided by 5 (weekday). Below is a sample of data and results I want to achieve. Data: Type1 Type2 WeekAvg Date ProdA ProdB ProdA ProdB ProdA ProdB 2/4/08 30 10 20 10 61 37 2/5/08 10 15 10 15 61 37 2/6/08 50 15 25 10 61 37 2/7/08 20 10 20 20 61 37 2/8/08 10 10 30 20 61 37 2/9/08 10 15 40 15 2/10/08 20 10 10 10 Result column: WeekAvg for ProdA = 61 (30+10+50+20+10+10+20+20+10+25+20+30+40+10) / 5 Result column: WeekAvg for ProdB = 37 (10+15+15+10+10+15+10+10+15+10+20+20+15+10) / 5 NOTE: 2/9 and 2/10 are weekend so does not need to show value. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help w/ calculating weekly Avg
Jim,
Can you repost? when I click on your response. The screen is blank. "Cam" wrote: Hello, I am not sure what formula to use to calculate the weekly average, which is the range of the week data (Monday to Sunday). Example: date range from 2/4 to 2/10/08 week is the sum of data from 2/4 to 2/10 divided by 5 (weekday). Below is a sample of data and results I want to achieve. Data: Type1 Type2 WeekAvg Date ProdA ProdB ProdA ProdB ProdA ProdB 2/4/08 30 10 20 10 61 37 2/5/08 10 15 10 15 61 37 2/6/08 50 15 25 10 61 37 2/7/08 20 10 20 20 61 37 2/8/08 10 10 30 20 61 37 2/9/08 10 15 40 15 2/10/08 20 10 10 10 Result column: WeekAvg for ProdA = 61 (30+10+50+20+10+10+20+20+10+25+20+30+40+10) / 5 Result column: WeekAvg for ProdB = 37 (10+15+15+10+10+15+10+10+15+10+20+20+15+10) / 5 NOTE: 2/9 and 2/10 are weekend so does not need to show value. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help w/ calculating weekly Avg
The news group seems to be having some difficulties. Try going directly to
the address... http://www.microsoft.com/communities...&lang=en&cr=US -- HTH... Jim Thomlinson "Cam" wrote: Jim, Can you repost? when I click on your response. The screen is blank. "Cam" wrote: Hello, I am not sure what formula to use to calculate the weekly average, which is the range of the week data (Monday to Sunday). Example: date range from 2/4 to 2/10/08 week is the sum of data from 2/4 to 2/10 divided by 5 (weekday). Below is a sample of data and results I want to achieve. Data: Type1 Type2 WeekAvg Date ProdA ProdB ProdA ProdB ProdA ProdB 2/4/08 30 10 20 10 61 37 2/5/08 10 15 10 15 61 37 2/6/08 50 15 25 10 61 37 2/7/08 20 10 20 20 61 37 2/8/08 10 10 30 20 61 37 2/9/08 10 15 40 15 2/10/08 20 10 10 10 Result column: WeekAvg for ProdA = 61 (30+10+50+20+10+10+20+20+10+25+20+30+40+10) / 5 Result column: WeekAvg for ProdB = 37 (10+15+15+10+10+15+10+10+15+10+20+20+15+10) / 5 NOTE: 2/9 and 2/10 are weekend so does not need to show value. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help w/ calculating weekly Avg
Jim,
I tried to duplicated the same data as your sample and it is giving me #VALUE!. Also, Wondering cell A11 and A12. I cannot put those reference cell there, cause there are hundred of other data to row hundreds that I am trying to calculate the same thing. What I did was I added another column after E to display the ending week date so they are showing date 2/10/08 for row 3 to 9. Can't you reference that instead? Also, for learning purpose, what is the -- symbol represent? Thanks "Jim Thomlinson" wrote: Something like this should do the trick... =IF(AND(WEEKDAY($A3)1, WEEKDAY($A3)<7), (SUMPRODUCT(--($A$3:$A$9=$A$11), --($A$3:$A$9<=$A$12), B$3:B$9)+SUMPRODUCT(--($A$3:$A$9=$A$11), --($A$3:$A$9<=$A$12), D$3:D$9))/5, "") My source data looked like this A B C D E F G 2 Date ProdA ProdB ProdA ProdB 3 4-Feb-08 30 10 20 10 61 37 4 5-Feb-08 10 15 10 15 61 37 5 6-Feb-08 50 15 25 10 61 37 6 7-Feb-08 20 10 20 20 61 37 7 8-Feb-08 10 10 30 20 61 37 8 9-Feb-08 10 15 40 15 9 10-Feb-08 20 10 10 10 10 11 4-Feb-08 12 10-Feb-08 -- HTH... Jim Thomlinson "Cam" wrote: Hello, I am not sure what formula to use to calculate the weekly average, which is the range of the week data (Monday to Sunday). Example: date range from 2/4 to 2/10/08 week is the sum of data from 2/4 to 2/10 divided by 5 (weekday). Below is a sample of data and results I want to achieve. Data: Type1 Type2 WeekAvg Date ProdA ProdB ProdA ProdB ProdA ProdB 2/4/08 30 10 20 10 61 37 2/5/08 10 15 10 15 61 37 2/6/08 50 15 25 10 61 37 2/7/08 20 10 20 20 61 37 2/8/08 10 10 30 20 61 37 2/9/08 10 15 40 15 2/10/08 20 10 10 10 Result column: WeekAvg for ProdA = 61 (30+10+50+20+10+10+20+20+10+25+20+30+40+10) / 5 Result column: WeekAvg for ProdB = 37 (10+15+15+10+10+15+10+10+15+10+20+20+15+10) / 5 NOTE: 2/9 and 2/10 are weekend so does not need to show value. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help w/ calculating weekly Avg
Jim,
Jim, I got it to work with your formula, but was wondering cell A11 and A12. I cannot put those reference cell there, cause there are hundred of other data to row hundreds that I am trying to calculate the same thing. What I did was I added another column after E to display the ending week date so they are showing date 2/10/08 for row 3 to 9. Can't you reference that instead? Also, for learning purpose, what is the -- symbol represent? Thanks "Jim Thomlinson" wrote: The news group seems to be having some difficulties. Try going directly to the address... http://www.microsoft.com/communities...&lang=en&cr=US -- HTH... Jim Thomlinson "Cam" wrote: Jim, Can you repost? when I click on your response. The screen is blank. "Cam" wrote: Hello, I am not sure what formula to use to calculate the weekly average, which is the range of the week data (Monday to Sunday). Example: date range from 2/4 to 2/10/08 week is the sum of data from 2/4 to 2/10 divided by 5 (weekday). Below is a sample of data and results I want to achieve. Data: Type1 Type2 WeekAvg Date ProdA ProdB ProdA ProdB ProdA ProdB 2/4/08 30 10 20 10 61 37 2/5/08 10 15 10 15 61 37 2/6/08 50 15 25 10 61 37 2/7/08 20 10 20 20 61 37 2/8/08 10 10 30 20 61 37 2/9/08 10 15 40 15 2/10/08 20 10 10 10 Result column: WeekAvg for ProdA = 61 (30+10+50+20+10+10+20+20+10+25+20+30+40+10) / 5 Result column: WeekAvg for ProdB = 37 (10+15+15+10+10+15+10+10+15+10+20+20+15+10) / 5 NOTE: 2/9 and 2/10 are weekend so does not need to show value. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help w/ calculating weekly Avg
Check out this link...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html Basically it is coercing true / false into 1 /0 -- HTH... Jim Thomlinson "Cam" wrote: Jim, Jim, I got it to work with your formula, but was wondering cell A11 and A12. I cannot put those reference cell there, cause there are hundred of other data to row hundreds that I am trying to calculate the same thing. What I did was I added another column after E to display the ending week date so they are showing date 2/10/08 for row 3 to 9. Can't you reference that instead? Also, for learning purpose, what is the -- symbol represent? Thanks "Jim Thomlinson" wrote: The news group seems to be having some difficulties. Try going directly to the address... http://www.microsoft.com/communities...&lang=en&cr=US -- HTH... Jim Thomlinson "Cam" wrote: Jim, Can you repost? when I click on your response. The screen is blank. "Cam" wrote: Hello, I am not sure what formula to use to calculate the weekly average, which is the range of the week data (Monday to Sunday). Example: date range from 2/4 to 2/10/08 week is the sum of data from 2/4 to 2/10 divided by 5 (weekday). Below is a sample of data and results I want to achieve. Data: Type1 Type2 WeekAvg Date ProdA ProdB ProdA ProdB ProdA ProdB 2/4/08 30 10 20 10 61 37 2/5/08 10 15 10 15 61 37 2/6/08 50 15 25 10 61 37 2/7/08 20 10 20 20 61 37 2/8/08 10 10 30 20 61 37 2/9/08 10 15 40 15 2/10/08 20 10 10 10 Result column: WeekAvg for ProdA = 61 (30+10+50+20+10+10+20+20+10+25+20+30+40+10) / 5 Result column: WeekAvg for ProdB = 37 (10+15+15+10+10+15+10+10+15+10+20+20+15+10) / 5 NOTE: 2/9 and 2/10 are weekend so does not need to show value. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| calculating b-weekly pay | Excel Discussion (Misc queries) | |||
| Calculating daily hours (as text) in the weekly total formula | Excel Worksheet Functions | |||
| with weekly score sheet how do I column a weekly progressive aver. | Excel Worksheet Functions | |||
| Problem calculating weekly costs | Excel Worksheet Functions | |||
| Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
Linear Mode
