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!

 

[offthelip]

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

 

[Legacy 143009]

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.

 

[jdellasala]

The Forum Search works very well for specific questions. I think this post should help!

 

[Legacy 143009]

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.

 

[jdellasala]

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
	A	B	C	D	E	F	G	H	I
1		Item 1	Item 2	Item 3	Item 4	Item 5		Max	Weight
2	Number	64	72	78	96	98		0	5
3	Weight	5	4	4	2	2		66	4
4	Weighted Avg	76.94%						80	2
5								100	0
Sheet1

Cell Formulas
Range	Formula
B3:F3	C3	=XLOOKUP(C2,$H$2:$H$5,$I$2:$I$5,,-1)
B4	B4	=SUMPRODUCT(B2:F2,B3:F3)/SUM(B3:F3)/100
I2:I5	I2	=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.

 

[Legacy 143009]

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...

{=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.

 

[jdellasala]

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...

{=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!!

 

[Legacy 143009]

"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?

 

[jdellasala]

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.