search

FocusedObjective / FocusedObjective.Resources

Various
706 stars 140 forks source link

Mode > 10 fails when no mode is foud #1

Closed FocusedObjective closed 10 years ago

FocusedObjective commented 10 years ago

In the data analysis spresdsheet, when samples entered have no explicit Mode, Excel causes formula errors in the Mode, Modes values. This also impacted the Outliers? column formula which was changed to show High when samples are in the top 5% rather than when value > 10 * Mode. This often happens when generating random numbers with decimal places. Since there is no duplicate values, there is no mode. Excel returns N/A and this breaks some formulas.

To do;

  1. Revert the formula in the "Outlier?" column to "> Mode * 10"
  2. If the Mode is N/A, show nothing for the High case
  3. Add a "Top 5%" outlier if statement to the formula.

The intention is a) Not show an error if there is no mode b) if there is no explicit mode to show "Top 5%" when appropriate to give some high outlier protection.

FocusedObjective commented 10 years ago

Fixed.

New Outlier formula: =IF(ISNUMBER(A2), IF((A2=0),"Zero", IF(A2 > PERCENTILE.INC(Samples,0.95), "Top 5%", IF(ISNA(Mode), "", IF(A2>Mode * 10,"High", "")))), "")