A statistically weighted average question

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!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Have a look at the frequency function it sounds like that could be a step on the way to getting what you are looking for
 
Upvote 0
Yes, I thought the same but I don't know how to relate it with my problem. Also it needs bin data. I am looking for something which will figure out its own frequent regions.
 
Last edited by a moderator:
Upvote 0
The Forum Search works very well for specific questions. I think this post should help!
This is not what I am looking for unfortunately. In this method you have to predefine weights (significance).

What I am looking for is a bell curve I guess? Is it? Some one have to approve.
 
Upvote 0
This is not what I am looking for unfortunately. In this method you have to predefine weights (significance).

What I am looking for is a bell curve I guess? Is it? Some one have to approve.
Using what you indicated, how's this?
Book2
ABCDEFGHI
1Item 1Item 2Item 3Item 4Item 5MaxWeight
2Number647278969805
3Weight54422664
4Weighted Avg76.94%802
51000
Sheet1
Cell Formulas
RangeFormula
B3:F3C3=XLOOKUP(C2,$H$2:$H$5,$I$2:$I$5,,-1)
B4B4=SUMPRODUCT(B2:F2,B3:F3)/SUM(B3:F3)/100
I2:I5I2=COUNTIFS($B$2:$F$2,">"&H2)

You should watch this (13 year old!) video. It may give you a better idea of how to determine the weights.
 
Last edited:
Upvote 0
Here, I came up my own formula. I think I need something like this but I am still not sure. A voice inside me says I must have been used Median and Std.Dev. We'll see...
Excel Formula:
{=SUMPRODUCT($A$1:$A$5,(1/ABS($A$1:$A$5-AVERAGE($A$1:$A$5))/SUM(1/ABS($A$1:$A$5-AVERAGE($A$1:$A$5)))))/SUM(1/ABS($A$1:$A$5-AVERAGE($A$1:$A$5))/SUM(1/ABS($A$1:$A$5-AVERAGE($A$1:$A$5))))}
Thanks for your helps. They gave me quite an idea.
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.
BTW, my first example was wrong since the mean of my numbers is 81 which already falls in 80's. It could be maybe the end of 70's. My formula gives me 79 which seems to be what I want. When I prepend 2 to the beginning, it drops to 78. Maybe the effect of 2 could be less but it is the best I can do until now.
 
Last edited by a moderator:
Upvote 0
Here, I came up my own formula. I think I need something like this but I am still not sure. A voice inside me says I must have been used Median and Std.Dev. We'll see...
Excel Formula:
{=SUMPRODUCT($A$1:$A$5,(1/ABS($A$1:$A$5-AVERAGE($A$1:$A$5))/SUM(1/ABS($A$1:$A$5-AVERAGE($A$1:$A$5)))))/SUM(1/ABS($A$1:$A$5-AVERAGE($A$1:$A$5))/SUM(1/ABS($A$1:$A$5-AVERAGE($A$1:$A$5))))}
Thanks for your helps. They gave me quite an idea.
"ORTALAMA"?? LOL. That generated a #NAME? error when copying the formula above, but for whatever reason the formula is fine in this reply! Nice work!!
 
Upvote 0
"ORTALAMA"?? LOL. That generated a #NAME? error when copying the formula above, but for whatever reason the formula is fine in this reply! Nice work!!
It is in my own language. Sorry 😬 I fixed it! Can you try it again and send your feedbacks please?
 
Upvote 0
It is in my own language. Sorry 😬 I fixed it! Can you try it again and send your feedbacks please?
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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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