| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Monthly Consolidation by Category
Hi all,
I have a MTD consolidation (average) by category problem that I desparately need your help. Here is what my data source may look like: DATE NAME SCORE 1/1/2006 John 8.70 1/11/2006 Donna 7.80 1/21/2006 Lucy 9.10 1/25/2006 Donna 8.60 1/31/2006 John 9.90 2/3/2006 John 8.80 2/10/2006 Lucy 9.50 2/20/2006 Donna 9.80 3/2/2006 Jane 9.00 3/7/2006 John 9.50 3/12/2006 Donna 9.20 3/22/2006 Lucy 8.90 3/30/2006 Donna 9.00 Here is what my report would look like: MONTH NAME MTD AVE YTD AVE JAN Donna 8.20 8.20 JAN John 9.30 9.30 JAN Lucy 9.10 9.10 FEB Donna 9.80 9.30 FEB John 8.80 9.13 FEB Lucy 9.50 8.73 MAR Donna 9.10 9.17 MAR Jane 9.00 9.23 MAR John 9.50 8.88 MAR Lucy 8.90 9.00 Since the date range varies for each month, how would you define your monthly date range in order to make the consolidation by category and by month easier and dynamic? Thanks so much! Sally |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with Monthly Consolidation by Category
Sally,
Why not using a pivot table ...??? Cheers Carim Sally wrote: Hi all, I have a MTD consolidation (average) by category problem that I desparately need your help. Here is what my data source may look like: DATE NAME SCORE 1/1/2006 John 8.70 1/11/2006 Donna 7.80 1/21/2006 Lucy 9.10 1/25/2006 Donna 8.60 1/31/2006 John 9.90 2/3/2006 John 8.80 2/10/2006 Lucy 9.50 2/20/2006 Donna 9.80 3/2/2006 Jane 9.00 3/7/2006 John 9.50 3/12/2006 Donna 9.20 3/22/2006 Lucy 8.90 3/30/2006 Donna 9.00 Here is what my report would look like: MONTH NAME MTD AVE YTD AVE JAN Donna 8.20 8.20 JAN John 9.30 9.30 JAN Lucy 9.10 9.10 FEB Donna 9.80 9.30 FEB John 8.80 9.13 FEB Lucy 9.50 8.73 MAR Donna 9.10 9.17 MAR Jane 9.00 9.23 MAR John 9.50 8.88 MAR Lucy 8.90 9.00 Since the date range varies for each month, how would you define your monthly date range in order to make the consolidation by category and by month easier and dynamic? Thanks so much! Sally |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with Monthly Consolidation by Category
For January MTD Ave:
=SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14="John"),$C$2:$C$14)/SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14="John")) Or =SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14=F3),$C$2:$C$14)/SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14=F3)) HTH "Carim" wrote: Sally, Why not using a pivot table ...??? Cheers Carim Sally wrote: Hi all, I have a MTD consolidation (average) by category problem that I desparately need your help. Here is what my data source may look like: DATE NAME SCORE 1/1/2006 John 8.70 1/11/2006 Donna 7.80 1/21/2006 Lucy 9.10 1/25/2006 Donna 8.60 1/31/2006 John 9.90 2/3/2006 John 8.80 2/10/2006 Lucy 9.50 2/20/2006 Donna 9.80 3/2/2006 Jane 9.00 3/7/2006 John 9.50 3/12/2006 Donna 9.20 3/22/2006 Lucy 8.90 3/30/2006 Donna 9.00 Here is what my report would look like: MONTH NAME MTD AVE YTD AVE JAN Donna 8.20 8.20 JAN John 9.30 9.30 JAN Lucy 9.10 9.10 FEB Donna 9.80 9.30 FEB John 8.80 9.13 FEB Lucy 9.50 8.73 MAR Donna 9.10 9.17 MAR Jane 9.00 9.23 MAR John 9.50 8.88 MAR Lucy 8.90 9.00 Since the date range varies for each month, how would you define your monthly date range in order to make the consolidation by category and by month easier and dynamic? Thanks so much! Sally |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with Monthly Consolidation by Category
..... should have been ...
=SUMPRODUCT(--(TEXT($A$2:$A$14,"mmm")="Jan"),--($B$2:$B$14="John"),$C$2:$C$14)/SUMPRODUCT(--(TEXT($A$2:$A$14,"mmm")="Jan"),--($B$2:$B$14="John")) "Toppers" wrote: For January MTD Ave: =SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14="John"),$C$2:$C$14)/SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14="John")) Or =SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14=F3),$C$2:$C$14)/SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14=F3)) HTH "Carim" wrote: Sally, Why not using a pivot table ...??? Cheers Carim Sally wrote: Hi all, I have a MTD consolidation (average) by category problem that I desparately need your help. Here is what my data source may look like: DATE NAME SCORE 1/1/2006 John 8.70 1/11/2006 Donna 7.80 1/21/2006 Lucy 9.10 1/25/2006 Donna 8.60 1/31/2006 John 9.90 2/3/2006 John 8.80 2/10/2006 Lucy 9.50 2/20/2006 Donna 9.80 3/2/2006 Jane 9.00 3/7/2006 John 9.50 3/12/2006 Donna 9.20 3/22/2006 Lucy 8.90 3/30/2006 Donna 9.00 Here is what my report would look like: MONTH NAME MTD AVE YTD AVE JAN Donna 8.20 8.20 JAN John 9.30 9.30 JAN Lucy 9.10 9.10 FEB Donna 9.80 9.30 FEB John 8.80 9.13 FEB Lucy 9.50 8.73 MAR Donna 9.10 9.17 MAR Jane 9.00 9.23 MAR John 9.50 8.88 MAR Lucy 8.90 9.00 Since the date range varies for each month, how would you define your monthly date range in order to make the consolidation by category and by month easier and dynamic? Thanks so much! Sally |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with Monthly Consolidation by Category
Thank you Carim and Toppers. The pivot table solution is the most
straightforward, thanks! But I am also interested in Topper's array solution. How would you apply that to varying ranges? My date ranges change everyday -- everyday new scores populate so at the end of the day I don't know what ranges those dates will fall in. You guys are great, thanks again! Sally "Toppers" wrote: .... should have been ... =SUMPRODUCT(--(TEXT($A$2:$A$14,"mmm")="Jan"),--($B$2:$B$14="John"),$C$2:$C$14)/SUMPRODUCT(--(TEXT($A$2:$A$14,"mmm")="Jan"),--($B$2:$B$14="John")) "Toppers" wrote: For January MTD Ave: =SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14="John"),$C$2:$C$14)/SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14="John")) Or =SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14=F3),$C$2:$C$14)/SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14=F3)) HTH "Carim" wrote: Sally, Why not using a pivot table ...??? Cheers Carim Sally wrote: Hi all, I have a MTD consolidation (average) by category problem that I desparately need your help. Here is what my data source may look like: DATE NAME SCORE 1/1/2006 John 8.70 1/11/2006 Donna 7.80 1/21/2006 Lucy 9.10 1/25/2006 Donna 8.60 1/31/2006 John 9.90 2/3/2006 John 8.80 2/10/2006 Lucy 9.50 2/20/2006 Donna 9.80 3/2/2006 Jane 9.00 3/7/2006 John 9.50 3/12/2006 Donna 9.20 3/22/2006 Lucy 8.90 3/30/2006 Donna 9.00 Here is what my report would look like: MONTH NAME MTD AVE YTD AVE JAN Donna 8.20 8.20 JAN John 9.30 9.30 JAN Lucy 9.10 9.10 FEB Donna 9.80 9.30 FEB John 8.80 9.13 FEB Lucy 9.50 8.73 MAR Donna 9.10 9.17 MAR Jane 9.00 9.23 MAR John 9.50 8.88 MAR Lucy 8.90 9.00 Since the date range varies for each month, how would you define your monthly date range in order to make the consolidation by category and by month easier and dynamic? Thanks so much! Sally |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with Monthly Consolidation by Category
I'm not sure what you mean by varying ranges: just ensure the highest range
index is greater than your expected maximum. Example below assumes no more than 1000 rows. =SUMPRODUCT(--(TEXT($A$2:$A$1000,"mmm")="Jan"),--($B$2:$B$1000="John"),$C$2:$C$14)/SUMPRODUCT(--(TEXT($A$2:$A$1000,"mmm")="Jan"),--($B$2:$B$1000="John")) You can replace literals with cells e.g. X1="Jan", X2="John" =SUMPRODUCT(--(TEXT($A$2:$A$1000,"mmm")=X1),--($B$2:$B$1000=X2),$C$2:$C$14)/SUMPRODUCT(--(TEXT($A$2:$A$1000,"mmm")=X1),--($B$2:$B$1000=X2)) "Sally" wrote: Thank you Carim and Toppers. The pivot table solution is the most straightforward, thanks! But I am also interested in Topper's array solution. How would you apply that to varying ranges? My date ranges change everyday -- everyday new scores populate so at the end of the day I don't know what ranges those dates will fall in. You guys are great, thanks again! Sally "Toppers" wrote: .... should have been ... =SUMPRODUCT(--(TEXT($A$2:$A$14,"mmm")="Jan"),--($B$2:$B$14="John"),$C$2:$C$14)/SUMPRODUCT(--(TEXT($A$2:$A$14,"mmm")="Jan"),--($B$2:$B$14="John")) "Toppers" wrote: For January MTD Ave: =SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14="John"),$C$2:$C$14)/SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14="John")) Or =SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14=F3),$C$2:$C$14)/SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14=F3)) HTH "Carim" wrote: Sally, Why not using a pivot table ...??? Cheers Carim Sally wrote: Hi all, I have a MTD consolidation (average) by category problem that I desparately need your help. Here is what my data source may look like: DATE NAME SCORE 1/1/2006 John 8.70 1/11/2006 Donna 7.80 1/21/2006 Lucy 9.10 1/25/2006 Donna 8.60 1/31/2006 John 9.90 2/3/2006 John 8.80 2/10/2006 Lucy 9.50 2/20/2006 Donna 9.80 3/2/2006 Jane 9.00 3/7/2006 John 9.50 3/12/2006 Donna 9.20 3/22/2006 Lucy 8.90 3/30/2006 Donna 9.00 Here is what my report would look like: MONTH NAME MTD AVE YTD AVE JAN Donna 8.20 8.20 JAN John 9.30 9.30 JAN Lucy 9.10 9.10 FEB Donna 9.80 9.30 FEB John 8.80 9.13 FEB Lucy 9.50 8.73 MAR Donna 9.10 9.17 MAR Jane 9.00 9.23 MAR John 9.50 8.88 MAR Lucy 8.90 9.00 Since the date range varies for each month, how would you define your monthly date range in order to make the consolidation by category and by month easier and dynamic? Thanks so much! Sally |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Financial Loan calc including monthly fees | Excel Discussion (Misc queries) | |||
| Only one colum of stack graph shows category label - problem | Charts and Charting in Excel | |||
| Automatic Sum by Category (SumIf extension) | Excel Discussion (Misc queries) | |||
| Linking Several Worksheets to One Worksheet | Excel Discussion (Misc queries) | |||
| Rank() based on category | Excel Worksheet Functions | |||
Linear Mode
