How to sum entries with same date and get average on month?

[kensor81]

Hi,

I'm sitting having a problem I can't get my head around for the time being.
Hopefully someone can push me in the right direction or help with some brainstorming.

I have one table with source data in called “Table 1”:

Date:      Project: Number of Docs.:
03.01.2014 5243      79
03.01.2014 5333      81
03.01.2014 5210     115
03.01.2014 5524      21
03.01.2014 5601      98
10.01.2014 5243      81
10.01.2014 5333      89
10.01.2014 5210     121
10.01.2014 5524      21
10.01.2014 5601      98
10.01.2014 5605      10

I need to sum the number of docs for each date like this:

Date: Sum  Number of Docs.:
03.01.2014 394
10.01.2014 420

And then calculate the average with in the months, for this example it will only be January like this: ((394 + 420)/2 = 407)

Month:  Sum  Number of Docs.:
January 407

The source table will be filled in each week on same day of the week, but I need to report the average by month.

The reporting was supposed to be by chart with 12 month perspective.

I had hoped to do this by the help of Pivot Table and Pivot Chart, and not to manually update this each month.

But I'm not sure how to go from the Source Table to the Pivot Table to the Pivot Chart.

If I use sum on the column called “Number of Docs.:” it will show correctly the sum for each date, but if then group further on months it will be wrong as I then need to have the average within that month.

I hope someone is able to give me some good pointers

Thx,

Kenneth

 

[crouchsw]

You can use VBA to automatically refresh the data on calculate (you might change this to workbook_open() if it gets laggy:

Private sub workbook_open()
Sheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

To the data, add a 'month' column (eg D2 formula =Month(A2) - you'll have to have the date in an Excel date format), have it show as an average in the pivot table (you can hide the column if you want).

See the example spreadsheet

 

[SirJB7]

Hi, kensor81!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, next time consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.

If you're looking for a formula only solution, then check the uploaded file. It has 3 tables (Tabla1, columns A:D), Tabla2 (F:H) and Tabla3 (J:K)).

Formulas:
D2: =SI(CONTAR.SI(A$2:A2;[@[Date:]])=1;MAX(D$1:D1)+1;0) -----> in english: =IF(COUNTIF(A$2:A2,[@[Date:]])=1,MAX(D$1:D1)+1,0)

F2: =SI.ERROR(INDICE(Tabla1[Date:];COINCIDIR(FILA()-1;Tabla1[Counter:];0));"") -----> in english: =IFERROR(INDEX(Tabla1[Date:],MATCH(ROW()-1,Tabla1[Counter:],0)),"")

G2: =SI([@[Date:]]="";"";SUMAR.SI(Tabla1[Date:];[@[Date:]];Tabla1[Number of Docs:])) -----> in english: =IF([@[Date:]]="","",SUMIF(Tabla1[Date:],[@[Date:]],Tabla1[Number of Docs:]))

H2: =SI([@[Date:]]="";"";SI(SUMAPRODUCTO((F$1:F1>[@[Date:]]-DIA([@[Date:]]))*(F$1:F1<=FIN.MES([@[Date:]]-DIA([@[Date:]])+1;0)))=0;MAX(H$1:H1)+1;0)) -----> in english: =IF([@[Date:]]="","",IF(SUMPRODUCT((F$1:F1>[@[Date:]]-DAY([@[Date:]]))*(F$1:F1<=EOMONTH([@[Date:]]-DAY([@[Date:]])+1,0)))=0,MAX(H$1:H1)+1,0))

J2: =SI.ERROR(INDICE(Tabla2[Date:];COINCIDIR(FILA()-1;Tabla2[Counter:];0))-DIA(INDICE(Tabla2[Date:];COINCIDIR(FILA()-1;Tabla2[Counter:];0)))+1;"") -----> in english: =IFERROR(INDEX(Tabla2[Date:],MATCH(ROW()-1,Tabla2[Counter:],0))-DAY(INDEX(Tabla2[Date:],MATCH(ROW()-1,Tabla2[Counter:],0)))+1,"")

K2: =SI([@[Month:]]="";"";PROMEDIO.SI.CONJUNTO(Tabla2[Sum:];Tabla2[Date:];">="&[@[Month:]];Tabla2[Date:];"<"&FECHA.MES([@[Month:]];1))) -----> in english: =IF([@[Month:]]="","",AVERAGEIFS(Tabla2[Sum:],Tabla2[Date:],">="&[@[Month:]],Tabla2[Date:],"<"&EDATE([@[Month:]],1)))

Just advise if any issue.

Regards!

PS: BTW, keep in mind that the average that you defined is the daily average, not the average for each entry of the original table.

 

[Somendra Misra]

Hi Kensor81!

As @SirJB7 already answered your query( which I think will be 100% meet your expected result) I thought of coming the same solution with a different set of formula's.

Please have a look of attache file. It has One source data table Table 1 (A1:C12). Table 2 for doing the sum of daily docs (E3:F15). Table 3 for taking monthly average (H3:I15).

In cell F1 I had count Nos. of unique days :--> =SUM(IF(FREQUENCY(Table1[Date],Table1[Date]),1,0)).
In cell I1 I had count nos. of unique months :--> =SUM(IF(FREQUENCY(MONTH(Table1[Date]),MONTH(Table1[Date])),1,0))

In E4:--> =IF(ROWS($E$4:E4)<=$F$1,INDEX((Table1[Date]),SMALL(IF(FREQUENCY(Table1[Date],Table1[Date]),ROW(Table1[Date])-ROW($A$2)+1),ROWS($E$4:E4))),"")
Copy this down to E15. Enter with Ctrl+SHift+Enter

In F4:--> =IFERROR(SUMPRODUCT(Table1[No. of Docs]*(--(Table1[Date]=$E4))),"")
Copy this down to F15.

In H4:--> =IF(ROWS($H$4:H4)<=$I$1,INDEX((Table1[Date]),SMALL(IF(FREQUENCY(MONTH(Table1[Date]),MONTH(Table1[Date])),ROW(Table1[Date])-ROW($A$2)+1),ROWS($H$4:H4))),"")
Copy this down to H15. Enter with Ctrl+Shift+Enter.

In I4:--> =IFERROR(SUM(IFERROR(IF(MONTH($H4)=MONTH($E$4:$E$15),$F$4:$F$15,0),0))/SUM(IFERROR(IF(MONTH($E$4:$E$15)=MONTH($H4),1,0),0)),0)
Copy this down to I15. Enter with Ctrl+Shift+Enter.

PS: I think your defnition of monthly average is wrong some where. Because in your sample data if you Just highlight the no. of docs you can see that system is calculating 75 as average. The same is shown in pivot table result also on sheet 4, of the attached file.

Just advise if any issue.

Regards!

 

[kensor81]

Hi, kensor81!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, next time consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.

Hi SirJB7,


Sorry for my mistake and my sincere apologies.

I have now read through the post in “New Users - Please Start Here” forum section as you stated.

I’m also sorry for not including an Excel file together with my question. I will do better next time,

I’m currently looking at all of your good help and suggestions, and I’m looking forward to start testing it.

Thank you all for the kind help so far, it is very much appreciated.

 

[SirJB7]

Hi, kensor81!
No need to apologize for nothing, you're a recently joined member so it's natural that you miss something.
About your issue, please check if any of both suggestions work for you and tell us if you have any issue.
Regards!