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.