Conditional max array formula for panel data

mdav_ak

New Member
Joined
Nov 7, 2011
Messages
2
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-ignore:padding; 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-ignore:padding; 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.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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
ABCD
1isogdpergas_rp
2AFG1992
3ALB1992
4DZA1992
Sheet2
 

mdav_ak

New Member
Joined
Nov 7, 2011
Messages
2
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

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,497
Members
414,072
Latest member
2020914

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top