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

 

[rboyd_01]

Sorry E19 is a cell that has the total number of surveys. Remeber the giant formula isn't referring to the exact cells, it is just an example of another formula I did for another chart. I'm trying to do the exact same thing though... Just without making it SOOOOOO long.

 

[Aladin Akyurek]

Sorry E19 is a cell that has the total number of surveys. Remeber the giant formula isn't referring to the exact cells, it is just an example of another formula I did for another chart. I'm trying to do the exact same thing though... Just without making it SOOOOOO long.

Does E19 house a formula that sums some range?

 

[rboyd_01]

yep E19 is the sum of E4-E17, which is the survey's returned. So in the case of the overall average of the averages, the total of the surveys returned is in O106.

 

[Aladin Akyurek]

yep E19 is the sum of E4-E17, which is the survey's returned. So in the case of the overall average of the averages, the total of the surveys returned is in O106.

I believe you should opt for not overfeeding a would-be helper for info s/he did not ask for...

Given that

=(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

works I'll venture:

=SUMPRODUCT(--ISNUMBER(MATCH(Raw!$AB$2:$AB$400,$D$4:$D$17,0)),Raw!$F$2:$F$400)/SUM($E$4:$E$17)

Adjust the ranges to suit, but note that whole columns are not allowed.

Does this produce the intended result?

 

[rboyd_01]

I have to have this overall average in the chart.... My boss wants it!
I tried that formula and it doesn't give me the right answer. I compared it against the answer that I got from the other (big) formula I used. It should have come out to 77%, but that formula gave me like 5%. So not even close. This seems to happen every time I try any other formula, they give me a very low percentage... not even close to what they should. Trust me, I wish I didn't have to put in this overall average. I dont even know why it's needed... but as we all know, you do what the boss tells ya to!

 

[rboyd_01]

Never mind... I ended up getting that formula you gave me to work!!! YAH!!! Thank you soooo much! I just had to change one of the cell references that I overlooked!