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!!!

 

[Lacan]

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.
View attachment 120257

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

Dear @PeteWright

Kindly reminder.
Thank you very much.

 

[PeteWright]

Hello @Lacan

After many attempts with trial and error I think I have found the right formula. Try this:

MrExcel_2024-12.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
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		NAME	DELTA	TOTAL	EXCLUDE
3		JOHN													0	0		LISA	5	55	ANNA
4		ANNA	30			100			5			5	10	10	160	26.66667		MARIE	50		ROB
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
Table

Cell Formulas
Range	Formula
C2:N2	C2	=TRANSPOSE(UPPER(TEXT(EDATE(EOMONTH(TODAY(),-12)+1,SEQUENCE(12,1,0)),"[$-es-ES]mmm yyy")))
O3:O7	O3	=SUM(Table!$C3:$N3)
P3:P7	P3	=IFERROR(AVERAGEIF(Table!$C3:$N3,"<>0"),0)
R3:T4	R3	=LET(rowID,FILTER(ROW(B3:B1048576),NOT(ISBLANK(B3:B1048576)),""), NameCol,B:B, Name,CHOOSEROWS(NameCol,rowID), TotCol,O:O, Tot,CHOOSEROWS(TotCol,rowID), AvrgCol,P:P, Avrg,CHOOSEROWS(AvrgCol,rowID), curMonValCol,XLOOKUP(UPPER(TEXT(TODAY(),"[$-es-ES]mmm yyy")),C2#,C3:N1048576,"-",0,1), curMonVal,FILTER(curMonValCol,B3:B1048576<>"",0), deltaAV,Avrg-curMonVal, crit1a,deltaAV>0, crit1,FILTER(crit1a,NOT(ISNA(crit1a))), exCol,U3:U1048576, crit2a,FILTER(exCol,exCol<>0,""), crit2b,XMATCH(Name,crit2a,0,1), crit2,ISNA(crit2b), dArr,FILTER(deltaAV,crit1*crit2,0), TotRes,SUM(dArr), Result1,FILTER(HSTACK(Name,ROUND(deltaAV,0)), crit1*crit2,""), Result2,ROUND(TotRes,0), Result,IFNA(HSTACK(Result1,FILTER(Result2,Result2,"")),""), Result)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
U3:U8	List	=$B$3:$B$1048576

With Data Validation you won't get errors due to a typo in Name:

Although I have tested many conditions I would not consider the formula error-proof so please try it out and let me know if it works on your data.

 

[PeteWright]

Hi @Lacan

I did not get any reply from you. Did this solve your problem? If so, please mark it as solution. Thanks

 

[Lacan]

Hi @Lacan

I did not get any reply from you. Did this solve your problem? If so, please mark it as solution. Thanks

Dear @PeteWright

First, Happy New Year 2025 for you and all of your Family.
Just return 2 days ago from a short holidays and already back to full work.
Have a lot of things going on my life at the same time however need to improve my file with more efficient excel formulas and will schedule to see your formula next weekend with time and precision.
Sorry for the delay not forgotten because have also a few new excel challenges to ask in the Forum.
Leet you know then.
Thank you very much for all the help and remembering!!!

 

[Lacan]

Dear @PeteWright 🖐🖐

Im back - very sorry for the delay!!!
The Formula works Brilliant!!!
However after a few tests just notice if first row doesnt have data the formula doesnt responds (Im sure you will fix it simple!)
Thank you thank you @PeteWright

Doesnt Working!

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	R	S	T	U	V
1
2		NAMES	ENE. 2025	FEB. 2025	MAR. 2025	ABR. 2025	MAY. 2025	JUN. 2025	JUL. 2025	AGO. 2025	SEP. 2025	OCT. 2025	NOV. 2025	DIC. 2025	TOTAL	AVERAGE		NAME	DELTA	TOTAL	EXCLUDE
3		JOHN																#VALOR!
4		ANNA	5			100			5			5	10	5	130	22
5		LISA	10	4	3		50	3		3	7	8	7	4	99	10
6		MARIE	2			50				50			2	3	107	21
7		ROB	1	2		3	4	100		8		8	8		134	17
8
9
07-12-2024 (4)

Cell Formulas
Range	Formula
C2:N2	C2	=TRANSPOSE(UPPER(TEXT(EDATE("01-01-2025",SEQUENCE(12,1,0)),"[$-es-ES]mmm aaa")))
O3:O7	O3	=SUM('07-12-2024 (4)'!$C3:$N3)
P3:P7	P3	=IFERROR(AVERAGE('07-12-2024 (4)'!$C3:$N3),"")
R3	R3	=LET(rowID,FILTER(ROW(B3:B1048576),NOT(ISBLANK(B3:B1048576)),""), NameCol,B:B, Name,CHOOSEROWS(NameCol,rowID), Paracol,O:O, Tot,CHOOSEROWS(Paracol,rowID), AvrgCol,P:P, Avrg,CHOOSEROWS(AvrgCol,rowID), curMonValCol,XLOOKUP(UPPER(TEXT(TODAY(),"[$-es-ES]mmm aaa")),C2#,C3:N1048576,"-",0,1), curMonVal,FILTER(curMonValCol,B3:B1048576<>"",0), deltaAV,Avrg-curMonVal, crit1a,deltaAV>0, crit1,FILTER(crit1a,NOT(ISNA(crit1a))), exCol,U3:U1048576, crit2a,FILTER(exCol,exCol<>0,""), crit2b,XMATCH(Name,crit2a,0,1), crit2,ISNA(crit2b), dArr,FILTER(deltaAV,crit1*crit2,0), TotRes,SUM(dArr), Result1,FILTER(HSTACK(Name,ROUND(deltaAV,0)), crit1*crit2,""), Result2,ROUND(TotRes,0), Result,IFNA(HSTACK(Result1,FILTER(Result2,Result2,"")),""), Result)
Dynamic array formulas.

Working!!!

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	R	S	T	U	V
1
2		NAMES	ENE. 2025	FEB. 2025	MAR. 2025	ABR. 2025	MAY. 2025	JUN. 2025	JUL. 2025	AGO. 2025	SEP. 2025	OCT. 2025	NOV. 2025	DIC. 2025	TOTAL	AVERAGE		NAME	DELTA	TOTAL	EXCLUDE
3		JOHN	2												2	2		ANNA	17	52
4		ANNA	5			100			5			5	10	5	130	22		MARIE	19
5		LISA	10	4	3		50	3		3	7	8	7	4	99	10		ROB	16
6		MARIE	2			50				50			2	3	107	21
7		ROB	1	2		3	4	100		8		8	8		134	17
8
9
07-12-2024 (4)

Cell Formulas
Range	Formula
C2:N2	C2	=TRANSPOSE(UPPER(TEXT(EDATE("01-01-2025",SEQUENCE(12,1,0)),"[$-es-ES]mmm aaa")))
O3:O7	O3	=SUM('07-12-2024 (4)'!$C3:$N3)
P3:P7	P3	=IFERROR(AVERAGE('07-12-2024 (4)'!$C3:$N3),"")
R3:T5	R3	=LET(rowID,FILTER(ROW(B3:B1048576),NOT(ISBLANK(B3:B1048576)),""), NameCol,B:B, Name,CHOOSEROWS(NameCol,rowID), Paracol,O:O, Tot,CHOOSEROWS(Paracol,rowID), AvrgCol,P:P, Avrg,CHOOSEROWS(AvrgCol,rowID), curMonValCol,XLOOKUP(UPPER(TEXT(TODAY(),"[$-es-ES]mmm aaa")),C2#,C3:N1048576,"-",0,1), curMonVal,FILTER(curMonValCol,B3:B1048576<>"",0), deltaAV,Avrg-curMonVal, crit1a,deltaAV>0, crit1,FILTER(crit1a,NOT(ISNA(crit1a))), exCol,U3:U1048576, crit2a,FILTER(exCol,exCol<>0,""), crit2b,XMATCH(Name,crit2a,0,1), crit2,ISNA(crit2b), dArr,FILTER(deltaAV,crit1*crit2,0), TotRes,SUM(dArr), Result1,FILTER(HSTACK(Name,ROUND(deltaAV,0)), crit1*crit2,""), Result2,ROUND(TotRes,0), Result,IFNA(HSTACK(Result1,FILTER(Result2,Result2,"")),""), Result)
Dynamic array formulas.