How to sort data averages into bins

[nahgoe]

Hi

I am trying to sort data into average bins. As can be seen from the attached image I want to say for every time the value in column C is 2 (all the way up to 30) add the corresponding values in column B together and then give an average.

Any help would be greatly appreciated!

 

[kpark91]

Try this formula.

=SUMIF(C2:C30, "=2",B2:B30) / COUNTIF(C2:C30, "=2")

 

[nahgoe]

Hi

Thanks for the reply

In column C the values from 2 up to 30 and the range is C2 up to C25118

What am I trying to say is... if C2:C25118 = 2 (etc) then sum the corresponding values in column B together. I can't put the cell name in as I don't know when 2 is going to change to 3 and to 4 etc

 

[kpark91]

I can't put the cell name in as I don't know when 2 is going to change to 3 and to 4 etc

Hi, could you expand on this constraint a little more possibly with examples?
I'm having a bit of trouble understanding.

 

[nahgoe]

Sure

Basically I want to create a new table to graph with values of wind speed (column C) of 0-30 and their corresponding average of LVActivePower (column B). I need to do this for multiple different sheets so need a formula that I can use to do it.


WindSpeed_AVG LVActivePower_AVG
0 x
1 x
2 x
3 x
4 x
5 x
6 x
7 x
8 x
9 x
10 x
11 x
12 x
13 x
14 x
15 x
16 x
17 x
18 x
19 x
20 x
21 x
22 x
23 x
24 x
25 x
26 x
27 x
28 x
29 x
30 x

Thanks!

 

[kpark91]

If I understood correctly, you want to get an average of LVActivePower at a specified WindSpeed.

Put this in D1 and copy down.
=SUMIF($C$1:$C$30, "=" & C1, $B$1:$B$30) / COUNTIF($C$2:$C$30, "=" & C1)

 

[nahgoe]

Yes you have understood me correctly but unfortunately that's not working! See screenshot below

Thanks!

 

[kpark91]

What are you using as your formula?
It seems you might have changed it to your fitting.

 

[nahgoe]

Didn't change it at all! Just copied and pasted it into D. I manually inputed those values into G and H to show what I am looking for.

=SUMIF($C$1:$C$30, "=" & C1, $B$1:$B$30) / COUNTIF($C$2:$C$30, "=" & C1)

The range needs to be from C2:25118 as that is how many entries there are. So for instance I need something that will identify that from C2 to C17 wind speed is 2 so then add B2 through to B17 and then give an average of active power. Then from C18 down to C(whatever) is 3 so add B17 through to B(whatever) and then give an average of active power etc all the way up to 30

Thanks

 

[kpark91]

Hmm, it's weird because the value you're getting in D is the same as the cells in B.

Let's try this again:
Put this in D2 and copy down
=SUMIF($C$2:$C$25118, "=" & C2, $B$2:$B$25118) / COUNTIF($C$3:$C$25118, "=" & C2)

and set your column B,C,D as numbers