A statistically weighted average question

  • Thread starter Thread starter Legacy 143009
  • Start date Start date
L

Legacy 143009

Guest
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!
 
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.
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Only this part is enough I guess:
Excel Formula:
{=SUMPRODUCT($A$1:A5,(1/ABS($A$1:A5-AVERAGE($A$1:A5))))/SUM(1/ABS($A$1:A5-AVERAGE($A$1:A5)))}
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,224,829
Messages
6,181,222
Members
453,024
Latest member
Wingit77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top