• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Averageifs Confusion - An array solution maybe?

Tom Nice

New Member
Hi,

I have uploaded a file containing data on keywords - the month they were collected, their ranking position and keyword name. One table shows keywords collected twice per month (most of the time) and the other is keywords that have been collected 4.3 times per month (weekly).

I want to investigate the difference in ranking positions between each frequency of collection. My idea is to have an average for each month for each keyword i.e. Keyword10 April average = (5+6)/2, which could be put in an 'Average' column but couldn't figure out a way of doing this without manually calculating for 450 to 900 entries!

If anyone has any ideas that can make this work then I would welcome them massively!

Thank you,

Tom
 

Attachments

  • chandooProblem.xlsx
    30.6 KB · Views: 3
Hi,

You want to know what was the rating for Keyword1 for Month Feb when it was collected on Bi-Weekly Basis as compared to when collected on monthly basis??
 
Hi Faseeh,

Nope, the average ranking for each month for each keyword. So where there is an average column in each table, I wanted to know if it's possible to calculate an average of the rankings taken at different points during the month - the first table is biweekly so for Keyword1 in Feb the average will be (6+5)/2=5.5 as I put in the first cell of the average column. The second table would produce (7+5+5+6)/4 = 5.75 for Keyword1 in February. A bit longwinded but does this make sense?

I have uploaded a fresh file containing a date column to hopefully make it a bit clearer.

Cheers
 

Attachments

  • chandooProblem.xlsx
    38.3 KB · Views: 4
Ajesh, I was wondering if you knew how to get the minimum value as opposed to the average like you worked out before. My attached attempt didn't get very far :/ It'd be great to know your ideas. Cheers
 

Attachments

  • chandooProblem.xlsx
    62.4 KB · Views: 3
@Tom Nice

Never put 0 in FALSE argument of IF function if you are using in MIN function as than it will calculate 0, leave it blank as than a FALSE will come and MIN function ignores logical & text values.

Regards,
 
Back
Top