Excel WordPress Project - Top 3 Days - Summary Statistics

  • Hello everyone.


    I started a new Excel 2007 project for my new WordPress blog. My skills with Excel are average, I made some pretty snazzy spreadsheets in the past though. It's been over a year since my last project with Excel so I'm a bit rusty again and I find myself stuck on how to do something. As I said, my skills are average by that I mean I know nothing about macros/scripts/vba, nor arrays and I'm thinking arrays may just be the thing I need this time.


    If I may, here are three screenshots to show what I am working on. I don't know the forum protocol regarding screenshots and/or asking for help. I don't know if I should embed the images or just give you links to the screenshots. I don't mean to break any rules.


    EDIT: Hmm, for some reason it won't let me upload the three screenshots. They are small (lite-weight) files but I get the red exclamation mark after attempting to upload, they are .pngs. I will just give a link to them instead. I don't know why I can't upload them, I uploaded a screenshot in the past with no problem. /shrug


    The first two screenshots are what the project is supposed to accomplish. The first one is actual data, the second one is a mock up that I showed to the blogging community of what the end-result would be if I was successful in figuring this out.


    Stats page - actual data = http://sdrv.ms/1fNz6oW
    Summary page - mock up that I showed the blogging community (the look has actually changed since then) = http://sdrv.ms/1hlZlmP


    Here is a screenshot showing the steps on the worksheet and where I got stuck. http://sdrv.ms/IuGpE0


    Basically it's like this:

    • Step one: The bloggers are asked to set values to what are important to them such as do they value how many views they get on their blog post or do they value comments more than views and so on. Then they answer yes or no if they will be tracking the comments and likes they get on their blog posts. If they answer yes, the last section "comments and likes" are to be filled in and the spreadsheet will use Rating #1 to break any ties (more on that later). If they say no, the comments are greyed out (ignored) and the Ratings #2 will be used to break ties.
    • Step two: The Summary Data section you see in this last screenshot is pulled from the front worksheet named Stats; those are the actual stats of my new blog from a couple days ago. This summary data provides the data to answer the six questions above it such as "What are the top 3 days of the week I get the most views, most followers, most comments, etc" These questions can all be answered from that small section of Summary Data.
    • Step three: I have tried this so many different ways with such a messier worksheet than what is now there (I deleted it all) but basically step 3 here is just me making the first pass through the summary data. For each of the different categories "Followers, Views, Visitors, Likes, Comments, Posts" I found the top three best numbers for each.
    • Step four: I tried to break it down more. As you can see in step 4 of the screenshot, some numbers are shown but others are listed as "ties". Take for example "Followers" from step 3. My best day has 7 followers, then my next two best days both had 4 followers (a tie). But if you look in the summary data section you will see that I actually have three days where I had 4 followers. So I have a 3 way tie for 2nd and 3rd best days of gaining followers.
    • Step five: was supposed to break this tie. As seen in the summary data for followers I have three days with 4 followers, what step 5 was supposed to do is find those days that had 4 followers, then look to the Ratings #1 column, pick the top two highest scores, and use those two for my 2nd and 3rd best days. I have tried this numerous different ways, even with vlookups but that won't work. I can't use vlookups because my columns contain the same value more than once on many occasions, and the table cannot be sorted. So vlookups will never return the correct info (all the time). And this is where I am stuck because I can't pull the top two ratings numbers in order to break my "ties". I'm guessing, maybe, arrays is what I need to use? If so I have no clue how.
    • Step six: Assuming I was able to complete step 5, step 6 would finish me up and pop in the correct "days" and values for each of those days.


    I made a mock up section of step 5 and 6 so you could see what I was attempting to accomplish.


    Here is the link to my 32.8kb excel file named "Blog Stats.xlsx" I'll give link here and THIS actually uploaded so you may see attachment for it as well. http://sdrv.ms/IjWvQu


    Again I don't know what the rules are for this community, if I am out of bounds for typing such a long post, asking for help (am I supposed to pay?) and so forth. All I know is if I have to pay I cannot, I've been out of work for a couple of years now for health reasons. So just kick me in the butt and tell me to scram if that's the case.


    Thank you in advance for any help you can offer.


    I think what I need is probably pretty easy to do, seems it to me, I just don't know how. I mean you Excel professionals can really do some complicated stuff but after looking at my "six questions" and "Summary data" you will see what I'm needing is not that complicated.


    I hope you all had a pleasant Thanksgiving, mine was fine, overate too =)


    Have a great night and thanks again.


    Mark


    [ATTACH=CONFIG]57716[/ATTACH]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!