Adding Arrows to ranking chart

  • HI there,


    I'm a 100% newbie and I really don't know how to search the required info for this.


    Here's the thing: I'm using Excel to create a very simple chart where movies are ranked. So far so good. I update the info once a week and I order it according to the users' votes (in a Spanish forum). I wanted to improve the chart somehow and tried to use arrows (up arrow and down arrow) to show the movies' rank change during the week, but I'm doing that manually, that is, if a movie goes from 15 to 20, I use a down arrow to show the movie got bad votes during the last week. Of course, that's a really slow, tiresome method.


    So my question is pretty simple: is there any EASY command to tell Excel to put an arrow when a movie changes in the rank?


    I'm attaching an image of the rank so this is easier to visualize:
    [Blocked Image: http://img.photobucket.com/albums/v315/cirerita/completa.jpg]

  • Re: Is this possible?


    Howdy, and welcome to the board.


    No easy way to do this. I setup up a process similar to this using VBA. Basically your change table has to be on another sheet (to keep track of changes week to week). Then you can name each arrow consecutively (i.e., Arrow1, Arrow2, etc.), os that these can be linked to cells on the "change table". Then you can use a loop in VBA to cycle through each arrow, checking the appropriate cell on the "change table" and adjusting the arrow appropriately.


    In my project I had 73 arrows (three directions and 3 changing colors), and it takes 1-2 seconds. The person who uses it has not ever had it breakdown - and this is for a SVP and entire staff (400+) every week.


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

  • Re: Is this possible?


    thanks...


    oh my! you lost me there! is that the only possible way??? as you can imagine, this is not a professional thing and I'm doing it out of pure pleasure, so to speak. But learning VBA to just use the arrows is way too much, I think.


    I'm thinking out loud here:
    isn't there any simple command such as:


    if movieXXX is > then ↑
    if movieXXX is < then ↓
    if movieXXX is = then --

  • Re: Is this possible?


    Here is just a none VBA "thought"
    You could set the font of your columns to wingdings, find the arrow keys(and memorize) then all you would have to do is go through and press the needed key for the week. It wouldnt be automated but certainly quicker than trying to paste arrows in every column.

  • Re: Adding Arrows to ranking chart


    thanks for the tip on the wingdings fonts. I'll give a try!


    andy,
    I already attrached an image of the chart. That's it. The only command used in that sheet is the addition to get the average score. Then I use "order" (the votes column) and the movies get ordered from the highest to the lowest score.


    To determine the rank, colors are a good clue (when you reorder the movies, colors get mixed), but only within the same category, if you know what I mean (if you find an orange movie in the pink section you know the movie has gotten a lower score than the previous week).

  • Re: Adding Arrows to ranking chart


    You can use an nested IF formula.
    Assuming A1 contains current rank and A2 contains previous rank you can use the following [vba]=IF(A1<B1, "↑",IF(A1>B1,"↓","↔"))[/vba]And if you don't like those arrows use the Character Map program to see what arrows are available in other fonts.

  • Re: Adding Arrows to ranking chart


    thanks for the If formula, but I think it won't work the way the chart is right now.
    as you can see, there's only one rank for each movie, there are not a previous and a current rank (though I could add a new column before the column with the movie names).


    also, I double-clicked on one of the scores so you can see the only formula used in this sheet: average.


    here's the image:
    [Blocked Image: http://img200.imageshack.us/img200/6717/06867pc.jpg]

  • Re: Adding Arrows to ranking chart


    yep, promedio is average.


    I know the ranking has changed thanks to the colors (at least when they get mixed), otherwise, I can't.


    But I could add a new column and make it work that way, right? That is, A1 for previous rank, B1 for current rank and C1 for movie names. or would you do it differently?

  • Re: Adding Arrows to ranking chart


    What changes the colours? Maybe you could set the arrows at the same time the colours are changed.


    I would store the 2 ranks in either A and B and possible then hide those columns or use 2 columns off to the right somewhere.

  • Re: Adding Arrows to ranking chart


    The colours change when I hit "order" column C. Excel asks me if I want to extend the selection to B (movie names) and I say yes. The average column is reordered as well as column B (movie names). If an movie with an orange color gets poor votes, then it goes down to the pink section. That's how the colours change!!! I cannot apply that to arrows.


    How can you store both ranks in the SAME column, as you suggest?

  • Re: Adding Arrows to ranking chart


    So you can compare the current average score with the average of last times. That is excluding the latest score.


    I have assumed the latest score is in column D

  • Re: Adding Arrows to ranking chart


    IRRELEVANT AFTER PREVIOUS POST


    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 a bunch! it does work!


    well, column D was not the latest score, it's actually the first vote. The only score is column C... but it doesn't matter, the formula works a treat!!!


    well, the up and down arrows do work, but the other one, ↔, never appears.
    I changed your formula
    =IF(C1<MEAN(E1:N1); "↑";IF(C1>MEAN(E1:N1);"↓";"↔"))
    =IF(C1<MEAN(E1:N1); "↑";IF(C1>MEAN(E1:N1);"↓";IF(C1=MEAN(E1:N1);"↔")))


    but no luck... (I translated PROMEDIO as MEAN, but I don't know if that's the word which appear in the English version of Excel. Or is it AVERAGE?)

  • Re: Adding Arrows to ranking chart


    Hello,


    If I may intervene on this thread:


    The solution by Andy is fine. However, I have 2 comments:


    1. Column D is the column with the latest scores. Each time when new scores are inputted, cirerita needs to insert a new column D. But then, the average formulas are not correct anymore (wrong references to ranges to average)


    2. (Me being an economist) Think about what happens to the average of a series when a new number is added to the series. If the new number is higher (lower) than the average of the existing numbers, the new average will necessarily be higher (lower). Therefore, you just need to compare the latest score (in column D) with the existing average (columns E to ...).


    My solution, then, solving the 2 issues above, is put in attachment.


    Please have a look at it and tell me what you think of it.


    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


    Cirerita,


    - It is AVERAGE(), indeed.
    - Perhaps you can put the votes in the order as we thought was the case, D being the latest vote.
    - the arrow ↔ will in fact appear, but rounding may be at stake here for the averages. Check out that first.


    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


    Hi Wigi,


    Doesn't the use of <= prevent the ↔ from ever being true?
    [vba]=IF(D3>AVERAGE(INDIRECT(ADDRESS(ROW(),5) &":" &ADDRESS(ROW(),COLUMN($N3)))),"↑",
    IF(D3<=AVERAGE(INDIRECT(ADDRESS(ROW(),5) &":" &ADDRESS(ROW(),COLUMN($N3)))),"↓","↔"))[/vba]

  • Re: Adding Arrows to ranking chart


    thank you both!


    is column D actually needed as latest score? what for? to compare it with last week's score?


    I think the arrows already let you know if the movie is doing fine or getting poor votes. Besides, having the column D as the latest scores forces me to write down those scores every week, doesn't it? or can it be done automatically?

  • Re: Adding Arrows to ranking chart


    going crazy over here!


    If I keep column D as last week's scores, when I paste the new scores (from column C) into column D, the values from C change! (I think that was what Wigi was saying before).


    Couldn't I use the formula without last week's score? That is, column C would be current scores and column D first vote, not last week's scores?? Of course, keeping the arrows.


    or else, I wouldn't mind keep column D as last week's scores, but there should be a way to paste values from column C into column D without getting column C changed!!!


    by the way, arrows work wrongly! the higher the vote, I get a down arrow; the lower the vote, a I get an up arrow!!!