Great question! I'm happy to help you with outlier analysis in Excel.

To calculate the mean of a range but not include or change values that are 2 or 3 standard deviations above or below the mean, you can use the following formula:

Here's how it works:

1. ABS(rng-AVERAGE(rng)) calculates the absolute difference between each value in the range and the mean of the range.
2. (2*STDEV(rng)) calculates twice the standard deviation of the range.
3. IF((ABS(rng-AVERAGE(rng)))(2*STDEV(rng)), "", rng) checks if the absolute difference between each value and the mean is greater than twice the standard deviation. If it is, the value is replaced with an empty string (""), otherwise the value is kept.
4. AVERAGE(IF((ABS(rng-AVERAGE(rng)))(2*STDEV(rng)), "", rng)) calculates the average of the remaining values.

To count the number of data points that were "removed" and report that as a percentage, you can use the following formula:

Here's how it works:

1. COUNTIF(rng,"<"&AVERAGE(rng)-2*STDEV(rng)) counts the number of values in the range that are less than 2 standard deviations below the mean.
2. COUNTIF(rng,""&AVERAGE(rng)+2*STDEV(rng)) counts the number of values in the range that are greater than 2 standard deviations above the mean.
3. COUNT(rng) counts the total number of values in the range.
4. (COUNTIF(rng,"<"&AVERAGE(rng)-2*STDEV(rng))+COUNTIF(rng,""&AVERAGE(rng)+2*STDEV (rng)))/COUNT(rng) calculates the percentage of values that were "removed".

To replace outliers with the 2 standard deviations above or below value, you can use the following formula:

Here's how it works:

1. IF(rng<AVERAGE(rng)-2*STDEV(rng),AVERAGE(rng)-2*STDEV(rng),IF(rngAVERAGE(rng)+2*STDEV(rng),AVER AGE(rng)+2*STDEV(rng),rng)) checks if each value in the range is less than 2 standard deviations below the mean or greater than 2 standard deviations above the mean. If it is, the value is replaced with the corresponding 2 standard deviations above or below value, otherwise the value is kept.

To replace outliers with the mean, you can use the following formula:

Here's how it works:

1. IF(rng<AVERAGE(rng)-2*STDEV(rng),AVERAGE(rng),IF(rngAVERAGE(rng)+2*ST DEV(rng),AVERAGE(rng),rng)) checks if each value in the range is less than 2 standard deviations below the mean or greater than 2 standard deviations above the mean. If it is, the value is replaced with the mean, otherwise the value is kept.