average all but last entry

[gtd526]

Hello,
Need to average all entries but the LAST one.
Use Test in C30 & C31.
Thank you

The Whole Enchilada.xlsm
	A	B	C
3	Sector	G/L %	Date
4	Tech 3X	2.65%	1/5/22
5	Tech 3X	-39.56%	2/1/22
6	Tech 3X	-49.60%	3/1/22
7	Tech 3X	-47.73%	4/8/22
8	Tech 3X	-73.16%	5/24/22
9	Tech 3X	-65.51%	6/1/22
10	Tech 3X	-5.93%	8/19/22
11	Tech 3X	-21.27%
12	Finance 3x	30.29%	10/28/22
13	Tech 3X	-1.87%
14	Finance 3x	9.01%	3/12/23
15	Tech 3X	33.60%
16	Finance 3x	-0.12%	4/1/23
17	Tech 3X	31.58%
18	Finance 3x	-0.64%	5/6/23
19	Tech 3X	70.14%
20	Finance 3x	5.54%	6/14/23
21	Tech 3X	94.11%
22	Finance 3x	10.11%	7/1/23
23	Tech 3X	100.04%
24	Finance 3x	14.91%
25	TSLA 1.5x	5.19%	7/17/23
26	Tech 3X	99.60%
27	Finance 3x	24.99%	7/23/23
28
29			Test
30		Tech 3x	8.47%
31		Finance 3x	11.76%
Sector Perform-ALL

Cell Formulas
Range	Formula
C30	C30	=AVERAGEIFS(B4:B27,A4:A27,B30)
C31	C31	=AVERAGEIFS(B4:B27,A4:A27,B31)

 

[alansidman]

With Power Query aka Get and Transform Data and found on the Data Tab

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Sector"}, {{"Average", each List.Average([#"G/L %"]), type number}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Average", Percentage.Type}})
in
    #"Changed Type"

Sector	Average
Tech 3X	0.08472926
Finance 3x	0.117617769
TSLA 1.5x	0.051912568

 

[gtd526]

With Power Query aka Get and Transform Data and found on the Data Tab

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Sector"}, {{"Average", each List.Average([#"G/L %"]), type number}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Average", Percentage.Type}})
in
    #"Changed Type"

Sector	Average
Tech 3X	0.08472926
Finance 3x	0.117617769
TSLA 1.5x	0.051912568

not interested in Power Query, looking for a formula. Your results are giving me the same results as the formulas im using now.
i want to calculate the average MINUS the last entry (ie Tech 3X will calculate the average WITHOUT using row 26 in the equation (its the last entry).

 

[Joe4]

Try this formula in cell C30 and copy down to C31:

=(SUMIFS($B$4:$B$27,$A$4:$A$27,B30)-LOOKUP(2,1/($A$4:$A$27=B30),$B$4:$B$27))/(COUNTIFS($A$4:$A$27,B30)-1)

 

[gtd526]

Try this formula in cell C30 and copy down to C31:

=(SUMIFS($B$4:$B$27,$A$4:$A$27,B30)-LOOKUP(2,1/($A$4:$A$27=B30),$B$4:$B$27))/(COUNTIFS($A$4:$A$27,B30)-1)

Thank you!

 

[Joe4]

You are welcome.