I am making reports in excel for an adwords adgroup report and I see that the columns that are averaged (no summed) at the bottom do not jive with the data. the columns in my case are CTR, Avg CPC, Avg Position, Conv Rate, cost/ conv. The easiest to explain would be the Avg Position column since there are no zero values in that column. If I average the data in that column, it does not match what google puts in the "totals and overall averages" row. In this case googles value is 3.4 and my value is 2.6 I have tried 2 ways to average the column and I get the same 2.6 answer I did the usual =average(j8:j385) formula and a more complicated one that checks for zero values =AVERAGE(IF(J8:J385>0,J8:J385)) I have (hopefully) attached a screenshot of a downloaded adgroup report that shows the problem. and i have added a line below the averages of the actual averages. But I am pretty sure google does not seem to actually average the values in the column, it is doing something completely different. Unfortunately this is no good since my charts will not be accurate when compared with google charts. Does anyone know about this, or perhaps have an explanation or a workaround?
I think I have figured it out. I have figured it out for the most part. Google does not calculate the average by using the column of numbers. In the case of CTR google is doing something I consider more accurate when considering the goal of CTR. Google takes the total clicks and divides by total impressions of all the rows. This will be vastly different than the average of the CTR's (especially if the are zero clicks in some rows e.g. when averaging with zeros the CTR is 2.44 instead of 4.6). I expect each column may be calculated in a slightly different way giving the most appropriate answer (not necessarily the most technically accurate). Google may also be calculating the answer for some columns without rounding, whereas the report offers many stats in a rounded format so it won't get as accurate in the excel sheet.