Conditional max array formula for panel data

[mdav_ak]

Hey all,

Just wanted to preface by saying THANK YOU to all of you Excel gurus out there. I am a grad student working as a research assistant for a prof and life would be way harder if
I didn't have Mr. Excel and other online communities out there to peruse.

I have panel style data (variables for country, year, etc. in columns) and am trying to create a conditional maximum array formula. My data looks like this:

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {text-align:left;} --> </style> <table style="border-collapse: collapse;width:195pt" border="0" cellpadding="0" cellspacing="0" width="195"> <colgroup><col style="width:65pt" span="3" width="65"> </colgroup><tbody><tr style="height:14.0pt" height="14"> <td class="xl63" style="height:14.0pt;width:65pt" height="14" width="65">iso</td> <td class="xl63" style="width:65pt" width="65">gdp</td> <td class="xl63" style="width:65pt" width="65">year</td> </tr> <tr style="height:14.0pt" height="14"> <td class="xl63" style="height:14.0pt" height="14">AFG</td> <td class="xl63">.</td> <td class="xl63">1991</td> </tr> <tr style="height:14.0pt" height="14"> <td class="xl63" style="height:14.0pt" height="14">ALB</td> <td class="xl63">2</td> <td class="xl63">1992</td> </tr> <tr style="height:14.0pt" height="14"> <td class="xl63" style="height:14.0pt" height="14">DZA</td> <td class="xl63">3</td> <td class="xl63">1993</td> </tr> <tr style="height:14.0pt" height="14"> <td class="xl63" style="height:14.0pt" height="14">ASM</td> <td class="xl63">4</td> <td class="xl63">1995</td> </tr> </tbody></table>
I would like to create an array formula in another worksheet that gives me the minimum year value based on column for each country
(i.e. the formula would look at all the available values for GDP above, it would notice that the lowest year value where there is data for
a non-blank GDP cell is in the 1992 row, and then it would return the minimum year value for which there is data—"1992").

The second worksheet, where I would put the results, looks like this:

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} --> </style> <table style="border-collapse: collapse;width:212pt" border="0" cellpadding="0" cellspacing="0" width="212"> <colgroup><col style="width:53pt" span="4" width="53"> </colgroup><tbody><tr style="height:14.0pt" height="14"> <td style="height:14.0pt;width:53pt" height="14" width="53">iso</td> <td style="width:53pt" width="53">gdp</td> <td style="width:53pt" width="53">er</td> <td style="width:53pt" width="53">gas_rp</td> </tr> <tr style="height:14.0pt" height="14"> <td style="height:14.0pt" height="14">AFG</td> <td>1992
</td> <td>.
</td> <td>.</td> </tr> <tr style="height:14.0pt" height="14"> <td style="height:14.0pt" height="14">ALB</td> <td>.
</td> <td>.
</td> <td>.</td> </tr> <tr style="height:14.0pt" height="14"> <td style="height:14.0pt" height="14">DZA</td> <td>.
</td> <td>.
</td> <td>.</td> </tr> </tbody></table>

So far I've tried highlighting all the required cells in the second worksheet, then using this array formula w/ Ctrl+Shift+Enter:

={MIN(IF(AND(iso_list=A2,gdp_list>0),year_list))}

But this just gives me zeroes all the way down. I've tried searching the forums and nothing seems to help. Guidance with the formula would be much appreciated.

 

[Robert Mika]

Hi mdav_ak and welcome to the board.
Based on Your example you do not need to lookup at the country column.
Try this:

Excel Workbook
	A	B	C	D
1	iso	gdp	er	gas_rp
2	AFG	1992
3	ALB	1992
4	DZA	1992
Sheet2

 

[mdav_ak]

Thank you for getting back to me. I tried the array formulas and they work for that first column, but I am not sure they are doing the right thing. I basically want to know the earliest year I have data for, for each country. GDP is just one of the variables I am looking at (er and gas_rp are two more).

I think your formula will help me find the minimum value of the variable, rather than the year. Is there some way for the formula to only look at non-blank values for a specific country (e.g. AFG) in a column, and then return the minimum corresponding year for that country?

In my example, the results in the second worksheet would be:

<table border="0" cellpadding="0" cellspacing="0" width="212"><tbody><tr style="height:14.0pt" height="14"><td style="height:14.0pt;width:53pt" height="14" width="53">iso</td> <td style="width:53pt" width="53">gdp</td> <td style="width:53pt" width="53">er</td> <td style="width:53pt" width="53">gas_rp</td> </tr> <tr style="height:14.0pt" height="14"> <td style="height:14.0pt" height="14">AFG</td> <td>.
</td> <td>.
</td> <td>.</td> </tr> <tr style="height:14.0pt" height="14"> <td style="height:14.0pt" height="14">ALB</td> <td>1992
</td> <td>.
</td> <td>.</td> </tr> <tr style="height:14.0pt" height="14"> <td style="height:14.0pt" height="14">DZA</td> <td>1993
</td> <td>.
</td> <td>.</td></tr></tbody></table>
since 1992 was the earliest year for ALB data, and 1992 was the earliest for DZA data. There was no data for AFG, so it would blank.

 

[Robert Mika]

Looking at your first post and my solution this was exactly whay you need( or at least it looked like) with AFG 1992 for GDP.

Could you post more data?
Do you have separate year column for each category(gdp, er, gas_rp)