Home / Data / Now I Know Why I Don’t Use Data Tables

Now I Know Why I Don’t Use Data Tables

Excel Data Tables - Whiy I Don't Use Them

I spent some time working on how to use data tables with some question at work and I now know why I never used them before: the type of work I did before (and now) just don't lend themselves well to the formulation of data tables. Also, the people I'm working with don't necessarily ask "what-ifs" questions.

Data tables can have at most 2 varying variables. I was looking at a situation of limiting to one changing variable and even then, I had difficulty applying the data table. At least one of the output variables (the variables with answers you are looking for) must reference the input variable (the changing variable). The other output variables, if you have more than one, must at least indirectly reference the input variable but referencing the one of the other output variables.

Below, I've uploaded a file where I played around with data tables. This is not the one used for work; even though the formulas are probably commonly used in the industry, I prefer not to divulge the work. So I had to spend time looking for something to create and play around. I decided to do something on IQ and comparison of population of US, India, and China. Taking into consideration that IQ is regarded as a normal distribution, I wanted to see how the distribution of IQ played out in the population of US, India and China. 

The first tab "Data Table" contains the data table, the second tab "research" contains some research on population and normal distribution of IQ, as well as some calculations of future growth, and the remaining tabs are the actual data as well as links to the sources of information. You will notice that in the "Data Table" tab there are some errors. I believe that is because the data table does not work due to the way I laid out the inputs and outputs. I designated the changing variable to be the US job growth and that variable is regarded as the input in cell C4. The data table starts on row 16 where you see changing US job growth going down the column. Off to the right are various output variables that I was interested in; these output variables references the output variables found in cells G3 to J10. Row 16 has the formulas references the output variables and rows 17 to 26 are created by the data table. So in this particular example US job growth is set to zero but population growth is .71%. The data table then takes the changing US job growth in column B and figures out the US population, the number of population of those with IQ greater than 85, labor participation population, the unemployed population, and their corresponding percentages. Due to zero US job growth, I wanted to set the labor participation to remain the same as that of 2017, but that didn't work. 

Notice that the population growth remained the same which is because the US population growth is not the varying variable. I would have liked that to be varying too but then I would need to create a 2 variable data table and I think I would then be looking at answers for only one output variable.

I'm not sure why I get the error message on rows 21 to 26 because when I enter 2.5% in cell C4, the table updates with the input variable starting at 2.5% in cell B16 and the table spits out the answer for that percentage. The data table creates that answer though with the use of an array formula. There's some work that needs to be done.

Notice how large the Chinese and Indian populations are: they are about 4 times the size of the US population. Assuming that total job available is equal to the total labor participation (not true since there will be companies searching for new hires but let's just skip that because I don't have the data for that yet) of 1,915,862,476, the total Indian and Chinese population with IQ greater than or equal to 85 (2,312,496,401, found on row 46 of "research" tab) is greater than number of jobs available. Included in that IQ population will be children and the stay-at-homes so the comparison is not a great comparison, but still, the size of the Chinese and Indian population swamps all other countries. 

Sometime in the 21st century, at least one of the African countries will overtake China and India.

Now add in the AI revolution taking over jobs that humans do.

Leave a Reply

Your email address will not be published. Required fields are marked *