"Averageifs" Function

[Michael Leder]

Hi everyone,

Hope you're well

In my research, I focus on IPOs since 2000 (no panel data). My dataset consists of one line per IPO incl. multiple information (e.g., financials, industry, issue year, etc.).

I want to calculate the averge industry revenue in the pre-IPO year. In a simplified version, my dataset looks like the table below.

I calculated the column "Avg. Industry Revenue in IPO Year" with the following command:
egen avg_industry_revenue_IPO_year = mean(Revenue), by (Industry IPO Year)

However, I do not find a proper solution to calculate the last column ("Avg. Industry Revenue in Year Before IPO). In excel, I would do it with the following command:
Averageifs(Revenue column; Industry Column; Industry; IPO Year; Year Before IPO)

As IPO Year and Year Before IPO are two different columns, I do not know to calculate this in Stata. I want to find out the average industry revenue in the year before the IPO (i.e., what was on average the revenue of competitors that went public in the year before the IPO of the observation?)

Looking forward to your support

Thank you very much!

Best,
Michael

Firm	Revenue	IPO Year	Year before IPO	Industry	Avg. Industry Revenue in IPO Year	Avg. Industry Revenue in Year Before IPO
A	67	2011	2010	Technology	78.5
B	90	2011	2010	Technology	78.5
C	97	2011	2010	Industrial	98.0
D	99	2011	2010	Industrial	98.0
E	48	2012	2011	Technology	41.0	78.5
F	34	2012	2011	Technology	41.0	78.5
G	14	2012	2011	Industrial	41.5	98.0
H	69	2012	2011	Industrial	41.5	98.0
I	12	2013	2012	Technology	15.5	41.0
J	19	2013	2012	Technology	15.5	41.0
K	53	2013	2012	Industrial	34.0	41.5
L	15	2013	2012	Industrial	34.0	41.5
M	71	2014	2013	Technology	58.5	15.5
N	46	2014	2013	Technology	58.5	15.5
O	36	2014	2013	Industrial	64.0	34.0
P	92	2014	2013	Industrial	64.0	34.0

[Clyde Schechter]

rangestat (firstnm) wanted = avgindustryrevenueinipoyear, by(industry) interval(ipoyear -1 -1)

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer. It is available from SSC.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Michael Leder]

Hi Clyde,

Awesome, thank you very much!!!!

I am sorry. I will definitely make use of it for my next question.