Average days by type and year

[PaulDaunno]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Days[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]12[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]24[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]65[/TD]
[TD]11[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]66[/TD]
[TD]44[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]23[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]66[/TD]
[TD]13[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]44[/TD]
[TD]2019[/TD]
[/TR]
[TR]
[TD]65[/TD]
[TD]8[/TD]
[TD]2019[/TD]
[/TR]
[TR]
[TD]66[/TD]
[TD]31[/TD]
[TD]2019[/TD]
[/TR]
</tbody>[/TABLE]


I want the average days by type, by year. So for example, in 2017, the average days for type 34 is 18. The average days for type 65 is 11.

I also want the average days by year for more than 1 type. So for example, in 2018, the average for type 34 and type 66 is 26.66.

What formulas would I use to accomplish this?

Thanks,
-Paul

 

[sandy666]

Re: Help Needed - Average days by type and year

Are you able to use Power Query aka Get&Transform?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Type[/td][td=bgcolor:#5B9BD5]Days[/td][td=bgcolor:#5B9BD5]Year[/td][td][/td][td=bgcolor:#70AD47]Type[/td][td=bgcolor:#70AD47]Year[/td][td=bgcolor:#70AD47]DaysAvg[/td][td][/td][td=bgcolor:#70AD47]Year[/td][td=bgcolor:#70AD47]DaysAvg[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

34​

[/td][td=bgcolor:#DDEBF7]

12​

[/td][td=bgcolor:#DDEBF7]

2017​

[/td][td][/td][td=bgcolor:#E2EFDA]

34​

[/td][td=bgcolor:#E2EFDA]

2017​

[/td][td=bgcolor:#E2EFDA]

18​

[/td][td][/td][td=bgcolor:#E2EFDA]

2018​

[/td][td=bgcolor:#E2EFDA]

26.66666667​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

34​

[/td][td]

24​

[/td][td]

2017​

[/td][td][/td][td]

65​

[/td][td]

2017​

[/td][td]

11​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

65​

[/td][td=bgcolor:#DDEBF7]

11​

[/td][td=bgcolor:#DDEBF7]

2017​

[/td][td][/td][td=bgcolor:#E2EFDA]

66​

[/td][td=bgcolor:#E2EFDA]

2018​

[/td][td=bgcolor:#E2EFDA]

28.5​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

66​

[/td][td]

44​

[/td][td]

2018​

[/td][td][/td][td]

34​

[/td][td]

2018​

[/td][td]

23​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

34​

[/td][td=bgcolor:#DDEBF7]

23​

[/td][td=bgcolor:#DDEBF7]

2018​

[/td][td][/td][td=bgcolor:#E2EFDA]

34​

[/td][td=bgcolor:#E2EFDA]

2019​

[/td][td=bgcolor:#E2EFDA]

44​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

66​

[/td][td]

13​

[/td][td]

2018​

[/td][td][/td][td]

65​

[/td][td]

2019​

[/td][td]

8​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

34​

[/td][td=bgcolor:#DDEBF7]

44​

[/td][td=bgcolor:#DDEBF7]

2019​

[/td][td][/td][td=bgcolor:#E2EFDA]

66​

[/td][td=bgcolor:#E2EFDA]

2019​

[/td][td=bgcolor:#E2EFDA]

31​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

65​

[/td][td]

8​

[/td][td]

2019​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

66​

[/td][td=bgcolor:#DDEBF7]

31​

[/td][td=bgcolor:#DDEBF7]

2019​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[DanteAmor]

Re: Help Needed - Average days by type and year

Try this

Book1
	A	B	C	D	E	F
1	Type	Days	Year		Year
2	34	12	2017		2018	26.667
3	34	24	2017
4	65	11	2017
5	66	44	2018
6	34	23	2018
7	66	13	2018
8	34	44	2019
9	65	8	2019
10	66	31	2019
Sheet1

Cell Formulas
Range	Formula
F2	=SUMPRODUCT((C2:C10=E2)*((A2:A10=34)+(A2:A10=66))*(B2:B10))/SUMPRODUCT((C2:C10=E2)*((A2:A10=34)+(A2:A10=66)))

 

[DanteAmor]

Re: Help Needed - Average days by type and year

Welcome to the forum!

You can also create a pivot table

 

[PaulDaunno]

Re: Help Needed - Average days by type and year

Thank you for your help, DanteAmor.

 

[DanteAmor]

Re: Help Needed - Average days by type and year

Im glad to help you. Thanks for the feedback.