Average sum of day

[BIGGAZ]

Hi,

I have a job sheet that enters the totals of a day. I need to average jobs completed each day for tracking. so sum each Tuesday and average against each other Tuesday.

I have used the averageifs function but that is averaging the jobs within that day.

This is the results page, with the formula above

This is the data page where the job details are entered

I need to sum it if the date is the same and machine is the same. then average that against same machine name but different date same day of the week.

Thanks

 

[alansidman]

Cannot manipulate data in picture. You can use power query to group on the date and machine to provide an average. If you will reload your sample data using XL2BB, then I can demonstrate.

 

[BIGGAZ]

Cannot manipulate data in picture. You can use power query to group on the date and machine to provide an average. If you will reload your sample data using XL2BB, then I can demonstrate.

I cannot install a program from an unknown source on my computer.

 

[alansidman]

Ok, then try my suggestion with Power Query

Load your table/range Data-->Get & Transform Data-->From Table/Range
Select Group Data-->Select your Machine Field and Date LoadField
Select your new Field Name and then select the field to sum
Close and Load to your Native Excel.

Or....
Load a sample file to a third party location such as DropBox or Box.Net

 

[BIGGAZ]

Ok, then try my suggestion with Power Query

Load your table/range Data-->Get & Transform Data-->From Table/Range
Select Group Data-->Select your Machine Field and Date Field
Select your new Field Name and then select the field to sum
Close and Load to your Native Excel.

Hi Alan,

Thanks very much for the idea but I think this would require manually selecting the correct data fields each time you want to get an average. Each week more data will get added to an endless list.

I think a modification of the above formula would be the way to go as it will auto update on new data.

Gary

 

[alansidman]

Not true. Will update automatically with new data added to the table. If you haven't worked with PQ, then you may be surprised how easy and efficient it is to work with.

 

[alansidman]

Book11
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Day	MACH N E	NAME	STITCHES	RU NS	COMPLETE		Date	MACH N E	Avg Stitch	Avg Runs	Avg Complete
2	10-Jan	TUESDAY	SINGLE HEAD		2726	2	2		1/10/2023	SINGLE HEAD	6079	2.5	2.5
3	10-Jan	TUESDAY	SINGLE HEAD		4184	5	5		1/11/2023	8Head	5335.571429	2.142857143	2.142857143
4	10-Jan	TUESDAY	SINGLE HEAD		11208	1	1				11283	5	5
5	10-Jan	TUESDAY	SINGLE HEAD		14046	3	3
6	10-Jan	TUESDAY	SINGLE HEAD		444	1	1
7	10-Jan	TUESDAY	SINGLE HEAD		4011	2	2
8	10-Jan	TUESDAY	SINGLE HEAD		730	1	1
9	10-Jan	TUESDAY	SINGLE HEAD		11283	5	5
10	11-Jan	Wednesay	8Head		2726	2	2
11	11-Jan	Wednesay	8Head		4184	5	5
12	11-Jan	Wednesay	8Head		11208	1	1
13	11-Jan	Wednesay	8Head		14046	3	3
14	11-Jan	Wednesay	8Head		444	1	1
15	11-Jan	Wednesay	8Head		4011	2	2
16	11-Jan	Wednesay	8Head		730	1	1
17					11283	5	5
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="tblRawData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "MACH N E"}, {{"Avg Stitch", each List.Average([STITCHES]), type number}, {"Avg Runs", each List.Average([RU NS]), type number}, {"Avg Complete", each List.Average([COMPLETE]), type number}})
in
    #"Grouped Rows"

Any data added to the orginal table will update automatically when the refresh button is selected.

 

[BIGGAZ]

Book11
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Day	MACH N E	NAME	STITCHES	RU NS	COMPLETE		Date	MACH N E	Avg Stitch	Avg Runs	Avg Complete
2	10-Jan	TUESDAY	SINGLE HEAD		2726	2	2		1/10/2023	SINGLE HEAD	6079	2.5	2.5
3	10-Jan	TUESDAY	SINGLE HEAD		4184	5	5		1/11/2023	8Head	5335.571429	2.142857143	2.142857143
4	10-Jan	TUESDAY	SINGLE HEAD		11208	1	1				11283	5	5
5	10-Jan	TUESDAY	SINGLE HEAD		14046	3	3
6	10-Jan	TUESDAY	SINGLE HEAD		444	1	1
7	10-Jan	TUESDAY	SINGLE HEAD		4011	2	2
8	10-Jan	TUESDAY	SINGLE HEAD		730	1	1
9	10-Jan	TUESDAY	SINGLE HEAD		11283	5	5
10	11-Jan	Wednesay	8Head		2726	2	2
11	11-Jan	Wednesay	8Head		4184	5	5
12	11-Jan	Wednesay	8Head		11208	1	1
13	11-Jan	Wednesay	8Head		14046	3	3
14	11-Jan	Wednesay	8Head		444	1	1
15	11-Jan	Wednesay	8Head		4011	2	2
16	11-Jan	Wednesay	8Head		730	1	1
17					11283	5	5
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="tblRawData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "MACH N E"}, {{"Avg Stitch", each List.Average([STITCHES]), type number}, {"Avg Runs", each List.Average([RU NS]), type number}, {"Avg Complete", each List.Average([COMPLETE]), type number}})
in
    #"Grouped Rows"

Any data added to the orginal table will update automatically when the refresh button is selected.

Thanks,

I will have a play. I just tried and couldnt get it i will watch some videos and read some stuff about it.