Trying to find outliers in data column with a bell curve

Hi,

The strange numbers simply mean that the number exeeds the maximum of 15 significant digits and can't display it in a decimal format so puts it into scientific format.

If you want a bell curve why not just plot a series that consists of the data points LESS 1 (or 2 depending on the confidence level you want) standard deviation, followed by the same data points PLUS 1 standard deviaton.

How much of this is statistics and how much is Excel? How are you intending to detect outliers? A few things that I am seeing.

I notice that both of your charts are not using anything for the xvalues -- which means that Excel defaults to simple counting numbers. I think your charts will make more sense if you instruct Excel to use column B for the x values rather than leaving the x values blank.

Also my curve looks nothing like a bell curve when I use true in the distribution formula

TRUE tells Excel to calculate the cumulative distribution function rather than the probability density function. Either is a perfectly good way of plotting a normal distribution. Wikipedia has charts that show both the probability density function (PDF) and the cumulative distribution function (CDF) for the normal distribution: https://en.wikipedia.org/wiki/Normal_distribution

You indicate that the goal is to identify outliers, but you have not indicated what algorithm you intend to use for outlier identification. If it's a statistics question ("I have no idea how to detect outliers in any programming language"), then I would recommend something like this that has a decent introduction to detecting outliers and a few of the common algorithms that are used: https://statisticsbyjim.com/basics/outliers/

Once you have chosen an algorithm, then help us understand your algorithm and we should be able to help you program that into the spreadsheet. Since you started by plotting a normal distribution PDF, that suggests that you maybe want to use something based on z-scores, so look at that section of the link.

Is your son taking a statistics course? For the purpose of this exercise, what is the course (teacher or textbook) saying about how to determine outliers?

Using Jim's suggestion of using z scores, Jim says

Originally Posted by statisticsbyjim

To calculate the Z-score for an observation, take the raw measurement, subtract the mean, and divide by the standard deviation. [Z=(X-mu)/s]...The further away an observation’s Z-score is from zero, the more unusual it is. A standard cut-off value for finding outliers are Z-scores of +/-3 or further from zero.

Using this procedure for detecting outliers, we could calculate a column of Z scores =(B2-$D$2)/$E$2 (maybe in G2 and copied down). Then look over that column and identify all of the Zscores that are <-3 or >+3.

It really depends on what procedure one wants to use to detect outliers.