Help for computing peer average

[ambily chacko]

I have a panel data of 80 companies and i need to compute the peer average of each company for 2011 to 2020. can any one help me

 

[vletm]

ambily chacko
You should able to send a sample Excel-file
with a sample data as well as with expected results
You've opened this to Discuss Data Visualizations and Charting ... what kind of output would You like to get?

 

[ambily chacko]

MY DATA IS MONTHLY DATA FOR 63 FIRMS FOR 10 YEARS..I NEED TO COMPUTE THE AVERAGE OF EACH FIRM IN A YEAR .i. e CONVERTING MONTLY TO YEARLY FOR THE WHOLE PANEL...IN THE FILE RESIDUAL VALUE FOR D2 TO D12 , THEN D13 TO D24 ETC ... PLZ HELP

 

[vletm]

ambily chacko
Please reread Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

PLEASE DON'T SHOUT! We have big ears and will hear you just the same.
= You've use only capital letters

Did You miss my question?
You've opened this to Discuss Data Visualizations and Charting
... what kind of output would You like to get?

 

[p45cal]

Similar to vletm's but since you seem to be wanting averages of financial years which begin Apr 1st, I've added a column (fYr) to your data which takes 3 months off each date, and created a pivot table using those dates. I've highlighted the 2 sample averages you wanted.

 

[ambily chacko]

MY DATA IS MONTHLY DATA FOR 63 FIRMS FOR 10 YEARS..I NEED TO COMPUTE THE AVERAGE OF EACH FIRM IN A YEAR .i. e CONVERTING MONTLY TO YEARLY FOR THE WHOLE PANEL...IN THE FILE RESIDUAL VALUE FOR D2 TO D12 , THEN D13 TO D24 ETC ... PLZ HELP
[/QUOTe
Thankyou so much sir...that was of much help
i apologize for breaking the rules even though i had no intention to do the same..

 

[ambily chacko]

Similar to vletm's but since you seem to be wanting averages of financial years which begin Apr 1st, I've added a column (fYr) to your data which takes 3 months off each date, and created a pivot table using those dates. I've highlighted the 2 sample averages you wanted.

Thankyou so much sir for the timely help..

 

[ambily chacko]

Thankyou so much sir for the timely help..

where the subscripts i, j, and t denote firm, industry, and year, respectively;

yijt is the dependent variable capturing firm i’s trade credit provision; ݕ

yhat ijt is the average trade receivables of TNIC-based peers, excluding firm i; ܺ

xbar-ijt are one-year lagged control variables; X-ijt are one-year lagged averages of the same control

In our regression analysis, we are mainly interested in the two coefficients ߚ and ߛᇱ

, which capture peer effects through peer firms’ actions and characteristics, respectively

how do we compute the peer averages excluding firm i?

i need peer average of firm excluding firm i for each variable

 

[p45cal]

You'll have to translate that for me; I'm having difficulty working out which columns of your attachment I should be looking at.
Also I see nothing about industry in your data - are they all the same industry?
Best manually calculate 10 or so results manually (do it for say 5 rows for 2 companies) with a bit of explanation of what you looked at to reach the values and attach that to your next message.

 

[ambily chacko]

sir,
iam analyzing 7 industry or sectors. the data variable uploaded are of a particular industry only
i.e there are 15 companies of oil industry, the data is for 10 year each for 15 companies.
for the first peer average of firm 1 for dependent variable 1- we need to average of all firms except firm 1(aban) i.e average excluding D3 to D12
for the second peer average- we need average of all firms except firm 2(Bpcl ) i.e average excluding D13 to D22

 

[vletm]

ambily chacko
Something like this?
You can do Your selections with Slicer.
> You've still skipped my #4 reply question <

 

[p45cal]

In the attached, two offerings, both based on the green shaded cells' formulae in columns N:W:
1. A pivot table at cell Z7
2. A single cell formula in cell Z27

Both agree with the manually calculated values highlighted in orange.

 

[ambily chacko]

In the attached, two offerings, both based on the green shaded cells' formulae in columns N:W:
1. A pivot table at cell Z7
2. A single cell formula in cell Z27

Both agree with the manually calculated values highlighted in orange.

Thank you so much for your timely help

 

[ambily chacko]

ambily chacko
Something like this?
You can do Your selections with Slicer.
> You've still skipped my #4 reply question <

Thank you sir