A statistically weighted average question

[Legacy 143009]

Hi all!

I am not an expert in statics and I need help from people who are more specialized in this topic.

Let's say I have 64, 72, 78, 96, and 98. I want to build an average system (most likely a formula) that will figure out its own weight without me manually predefine the weights. My aim is to output an average considering 2 factors: the count of numbers within a relative range and distance.

In my example, the numbers are populated between 64-78 mostly (3 elements). What I want is to have the effect of 96 and 98 less since the count of them is 2. But they are still close to the first group in terms of distance so I am expecting a result something close to 80's.

Another example would be to add a 2 to the beginning of the series. I want its effect lesser and lesser than the others since it is just a single element and it drops into a far range in comparison to the rest of the more frequent numbers' position.

I hope I am not speaking nonsense. I am sure this has an equivalent method in statistics science. I've googled mean, median and modulus concepts but I was not able to figure it out by myself. Hope to get an answer. Thank you very much!

 

[Legacy 143009]

I got the formula fine. Other than the fact that unless you plan to copy the formula elsewhere on the same worksheet, the absolute references are unnecessary.
I missed that you were looking for a Bell Curve. You might want to watch this video which has a sample workbook, and maybe this one, both from Mr. Excel.

Actually I wanted to find the peak point of a bell curve I guess. I will work for it and let you know if I can make it.

 

[Legacy 143009]

Only this part is enough I guess:

{=SUMPRODUCT($A$1:A5,(1/ABS($A$1:A5-AVERAGE($A$1:A5))))/SUM(1/ABS($A$1:A5-AVERAGE($A$1:A5)))}