Adding Arrows to ranking chart

  • Re: Adding Arrows to ranking chart


    C header would be "Votos totales"
    Like this:


    C
    Votos
    totales


    and not


    C
    Votos totales


    The second option would be a column too long for seeing the ranking chart online (you'll know what I mean if you take a look at it in the Spanish forum).


    If it can't be that way, then "Total" would be ok. Like this:


    C
    Total


    For D, it would be: "Votos parciales".


    D
    Votos
    parciales


    Or, if it can't be that way,


    D
    Parcial


    thanks again.

  • Re: Adding Arrows to ranking chart


    ok, now the really cool thing would be to sort the movies automatically using a macro or a command. I normally select all the E column (average score) and then hit "sort" extending the selection and the "descending" mode.


    I see there's no shortcut to do this, so I guess a special command or module is needed, right?

  • Re: Adding Arrows to ranking chart


    Quote from cirerita

    wigi,
    one minor thing, though. after running andy's macros, the C and D headers are reset to "Cheader" and "Dheader".


    Sorry, mistake of mine. I did that in the code when I didn't know what these were going to be. After I knew, I forgot to adjust them. It's done now.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Adding Arrows to ranking chart


    The sort macro is also taken up. Access that macro in the same way as the other macros. See attached file pls.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Adding Arrows to ranking chart


    thanks, Wigi, I think that was it!


    you really rock, guys!


    I never thought possible that a very simple ranking chart would result in such a long thread! It's nice to find a helping hand once in a while.


    thanks agains and best from Spain...

  • Re: Adding Arrows to ranking chart


    I just noticed something that might be improved. It's a VERY minor thing, but it'd nice to implement it.


    When there are several movies with the same average score but different number of votes, I don't know which is the order Excel uses to sort the movies, but I think it's only fair that the first movie sorted should be the one with more votes.
    For example:


    Position - movie name - average - votes
    1 movie1 6 8 - 8
    2 movie2 6 7- 7- 4
    3 movie3 6 6 - 6 - 6 - 6 -6


    In this case, which I just made up, I think movie3 should go into movie1's position. Movie2 would remain in the same position.


    Curiously enough, sometimes Excel does this (probably unknowingly), but sometime don't.

  • Re: Adding Arrows to ranking chart


    Quote

    BTW movie 1 averages 9 not 6


    you're right. fixed!


    well, in a ranking chart I don't think you can "measure" quality. The quality is somehow implied in the votes given by the users. Since the chart is sorted using the highest-to-lowest average score factor, what would you do when several movies have the same average score?

  • Re: Adding Arrows to ranking chart


    sorry to be a pain but still movie1 don't average 6 (a 10-2 / 9-3 / 8-4 would be more appropriate).


    Nevertheless my point was: of course all three give the same average result, but is more valuable 1x 9 and 1x 3 or 2x 7 and 1x 4?


    Take as example the medal Table of the Olympic Games. A country with just one gold medal, comes before of one with 20 silver and 50 bronze. 1 to 70 but...
    The country 20x S 50x B comes before the country 19x S 100x B.


    Now withing the average group you may think to sort keeping in mind these order. Does it make any sense to you.


    Saludos


    filippo

  • Re: Adding Arrows to ranking chart


    Just as we sort on the first criterium (average), it is trivial to sort on 2 other criteria as well. But you have to decide what makes more sense in case of a tie: number of votes, or the maximum, or perhaps the spread around the mean (?), ...


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Adding Arrows to ranking chart


    ciao, filippo, sei italiano per caso???


    anyway, the Olympic simile doesn't apply here: you're talking of one medal against 70. I'm talking of 70 gold medals against 70 gold medals. Who wins then?


    Quote

    perhaps the spread around the mean


    what do you mean by that? which is the logical deciding factor when there's a tie? you'd think the movies with more votes, right?

  • Re: Adding Arrows to ranking chart


    About the similarity with the Olympic games, it's like comparing a bronze and a gold with 2 silver medals.


    Seems logical to sort by average, then how much votes. About the spread: you could argue that being constant (more or less) around a mean is better (or worse) than having the same average, but some times very high votes as well as some times very low votes. It's up to you...


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Adding Arrows to ranking chart


    yep, it seems unfair that a country with a single gold medal ranks better than another country with a zillion silver medals, but I still can't see the connection with my ranking chart!


    if the logic says first average and then number of votes, then I guess that's the best way to do it.


    just think of it: is it logical that a movie with a single vote ranks better than a movie with 25 votes (having both the same average)? I mean, it's easy to get that average with a single vote, whereas having that average with 25 votes seems more complicated.


    doesn't the goal average work like that?