average and average???

rboyd_01

New Member
Joined
Dec 7, 2005
Messages
17
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???
 

Some videos you may like

Excel Facts

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

rboyd_01

New Member
Joined
Dec 7, 2005
Messages
17
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
Joined
Feb 16, 2002
Messages
8,458
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
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

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
Joined
Dec 7, 2005
Messages
17
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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
Joined
Dec 7, 2005
Messages
17
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,388
Messages
5,571,842
Members
412,420
Latest member
grace_abar
Top