average and average???

[rboyd_01]

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

 

[NBVC]

You mean?

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

 

[rboyd_01]

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]

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]

Can you post a sampling of data and expected result?

Edit:

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

 

[rboyd_01]

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

 

[Aladin Akyurek]

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

 

[NBVC]

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]

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.

 

[Aladin Akyurek]

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?