MAXIFS/MINIFS

[phillipus2005]

Hi

I have a long normalised table of data (research results for multiple countries in multiple waves). I am looking for a formula to identify the earliest and latest results for each question per market. In the table below, I would like to have "baseline" returned in column O for the earliest wave in each country for which there is data in column N and "latest" returned in column O for the latest wave in each country for which there is data in column N. The trouble is that in some countries, in some waves, the question was not asked at first so the "baseline" label refers to a blank score in column N so when pivoting the data to show change from baseline, it skews the results... i.e. in the below, "baseline" should be in O3 for Country 1 NOT O2.

My current formula returns "baseline" for the earliest wave (column M) per country (column L), i.e. O2, below. I can't figure out how to also factor in Column N. My current formula is: =IF(MAXIFS([Wave],[Market],[@Market])=[@Wave], "Latest", IF(MINIFS([Wave],[Market],[@Market])=[@Wave], "Baseline", ""))

Any help on how to modify this would be much appreciated - i've tried adding in another condition in the maxifs and minifs but they return blank in O3 (i guess because M2 is not the max/min)

Thanks in advance

	L	M	N	O
1​	Country	Wave	Score	Baseline/latest
2​	Country 1	1​		Baseline
3​	Country 1	2​	10​
4​	Country 1	3​	20​	Latest
5​	Country 2	1​	10​	Baseline
6​	Country 2	2​	15​
7​	Country 2	3​	20​	Latest

 

[Peter_SSs]

Is this what you are after?

24 02 22.xlsm
	L	M	N	O
1	Country	Wave	Score	Baseline/latest
2	Country 1	1
3	Country 1	2	10	Baseline
4	Country 1	3	20	Latest
5	Country 2	1	10	Baseline
6	Country 2	2	15
7	Country 2	3	20	Latest
Baseline

Cell Formulas
Range	Formula
O2:O7	O2	=IF(COUNTIFS(L$2:L2,L2,N$2:N2,"<>")=1,"Baseline",IF(COUNTIFS(L2:L$7,L2,N2:N$7,"<>")=1,"Latest",""))

 

[phillipus2005]

Hi - thanks - yes it is... sort of
I can't figure out how it works - how would i apply it to an excel dynamic table?

 

[Peter_SSs]

how would i apply it to an excel dynamic table?

phillipus2005.xlsm
	L	M	N	O
1	Country	Wave	Score	Baseline/latest
2	Country 1	1
3	Country 1	2	10	Baseline
4	Country 1	3	20	Latest
5	Country 2	1	10	Baseline
6	Country 2	2	15
7	Country 2	3	20	Latest
Baseline

Cell Formulas
Range	Formula
O2:O7	O2	=IF(COUNTIFS(INDEX([Country],1):[@Country],[@Country],INDEX([Score],1):[@Score],"<>")=1,"Baseline",IF(COUNTIFS([@Country]:INDEX([Country],ROWS([Country])),[@Country],[@Score]:INDEX([Score],ROWS([Score])),"<>")=1,"Latest",""))

 

[phillipus2005]

Thank you - testing now - appreciate the help!!

 

[phillipus2005]

Hi - this doesn't quite work - in case helpful, here is a more detailed table with three columns at the end showing: (1) what my original formula returned, (2) what it should show, and (3) what your formula returned...

Any advice greatly appreciated - you'll see the use case i'm trying to deal with is how to handle "Baseline" data that was not in the first wave of the research (Wave 0)... I've highlighted in red text below where this is an issue...

Thank you again

Market	Segment	Section	Subsection	Wave	Date	Score	What it currently shows (my original formula)	What it should show	What your formula returned
Country 1	GenPop	Awareness	Familiar with XXXX	0	Dec-20	81	Baseline	Baseline	Baseline
Country 1	GenPop	Awareness	Familiar with YYYY	0	Dec-20	56	Baseline	Baseline
Country 1	GenPop	Perceptions	XXXX is a positive development	0	Dec-20	27	Baseline	Baseline
Country 1	GenPop	Perceptions	YYYY is a positive development	0	Dec-20	21	Baseline	Baseline
Country 1	GenPop	Awareness	Familiar with XXXX	1	Dec-21	82
Country 1	GenPop	Awareness	Familiar with YYYY	1	Dec-21	40
Country 1	GenPop	Perceptions	XXXX is a positive development	1	Dec-21	89
Country 1	GenPop	Perceptions	YYYY is a positive development	1	Dec-21	72
Country 1	GenPop	Awareness	Familiar with XXXX	2	Jul-23	59
Country 1	GenPop	Awareness	Familiar with YYYY	2	Jul-23	60
Country 1	GenPop	Perceptions	XXXX is a positive development	2	Jul-23	78
Country 1	GenPop	Perceptions	YYYY is a positive development	2	Jul-23	62
Country 1	GenPop	Awareness	Familiar with XXXX	3	Dec-23	91	Latest	Latest
Country 1	GenPop	Awareness	Familiar with YYYY	3	Dec-23	68	Latest	Latest
Country 1	GenPop	Perceptions	XXXX is a positive development	3	Dec-23	56	Latest	Latest
Country 1	GenPop	Perceptions	YYYY is a positive development	3	Dec-23	61	Latest	Latest	Latest
Country 2	GenPop	Awareness	Familiar with XXXX	0	Dec-20		Baseline		Baseline
Country 2	GenPop	Awareness	Familiar with YYYY	0	Dec-20		Baseline
Country 2	GenPop	Perceptions	XXXX is a positive development	0	Dec-20		Baseline
Country 2	GenPop	Perceptions	YYYY is a positive development	0	Dec-20		Baseline
Country 2	GenPop	Awareness	Familiar with XXXX	1	Dec-21	82		Baseline
Country 2	GenPop	Awareness	Familiar with YYYY	1	Dec-21	40		Baseline
Country 2	GenPop	Perceptions	XXXX is a positive development	1	Dec-21	89		Baseline
Country 2	GenPop	Perceptions	YYYY is a positive development	1	Dec-21	72		Baseline
Country 2	GenPop	Awareness	Familiar with XXXX	2	Jul-23	59
Country 2	GenPop	Awareness	Familiar with YYYY	2	Jul-23	60
Country 2	GenPop	Perceptions	XXXX is a positive development	2	Jul-23	78
Country 2	GenPop	Perceptions	YYYY is a positive development	2	Jul-23	62
Country 2	GenPop	Awareness	Familiar with XXXX	3	Dec-23	91	Latest	Latest
Country 2	GenPop	Awareness	Familiar with YYYY	3	Dec-23	68	Latest	Latest
Country 2	GenPop	Perceptions	XXXX is a positive development	3	Dec-23	56	Latest	Latest
Country 2	GenPop	Perceptions	YYYY is a positive development	3	Dec-23	61	Latest	Latest	Latest

 

[Peter_SSs]

in case helpful, here is a more detailed table

It certainly is more helpful as it appears Date is relevant and Date was not mentioned or shown in the original information so I assumed "earliest" and "latest" simply meant first/last in the column.

See if this is closer.

phillipus2005.xlsm
	A					F	G		I		K
1	Market					Date	Score		should show		New Try
2	Country 1					Dec-20	81		Baseline		Baseline
3	Country 1					Dec-20	56		Baseline		Baseline
4	Country 1					Dec-20	27		Baseline		Baseline
5	Country 1					Dec-20	21		Baseline		Baseline
6	Country 1					Dec-21	82
7	Country 1					Dec-21	40
8	Country 1					Dec-21	89
9	Country 1					Dec-21	72
10	Country 1					Jul-23	59
11	Country 1					Jul-23	60
12	Country 1					Jul-23	78
13	Country 1					Jul-23	62
14	Country 1					Dec-23	91		Latest		Latest
15	Country 1					Dec-23	68		Latest		Latest
16	Country 1					Dec-23	56		Latest		Latest
17	Country 1					Dec-23	61		Latest		Latest
18	Country 2					Dec-20
19	Country 2					Dec-20
20	Country 2					Dec-20
21	Country 2					Dec-20
22	Country 2					Dec-21	82		Baseline		Baseline
23	Country 2					Dec-21	40		Baseline		Baseline
24	Country 2					Dec-21	89		Baseline		Baseline
25	Country 2					Dec-21	72		Baseline		Baseline
26	Country 2					Jul-23	59
27	Country 2					Jul-23	60
28	Country 2					Jul-23	78
29	Country 2					Jul-23	62
30	Country 2					Dec-23	91		Latest		Latest
31	Country 2					Dec-23	68		Latest		Latest
32	Country 2					Dec-23	56		Latest		Latest
33	Country 2					Dec-23	61		Latest		Latest
Sheet2

Cell Formulas
Range	Formula
K2:K33	K2	=IF([@Date]=MINIFS([Date],[Market],[@Market],[Score],"<>"),"Baseline",IF([@Date]=MAXIFS([Date],[Market],[@Market],[Score],"<>"),"Latest",""))

 

[phillipus2005]

Thank you - this is PERFECT. I was using Wave as a proxy for the date (as the earliest is wave 0)... it looks like i was getting the formula to determine if score is blank wrong

Thank you again!!!

 

[Peter_SSs]

You're welcome. Glad we got it sorted in the end. Thanks for the follow-up.