Excel Formula Help

[arvarr]

Hi
I need some help with excel formulas.
The inputs are the criteria to obtain the output.

Input
Year - 2014
Person - David
Industry - Agriculture, Forestry and Fishing

Output
# of Businesses - TBA

Thanks guys.

 

[zelgar]

Based upon you data in the spreadsheet, I'm not certain how you'd determine this. There doesn't appear to be any correlation between the data in Table 1 which contains business information, and Table 2 which contains data for each individual:

[pre] start entries exits end change %change entry% exit%
Table1 Agriculture, Forestry ... 187,539 15,675 19,463 183,751 -3,788 -2.0 8.4 10.4
Table2 David 688,403 96,143 87,621 696,925 8,522 1.2 14.0 12.7
[/pre]
Based upon your data, there are 20 types of industry and 9 people. The only correlation between the 2 data sets is that their annual totals are the same. Since each of the people didn't have the exact same percentages, it's evident that they don't have the same percentages of the different types of industries, therefore I don't know how you'd calculate what you want.

 

[SkipVought]

BTW, in addition to zelgar's observation, your tables are constructed to look pretty for visual consumption, but are virtually useless for analysis via formulas or other Excel analysis features. The year data must be in every row of the table!

 

[arvarr]

Hi Guys
Thanks for the feedback. I have inserted another column to bring the years down to every row.
Just trying to figure out the logic.
Yes, there are no correlation between the two - one is industry and the other is Person.
Therefore, I presumed that I will need to pro-rata the figures.
Thanks guys.

 

[SkipVought]

You need to define the logic for what aggregation you want.