• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Latest year with available data in a dataset

Mariana1234

New Member
Hi,

I have a dataset for several countries, from 1990 to 2014, with several variables. The latest year for which data are available varies according to the country and variable. The data is attached for you to see the format (it is in list/database format).

I would like to know how to create a variable that gives me the latest data point for which data are available, and a second variable with the year to which that data point refers to. I managed to do so with a lookup formula when the years are in columns (rather than in rows), do not manage to get it to work with the data as a list.

This would be useful as I use pivot tables, and would like to avoid the year to be one of the dimensions in the pivot. For example, I am analysing the Greenhouse Gas emissions per sector (energy, transport, etc), and per country. I would like to be able to have the countries in rows in the pivot, the sector as columns, and the variable "latest value available" in Values (since for some countries data are not available for 2014 for example, filtering for the latest year does not work).

Any help would be much appreciated! Happy to provide clarifications if needed.

many thanks!

Mariana
 

Attachments

  • upload_2017-3-23_11-29-40.png
    upload_2017-3-23_11-29-40.png
    32.9 KB · Views: 12
  • data.xlsx
    295.3 KB · Views: 12
  • data.xlsx
    295.3 KB · Views: 3
Hi,

not sure I understand your answer. I would need to create a variable that provides me the data for the latest year available for different countries. The image you shared looks like a pivot filtered for 2014? If this is the case, if I add, for example, China, I would have empty values for them, as the latest data point available is 2010 for example.

Thanks!

M.
 
@Mariana1234
That snapshot presents ONLY the 1st Country -Australia.
The rest of Countries would be below of Australia.
Add depends of Your data-file.
 
Hi,

but how do you calculate the variable with the data for the latest year? Great if you could attach an excel with the formula, would be easier to see.

Thanks again!
 
@Mariana1234
Okay, You have two possibilities
a) press that [Do It]-button and get results
b) make formulas as I have done 'few' ... and much much later, You'll see results
 

Attachments

  • data(1).xlsb
    177.4 KB · Views: 3
Back
Top