Standard Deviation and weighted average of a subset with duplicate values

vjayf1

New Member
Joined
Jan 25, 2018
Messages
1
Hi, I have a data set which has subsets having duplicate values like this:

CustomerScore
10.253
10.253
10.253
10.356
10.356
20.245
20.245
20.378
30.425
30.425
30.235
30.235
30.39
30.38
30.39
40.45
40.45
40.51
40.52

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

I used the formula =STDEV(IF($A$2:$A$34894=A2,$B$2:$B$34894)). But it gives me the same standard deviation value for the entire set.

Can someone please help me with an appropriate formula?
Also, if i have weights for each individual row, is there a formula that can give me a weighted average for each subset?

Thanks in advance!
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
Your formula is an array formula and must be entered with CTRL-SHIFT-ENTER.

Put the cursor in the cell with the formula.
-press F2 to edit
-then CTRL-SHIFT-ENTER.
Excel Workbook
ABCD
1CustomerScore
210.2530.093729
310.2530.056415
410.253
510.356
610.356
720.245
820.245
920.378
1030.425
1130.425
1230.235
1330.235
1430.39
1530.38
1630.39
1740.45
1840.45
1940.51
2040.52
Sheet
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,885
Members
413,947
Latest member
gizmolucy

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
Top