I have the following salary data for example:
Country State 2012 2013 -> 2027
======= ===== ==== ====
China Other 1000 1100
China Shanghai 1310 1400
China Tianjin 1450 1500
India Orissa 1500 1600
So now in another Excel sheet I would want an answer to one of the following questions:
- What is the salary in Shanghai for 2013? (Answer would be 1400)
- What is the salary in Hubei province for 2012? (Since it is not listed, use "Other" - 1000)
- What is the average salary in China for 2013? (Answer would be 1333)
- What is the highest salary in China for 2012? (Answer is Tianjin)
So as in the above order of priority, I would like those numbers in another Excel sheet using some form of query. I considered PivotTables but I was wondering if there is another much better more efficient way of doing this?
I imagine SQL is suited for this but I am not clued up on that. Some Excel functionality is much rather preferred. Also suggestions on an appropriate format of data for such queries would be appreciated.
Country
,State
,Year
,Salary
. Then make it into a pivot table and useGETPIVOTDATA()
to pull the data you want.