extract single years from time spans / analyse membership data

%3CLINGO-SUB%20id%3D%22lingo-sub-2209761%22%20slang%3D%22en-US%22%3Eextract%20single%20years%20from%20time%20spans%20%2F%20analyse%20membership%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2209761%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20possibilities%20to%20analyse%20membership%20data.%3C%2FP%3E%3CP%3EThe%20aim%20is%20to%20analyse%20the%20development%20of%20the%20gender%20ratio%20among%20the%20years.%3C%2FP%3E%3CP%3EHowever%2C%20the%20data%20show%20only%20the%20entry%20and%20exit%20date%20(before%202015%20even%20only%20the%20years).%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20extract%20all%20the%20individual%20year%20from%20this%20kind%20of%20time%20spans%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%20for%20the%20further%20analysis%20are%20welcome!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance%20for%20any%20piece%20of%20advice!%3C%2FP%3E%3CP%3EBabsi%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2209761%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2209793%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20single%20years%20from%20time%20spans%20%2F%20analyse%20membership%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2209793%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F997087%22%20target%3D%22_blank%22%3E%40BabsiW%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2209841%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20single%20years%20from%20time%20spans%20%2F%20analyse%20membership%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2209841%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F997087%22%20target%3D%22_blank%22%3E%40BabsiW%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20proposed%20measure%20of%20%3CSTRONG%3E%3DF%2FM%3C%2FSTRONG%3E%20is%20an%20unusual%20choice.%26nbsp%3B%20The%20female%20numbers%20as%20a%20percentage%20of%20the%20total%20would%20be%20more%20easily%20understood.%26nbsp%3B%20Also%2C%20as%20the%20total%20numbers%20drop%2C%20the%20metrics%20become%20volatile%20and%20there%20is%20little%20meaning%20to%20be%20extracted%20from%20them.%26nbsp%3B%20I%20would%20suggest%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20IF(F%2BM%26gt%3B9%2C%20F%2F(F%2BM)%2C%20NA()%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eonce%20you%20have%20derived%20the%20numbers%20using%20COUNTIFS.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

I am looking for possibilities to analyse membership data.

The aim is to analyse the development of the gender ratio among the years.

However, the data show only the entry and exit date (before 2015 even only the years).

Is it possible to extract all the individual year from this kind of time spans?

 

Any suggestions for the further analysis are welcome!

 

Many thanks in advance for any piece of advice!

Babsi

4 Replies

@BabsiW 

See the attached version.

@BabsiW 

Your proposed measure of =F/M is an unusual choice.  The female numbers as a percentage of the total would be more easily understood.  Also, as the total numbers drop, the metrics become volatile and there is little meaning to be extracted from them.  I would suggest

= IF(F+M>9, F/(F+M), NA() )

once you have derived the numbers using COUNTIFS.

@Hans Vogelaar @Peter Bartholomew 

Hi !

Many thanks for your feedback and solutions!!

 

The table in the file is only a small subset.

I should have added that at the end there should be a report showing not only the development re gender among all members but also among the different groups (units/subunits)

Additionally, the report should also show how the distribution of the members among the countries changes (also for the total of all members but also among the different groups).

 

That’s why I thought to split the time range in single years and then use Pivot-tables/charts.

But maybe I getting this wrong in the beginning and should rearrange the original data?

 

Sorry, for these basic questions. I’m just (re)diving into the world of data analysis/Excel etc..

 

Your help is immensely appreciated!