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]

Try this:

MrExcel_2024-12.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	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	AVARAGE	RESULT
2		1	2	3	4	5	6	7	8	9	10	11	12
3	JOHN													0
4	ANNA	30			100			5			5	10	10	160	27	17
5	LISA	10	4	3		50	3		3	7	8	7	5	100	10	5
6	MARIE				50				50				100	200	67
7	ROB	1	2		3	4	100		8		8	8	4	138	15	11
8
9	RESULTS
10	ANNA	17
11	LISA	5
12	ROB	11
Sheet1

Cell Formulas
Range	Formula
O3:O7	O3	=SUM(Tabela7[@[JAN. 2024]:[DEZ. 2024]])
P3:P7	P3	=IFERROR(AVERAGE(Tabela7[@[JAN. 2024]:[DEZ. 2024]]),"")
Q3:Q7	Q3	= IF(AND(P3>INDEX(C3:N3,1,XMATCH(MONTH(TODAY()),MONTH($C$2:$N$2),0,1)),P3<>""), ABS(INDEX(C3:N3,1,XMATCH(MONTH(TODAY()),MONTH($C$2:$N$2),0,1))-P3), "")
B10:C12	B10	= FILTER(FILTER(B3:Q7,Q3:Q7<>""),{1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1})
Dynamic array formulas.

Edit:
Keep in mind that cells C2:N2 have to be actual dates in order to make the formulas work!
For this I use values from 1/24 to 12/24 with custom number formatting m (but you could use any format here like mmm. yyyy)

 

[Lacan]

Dear @PeteWright

Thank you very much for your help.
In order to help just made a formula to make months sequentially.
Think a formula with Let function and Filter function would be shorter, simple and efficiency.
Can you please help?
Again, 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	JAN. 24	FEV. 24	MAR. 24	ABR. 24	MAI. 24	JUN. 24	JUL. 24	AGO. 24	SET. 24	OUT. 24	NOV. 24	DEZ. 24	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
C2:N2	C2	=TRANSPOSE(UPPER(TEXT(EDATE(EOMONTH(TODAY(),-12)+1,SEQUENCE(12,1,0)),"mmm. aa")))
O3:O7	O3	=SUM('07-12-2024'!$C3:$N3)
P3:P7	P3	=IFERROR(AVERAGE('07-12-2024'!$C3:$N3),"")
Dynamic array formulas.

 

[PeteWright]

This should do it:

MrExcel_2024-12.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	NAMES	JAN. 24	FEB. 24	MAR. 24	APR. 24	MAY. 24	JUN. 24	JUL. 24	AUG. 24	SEP. 24	OCT. 24	NOV. 24	DEC. 24	TOTAL	AVERAGE
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	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
07-12-2024

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

Edit:
Please change the formulas to match your locale, e.g. "mmm. aa" instead of "mmm. yy"

 

[Lacan]

This should do it:

MrExcel_2024-12.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	NAMES	JAN. 24	FEB. 24	MAR. 24	APR. 24	MAY. 24	JUN. 24	JUL. 24	AUG. 24	SEP. 24	OCT. 24	NOV. 24	DEC. 24	TOTAL	AVERAGE
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	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
07-12-2024

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

Edit:
Please change the formulas to match your locale, e.g. "mmm. aa" instead of "mmm. yy"

Brilliant @PeteWright !!!
Have to test it more but so far so Perfect!!!

One last question, have to convert Portuguese months to Spanish months Language.
How can that be done in order that both formulas work well like the unique spill formula range "=TRANSPOSE(UPPER(TEXT(EDATE(EOMONTH(TODAY(),-12)+1,SEQUENCE(12,1,0)),"mmm. yy")))" and your Let formula?
If so can you give a hand?
Thank you very much Pete.

 

[PeteWright]

I'm not sure if I got you right.
Do you use multiple languages?

If you only need to make it work with one language just change all occurences of "mmm. yy" to "mmm. aa"
Should be the same in Portuguese and Spanish a for year and m for month.

Is that what you need?

 

[Lacan]

I'm not sure if I got you right.
Do you use multiple languages?

If you only need to make it work with one language just change all occurences of "mmm. yy" to "mmm. aa"
Should be the same in Portuguese and Spanish a for year and m for month.

Is that what you need?

Let me clarify @PeteWright

My Office 365 Excel is in default Portuguese Preference Language however my job sales data especially the months are in Spanish Language.
That´s why my question.
What should do.
Thanks again Pete.

 

[PeteWright]

I see...

In Spanish it is Años (yy) and Meses (m) while in Portuguese it is Anos (aa) and Meses (m)

So you need a formula set that considers both, right?

Let me think...

 

[PeteWright]

I think this is it:

Note the explicit language specific format [$-pt-PT] for Portuguese (Portugal)

MrExcel_2024-12.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	NAMES	JAN. 24	FEV. 24	MAR. 24	ABR. 24	MAI. 24	JUN. 24	JUL. 24	AGO. 24	SET. 24	OUT. 24	NOV. 24	DEZ. 24	TOTAL	AVERAGE
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	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
07-12-2024

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

Please select "accept solution" if this solves your problem. Thanks.

 

[Lacan]

So Beautiful @PeteWright
Have to test it more however so far so Perfect!!!

Just some last arrangements, please update in your Let formula the possibility:

-Exclude List names even if meet criteria ("Marie"):
-Total in 3rd Column

Thank you very much Pete!!!!

Here is table.

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		ANNA	17	78		MARIE
11		LISA	5
12		MARIE	50
13		ROB	6
14
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:C13	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),Result)
D10	D10	=SUM(C10:C13)
Dynamic array formulas.