Download and open the file that demonstrates how I can create any one of 90 histograms at the press of the cursor using some simple and some relatively complex formulations.

The formulas you need to concentrate on are these:

  • N4=AND(AVERAGE(B4:L4)>MEDIAN(B4:L4),SKEW(B4:L4)>0)
  • O4 =IFERROR(SKEW(B4:L4),””)
  • Q4 =MAX(B4:L4)
  • R4 =MIN(B4:L4)
  • S4 =Q4-R4
  • U5 =VLOOKUP(U3,ratios,18,0)
  • U6 =U5+(VLOOKUP(U3,ratios,19,0)/4)
  • V5:V9 … array entered {=FREQUENCY(INDIRECT(CELL(“address”,INDEX(names,MATCH(U3,names,0),1))&”:”&CELL(“address”,INDEX(ratios,MATCH(U3,names,0),12))),U5:U9)}

The following image shows why I did this! U3 contains Data Validation that allows me to choose any of the 90 line items I am interested in choosing. The Class Boundaries and Frequencies show us the distribution of the line item chosen and the graph is the histogram of the frequencies for that line item.

If you already know histograms and distributions, this is not new … until you get to the array entered formula in the range V5:V9 … quite complex!

Here is a preview of the data we are working with:

Columns N and O contain the Positive Skewness Test I mentioned and you can see how I linked those outputs to the distribution and histogram outputs by means of the arrow to the right of the histogram!

In other words, I am mainly testing the line items for their degree of skew.

Download the file from here … netflix_compet_ft_b_small

 

All feedback greatly appreciated

 

Duncan Williamson

8th January 2019

%d bloggers like this: