Formula help (averaging multiple columns)

I have this great Excel spreadsheet that pulls rankings from Google...but, i'm new to Excel and could use some help on the formulas...


The spreadsheet can be found here: http://www.searchenginepeople.com/wp...p-rankings.zip

Original article is here: http://www.searchenginepeople.com/bl...oft-excel.html)


What i would like to do is get the aggregate percentage increase (or decrease) for each day's rankings, and also the average of today's rankings over the first day's rankings.

Hopefully the attached graphic explains it better!

In other words, i want to know how much, overall, the rankings have increased or decreased (as a percentage). One is the amount of change vs. yesterday. The other is the amount of change vs. the first date.

Any help much appreciated.

Sure here it is!
Thanks a lot.

SERP Rankings.xls

Wow, very cool WenQQ3. Thanks for this help.

So let me clarify:

1) a "1" is actually better than a "10" because we're talking about rankings on Google. So, #1 is the top spot! (i say this because your 'aggregate %' is showing a negative on the 2nd day (and should be positive because all rankings on row 6 went up)

2) so on the first new column ("F") i want to know how much all my rankings on an aggregate level went up or down (percentage) verses the day before.

3) the second column ("G") should show aggregate % increase verses the very first day (row 5).

thank you so much for helping.

well, in column F we are saying "overall, your rankings were up/down this percent over yesterday's rankings". in column G we are saying "overall...your rankings are up/down this percent when comparing today's ranking with the first date we started collecting rankings (5/1/2010 on this spreadsheet, or Row 5)

does that help?

hi again Wenqq3, thanks a lot! i *think* there's only one issue left, and that is these values are negative. a '1' is better than a '10'. so maybe it's just a matter of "reversing the polarity" on those two columns?

in other words, if the value goes from "1" to "5", that's a 'decrease' in rankings.

I think this might be it! Let me work with it and I will let you know what i find out. Really nice of you to help me on this.

Hi Wenqq3, how would i implement what you did on to another spreadsheet that i already have? by the way, this spreadsheet has more columns (more keywords).

Thanks a lot for explaining that. I implemented it (and formatted cells for percentage) and it went pretty smooth! I will post back tomorrow after i run the next report using it, and let you know how it goes! I really, really appreciate your help on this, Wenqq3!

Works great! Thanks so much for the help on this!!

Not sure if i should start a different thread for this, but two things i'm hoping to do next on this spreadsheet are:

1) When a keyword is not found, it shows a zero (which is bad). i am wondering if i can have it simply be 'null' or blank and not effect the calculations.
2) When i run the macro, it's going 2 columns too far (the two columns which have your formulas). Anyway to fix that?

Eitherway, though, thanks a lot, this is already helping me!

Hi Wenqq3,

I am working from your latest version, so there's nothing different about my workbook.

If you run the macro and it doesn't find a rank for a certain keyword, it puts in a zero. I'm thinking this should either be simply a null value (so it doesn't change the averaging numbers you added) or perhaps a 100?

By the way, how hard would it be to add a 'trend line' to this that is based off of the 2nd averaging column (the one that averages against the very first day of rankings)?