# average and average???

#### rboyd_01

##### New Member
I have a column that is the average of a block of information. But I need the actual average of that. I can't just average the average, becuase it doesn't give the right answer.
My origional formula is SUMIF(Raw!\$AB:\$AB,\$N104,Raw!\$D:\$D)/\$O104
this gives me the first average. This is in column P. I need to get the average of that column
What do I do???

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### NBVC

##### Well-known Member
You mean?

=SUM(P:P)/COUNTA(P:P)

#### rboyd_01

##### New Member
No, the origional formula gives me the right average. This is copied all the way down column P.
I need one to get me the average of that column. Without using Average to get it, as this doesn't give me the correct answer.

#### Brian from Maui

##### MrExcel MVP
rboyd_01 said:
I have a column that is the average of a block of information. But I need the actual average of that. I can't just average the average, becuase it doesn't give the right answer.
My origional formula is SUMIF(Raw!\$AB:\$AB,\$N104,Raw!\$D:\$D)/\$O104
this gives me the first average. This is in column P. I need to get the average of that column
What do I do???

An array formula,

=AVERAGE(IF(Raw!\$AB2:\$AB100=\$N104,Raw!\$D2:\$\$D100))

No whole column references

#### NBVC

##### Well-known Member

Can you post a sampling of data and expected result?

Edit:

That is, if the other offered suggestion doesn't work?

#### rboyd_01

##### New Member
I don't need the formula I gave you to be fixed. It gives me the right answer. I need another formula to give me the average of all the averages in the column. I only added the formula to show what I'm working with to make another formula

##### MrExcel MVP

What are the exact ranges in AB:AB, D:D on Raw, and in P:P?

#### NBVC

##### Well-known Member
rboyd_01 said:
I don't need the formula I gave you to be fixed. It gives me the right answer. I need another formula to give me the average of all the averages in the column. I only added the formula to show what I'm working with to make another formula

Isn't that what I gave you originally? Did you try it?

#### rboyd_01

##### New Member
The ranges change as I add new data. I don't want to have to update all the ranges all of the time. As for that array formula, The only problem is that I need it to calculate the average if AB:AB equals N104,N105, N106... etc. I have a formula that works to get this, but it's a royal pain in the butt to do. Here's another one I did to get the overall average. But that one only had 13 lines of info... unlike this one that has like 100 lines of info.

=(SUMIF(Raw!\$AB:\$AB,\$D4,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D5,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D6,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D7,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D8,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D9,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D10,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D11,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D12,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D13,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D14,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D15,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D16,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D17,Raw!\$F:\$F))/\$E19

Basically what this chart is doing is looking for the average positive responses to a survey. I have replaced the values I want with 1 and 0. So it takes each person, looks for their name in Raw! AB, and adds up all of the surveys that are returned to that person. Also it gives the average of positive returns (column Raw! D).
The total number of surveys is put into column O, and the average positive returns is put into column P. To get this initial average I used the formula =COUNTIF(Raw!\$AB:\$AB,N4) (N being the column with the person's name in to reference to the other chart) This is copied all the way down. so N4 changes to N5 and so on.

I need a way to get this overall average without using a huge formula similar to the one I entered above.

##### MrExcel MVP
rboyd_01 said:
The ranges change as I add new data. I don't want to have to update all the ranges all of the time. As for that array formula, The only problem is that I need it to calculate the average if AB:AB equals N104,N105, N106... etc. I have a formula that works to get this, but it's a royal pain in the butt to do. Here's another one I did to get the overall average. But that one only had 13 lines of info... unlike this one that has like 100 lines of info.

=(SUMIF(Raw!\$AB:\$AB,\$D4,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D5,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D6,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D7,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D8,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D9,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D10,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D11,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D12,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D13,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D14,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D15,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D16,Raw!\$F:\$F)+SUMIF(Raw!\$AB:\$AB,\$D17,Raw!\$F:\$F))/\$E19

Basically what this chart is doing is looking for the average positive responses to a survey. I have replaced the values I want with 1 and 0. So it takes each person, looks for their name in Raw! AB, and adds up all of the surveys that are returned to that person. Also it gives the average of positive returns (column Raw! D).
The total number of surveys is put into column O, and the average positive returns is put into column P. To get this initial average I used the formula =COUNTIF(Raw!\$AB:\$AB,N4) (N being the column with the person's name in to reference to the other chart) This is copied all the way down. so N4 changes to N5 and so on.

I need a way to get this overall average without using a huge formula similar to the one I entered above.

And what is in E19?

Replies
5
Views
94
Replies
3
Views
94
Replies
1
Views
119
Replies
0
Views
113
Replies
1
Views
136