i having troubles formulating problem hope understand!
i have table of firms building production plants in foreign countries in years. (columns c).
in seperate table have so-called cross-national distance measures (based on difference in gdp of countries). (columns g m). note distances change per year.
a simplified version of excel this: https://new.wu.ac.at/fileadmin/wu/d/i/iib/photo/stack.jpg
what want formula manually entered results in column d. shall give me result following:
- it shall in countries specific company has (years before) built plants
- it shall find smallest cross-national distance current country of countries entered
- the value should year of current plant-construction
let me illustrate request example result want in cell d8:
- the formula have find list of countries entered in case turkey , bulgaria
- it have second table , give me minimum of distances kosovo turkey , bulgaria
- this have done in rows 2008 (current year)
i hope guys can me, figured out way find minimum in list , can years issue having excel first needs find entered countries, memorize them in kind of array , use these countries consider minimum distance.
thank much!
try "array formula" d2 copied down
=iferror(small(if(countifs(a$2:a$11,a2,b$2:b$11,"<"&b2,c$2:c$11,"<>"&c2,c$2:c$11,i$1:m$1)*(g$2:g$31=b2)*(h$2:h$31=c2),i$2:m$31),1),"n/a")
confirmed ctrl+shift+enter
that checks 3 conditions larger table - header row matches qualifying country (using countifs function based on criteria in small table), column g matches current year , column h matches current country.
if criteria satisfied relevant values in table returned, , small finds smallest. if there's error (because there no qualifying values) n/a returned
in excel 2010 or later versions can use aggregate function instead of small - useful because doesn't require "array entry"
=iferror(aggregate(15,6,i$2:m$31/(countifs(a$2:a$11,a2,b$2:b$11,"<"&b2,c$2:c$11,"<>"&c2,c$2:c$11,i$1:m$1)>0)/(g$2:g$31=b2)/(h$2:h$31=c2),1),"n/a")
