How to filtered/extract names and calculate difference between values?

[Lacan]

Hi Guys,

Hope everyone is ok!
Would like some help in order to filtered/extract names and calculate difference between atual month (December) and average?
Some notes:
-Only names if value average is greater then value atual month;
-Not blank cells.
In sheet there is the example of Expected Results.
Any question, please let me know.
Thank you very much.
Big hug.

Fórmula Principal para o Ficheiro VDF.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2		NAMES	JAN. 2024	FEV. 2024	MAR. 2024	ABR. 2024	MAI. 2024	JUN. 2024	JUL. 2024	AGO. 2024	SET. 2024	OUT. 2024	NOV. 2024	DEZ. 2024	TOTAL	AVERAGE
3		JOHN
4		ANNA	30			100			5			5	10	10	160	27
5		LISA	10	4	3		50	3		3	7	8	7	5	100	10
6		MARIE				50				50				100	200	67
7		ROB	1	2		3	4	100		8		8	8	4	138	15
8
9		EXPECTED RESULTS
10		ANNA	17
11		LISA	5
12		ROB	11
13
14
07-12-2024

Cell Formulas
Range	Formula
O3:O7	O3	=SUM(Tabela7[@[JAN. 2024]:[DEZ. 2024]])
P3:P7	P3	=IFERROR(AVERAGE(Tabela7[@[JAN. 2024]:[DEZ. 2024]]),"")

 

[PeteWright]

@Lacan Here we go:

MrExcel_2024-12.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	NAMES	ENE. LU.	FEB. JU.	MAR. VI.	ABR. LU.	MAY. MI.	JUN. SÁ.	JUL. LU.	AGO. JU.	SEP. DO.	OCT. MA.	NOV. VI.	DIC. DO.	TOTAL	AVERAGE	EXCLUDE
3	JOHN													0
4	ANNA	30			100			5			5	10	10	160	27
5	LISA	10	4	3		50	3		3	7	8	7	5	100	10
6	MARIE				50				50					100	50	x
7	ROB	1	2		3	4	100		8		8	8	10	144	16
8
9	RESULTS		TOTAL
10	ANNA	17	28
11	LISA	5
12	ROB	6
07-12-2024 (2)

Cell Formulas
Range	Formula
C2:N2	C2	=TRANSPOSE(UPPER(TEXT(EDATE(EOMONTH(TODAY(),-12)+1,SEQUENCE(12,1,0)),"[$-es-ES]mmm aaa")))
O3:O7	O3	=SUM('07-12-2024 (2)'!$C3:$N3)
P3:P7	P3	=IFERROR(AVERAGE('07-12-2024 (2)'!$C3:$N3),"")
B10:C12	B10	=LET(Name,B3:B7,Tot,O3:O7,Avrg,P3:P7,curMonVal,XLOOKUP(UPPER(TEXT(TODAY(),"[$-es-ES]mmm aaa")),C2#,C3:N7,"N/A",0,1),deltaAV,IFERROR(ROUND(Avrg-curMonVal,0),0),crit,deltaAV>0,ex,Q3:Q7="",Result,FILTER(HSTACK(Name,deltaAV),(crit*ex)),Result)
D10	D10	=SUM(C10:C13)
Dynamic array formulas.

Just type anything into the EXCLUDE column and the corresponding row (Name) will be excluded.

 

[Lacan]

@Lacan Here we go:

MrExcel_2024-12.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	NAMES	ENE. LU.	FEB. JU.	MAR. VI.	ABR. LU.	MAY. MI.	JUN. SÁ.	JUL. LU.	AGO. JU.	SEP. DO.	OCT. MA.	NOV. VI.	DIC. DO.	TOTAL	AVERAGE	EXCLUDE
3	JOHN													0
4	ANNA	30			100			5			5	10	10	160	27
5	LISA	10	4	3		50	3		3	7	8	7	5	100	10
6	MARIE				50				50					100	50	x
7	ROB	1	2		3	4	100		8		8	8	10	144	16
8
9	RESULTS		TOTAL
10	ANNA	17	28
11	LISA	5
12	ROB	6
07-12-2024 (2)

Cell Formulas
Range	Formula
C2:N2	C2	=TRANSPOSE(UPPER(TEXT(EDATE(EOMONTH(TODAY(),-12)+1,SEQUENCE(12,1,0)),"[$-es-ES]mmm aaa")))
O3:O7	O3	=SUM('07-12-2024 (2)'!$C3:$N3)
P3:P7	P3	=IFERROR(AVERAGE('07-12-2024 (2)'!$C3:$N3),"")
B10:C12	B10	=LET(Name,B3:B7,Tot,O3:O7,Avrg,P3:P7,curMonVal,XLOOKUP(UPPER(TEXT(TODAY(),"[$-es-ES]mmm aaa")),C2#,C3:N7,"N/A",0,1),deltaAV,IFERROR(ROUND(Avrg-curMonVal,0),0),crit,deltaAV>0,ex,Q3:Q7="",Result,FILTER(HSTACK(Name,deltaAV),(crit*ex)),Result)
D10	D10	=SUM(C10:C13)
Dynamic array formulas.

Just type anything into the EXCLUDE column and the corresponding row (Name) will be excluded.

Dear @PeteWright

Thanks again.
Fortunately figured out also how to add "exclude list" in your formula near to my job workbook.
To finish would like to add in your formula the sum function and to be integrated in an unique formula like in table "Expected Results".

To conclude please can you give a hand?
Thank you very much.

Fórmula Principal para o Ficheiro VDF.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2		NAMES	ENE. 2024	FEB. 2024	MAR. 2024	ABR. 2024	MAY. 2024	JUN. 2024	JUL. 2024	AGO. 2024	SEP. 2024	OCT. 2024	NOV. 2024	DIC. 2024	TOTAL	AVERAGE
3		JOHN
4		ANNA	30			100			5			5	10	10	160	27
5		LISA	10	4	3		50	3		3	7	8	7	5	100	10
6		MARIE				50				50					100	50
7		ROB	1	2		3	4	100		8		8	8	10	144	16
8
9		EXPECTED RESULTS		TOTAL		EXCLUDE LIST
10		LISA	5	55		ANNA
11		MARIE	50			ROB
12
13
07-12-2024 (2)

Cell Formulas
Range	Formula
C2:N2	C2	=TRANSPOSE(UPPER(TEXT(EDATE(EOMONTH(TODAY(),-12)+1,SEQUENCE(12,1,0)),"[$-es-ES]mmm aaa")))
O3:O7	O3	=SUM('07-12-2024 (2)'!$C3:$N3)
P3:P7	P3	=IFERROR(AVERAGE('07-12-2024 (2)'!$C3:$N3),"")
B10:C11	B10	=LET(Name,B3:B7,Tot,O3:O7,Avrg,P3:P7,curMonVal,XLOOKUP(UPPER(TEXT(TODAY(),"[$-es-ES]mmm aaa")),C2#,C3:N7,"N/A",0,1),deltaAV,IFERROR(ROUND(Avrg-curMonVal,0),0),crit,deltaAV>0,Result,FILTER(HSTACK(Name,deltaAV),crit*ISNA(MATCH(Name,F10:F12,0))),Result)
D10	D10	=SUM(C10:C13)
Dynamic array formulas.

 

[PeteWright]

@Lacan How would you like it to be?

Fortunately figured out also how to add "exclude list" in your formula near to my job workbook.
To finish would like to add in your formula the sum function and to be integrated in an unique formula like in table "Expected Results".

Should the "exclude list" be some fixed range in your worksheet? If so, it would be "hard coded" and limited to a static number of exclusions.
That's why I came up with the idea of adding an extra column to the right of your table.
So you don't like it

Please let me know how you would like to handle excluded names and I'll incorporate that in a formula.

EDIT:
If you use an exclude list where you just "type in" the names there could also be a typo which would result in that name not being excluded.
Think about that too

 

[PeteWright]

P.S. How about checkboxes?

Edit 2:
Are there always 5 entries / names or can there be more?

 

[Lacan]

P.S. How about checkboxes?

View attachment 120201

Edit 2:
Are there always 5 entries / names or can there be more?

Dear @PeteWright

First, thank you very much!!!
You´re the Greatest!!!!
In my main List have almost 250 entries/names and in the exclude List contains 6 entries/names.
What you think to be pratical and efficient?

If you could would like to add the sum in your Let formula in order to sum all the entries/names if meet criteria.

We keep in touch my friend.
Very grateful!!!