Mode > 10 fails when no mode is foud
Closed this issue · 1 comments
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;
- Revert the formula in the "Outlier?" column to "> Mode * 10"
- If the Mode is N/A, show nothing for the High case
- 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.
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", "")))), "")