Adding Arrows to ranking chart

  • Re: Adding Arrows to ranking chart


    Quote from Andy Pope

    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]



    Of course, sorry a typo. Take, respectively, ..... > ........ < ......


    @ Cirerita:


    you can adjust the formulas if you want to type the votes in the other order, so the latest score at the end of the row. My suggestion was one way to do it. With many votes, columns could easily be long. So inserting a new column D could be better than. Do as you prefer (but change the formulas if needed)



    "having the column D as the latest scores forces me to write down those scores every week"


    That's correct. Perhaps then, add the votes at the end of each row (also in case you only have a few new votes), but this changes the formulas. However, the basic picture suggested by Andy and worked out by me, remains.


    Greetz,


    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



    Why do you want to copy C to D? C is just the average of existing votes, and sometimes you add a new vote. But you're right suggesting that the last vote should be at the end of a row. Or am I misinterpreting the structure of your worksheet?


    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


    ok, let's imagine a movie gets 4-5 high votes during the last 4-5 weeks. It'll get an up arrow. Let's say nobody votes that movie during the following 3-4 weeks. If somebody takes a look at the ranking they will see an up arrow even if the movie hasn't been voted during those 3-4 weeks...


    I know, maybe I'm asking way too much and I know Excel is not human, but could it be possible to add a standstill arrow ↔ in those cases????

  • Re: Adding Arrows to ranking chart


    no, take a look at this image (with your latest formula already applied).


    For example, take the movie "Hostage". It has up arrow even if it hasn't been voted as of lately, giving the false impression it's going up, when it's actually at a standstill. It was going up the last time it was voted, but it's been at a standstill for some time now.


    How would you solve that?
    [Blocked Image: http://img214.imageshack.us/img214/3698/06875yw.jpg]

  • Re: Adding Arrows to ranking chart


    You need to store, in another column, the fact that the film received no votes for this period.
    That way you can adjust the formula such that non voted for films always get the <-> marker.
    Films with votes then have the original formula applied to determine whether they went up, down or remained the same.

  • Re: Adding Arrows to ranking chart


    Quote

    You need to store, in another column, the fact that the film received no votes for this period.


    how? a different formula?

  • Re: Adding Arrows to ranking chart


    Cirerita,


    perhaps you should insert column headings in row 1, as Andy did in his file. Then you know how many columns / votes are being used maximally (just counting the number of headers if each column corresponds to 1 moment of votes). If a movie has no vote in the last column, you know that is thas to be ↔ arrow. If it has, you should evaluate whether it is or ↔ or ↑ or ↓. Use COUNT() formula, as well as ADDRESS(), INDIRECT, ROW() and COLUMN(), but start from the existing formulas in your sheet.


    If you're not following anymore, wait until me or someone else posts the formulas.


    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


    I have inserted a new column A which contains a 1 if the film received a vote in the lasted set of figures or 0 if not.
    So the revised formula would be[vba]=IF(A2=1,
    IF(D2>AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN()+1)&":"&ADDRESS(ROW(),COUNTA(E2:O2)+2))), "↑",
    IF(D2<AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN()+1)&":"&ADDRESS(ROW(),COUNTA(E2:O2)+2))),"↓","↔")),
    "↔")[/vba]

  • Re: Adding Arrows to ranking chart


    Hi,


    I think Andy's formulas can be replaced with the following non-volatile formulas..


    In D2 and copy down,


    =AVERAGE(E2:INDEX(E2:IV2,MATCH(9E+99+307,E2:IV2)))


    In B2 and copy down,


    =IF(A2=1,IF(D2>AVERAGE(E2:INDEX(E2:IV2,MATCH(9.999E+307,E2:IV2)-1)),"↑",IF(D2<AVERAGE(E2:INDEX(E2:IV2,MATCH(9.999E+307,E2:IV2)-1)),"↓","↔")),"↔")

  • Re: Adding Arrows to ranking chart


    Hello Krishnakumar,


    just interested in your formula:


    where does the 9E+99+307 come from? I understand you're just looking for the number of votes in each row and that you use a MATCH(), but why 9E+99+307? Especially the 307.


    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



    Then you're just having luck as the 307 doesn't change things over here.


    BTW, why don't we make the A-column with a formula (or alternatively, in the formula in the B-column, replace A2=1, by a formula). This in the spirit of Krishnakumar's suggestion above.


    Something like this, in B2:


    Code
    =IF(IF(MATCH(9.999E+307,E2:IV2)=COUNTA($E$1:$IV$1),1,0)=1,IF(D2>AVERAGE(E2:INDEX(E2:IV2,MATCH(9.999E+307,E2:IV2)-1)),"↑",IF(D2<AVERAGE(E2:INDEX(E2:IV2,MATCH(9.999E+307,E2:IV2)-1)),"↓","↔")),"↔")


    and copied down


    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,



    May be...


    Delete Col A,


    In A2 (earlier B2),


    =IF(MATCH("zzzzzzzzz",$D$1:$IV$1)=MATCH(9.9999E+307,D2:IV2),IF(C2>AVERAGE(D2:INDEX(D2:IV2,MATCH(9.999E+307,D2:IV2)-1)),"↑",IF(C2<AVERAGE(D2:INDEX(D2:IV2,MATCH(9.999E+307,D2:IV2)-1)),"↓","↔")),"↔")


    HTH

  • Re: Adding Arrows to ranking chart


    I understand the concepts being discussed here, but I can't follow the formulas. Which one do you suggest to use, the last one by Andy or Krishnakumar's?


    wigi,
    if I add a new row (as Andy did) then the numbers on the left for the movies would begin with number 2 instead of number 1 (used for the new row containing the columns description).


    any solution as to that?


    I'm attaching the ranking chart I use so you can paste the formulas there!!! Please use sheet1 (current movies) as sheet2 is where I store the old movies (deleted from sheet1).

  • Re: Adding Arrows to ranking chart


    See attached. I kinda summarized the discussion with the formulas in the sheet.


    Good luck with 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


    wigi,


    thanks, but it's not what I intended to do. It's not your fault, though.


    Columns D-IV2 don't represent weeks, but votes. That is, if King Kong gets 5 votes, I use columns D, E, F, G and H for those 5 votes. The average of those votes appears in column C.


    Ok, next week King Kong gets another 5 votes. I use columns I, J, K, L, and M for those votes. Column C shows the average of those 5 votes plust last week's votes.


    Column A, thanks to you all, shows if the past votes have been positive, negative or just average.


    Accordingly, the arrows don't work now. Most of them remain ↔ no matter how many votes I add (just try adding several 10 and 9 to any movie with 2-3 votes and you'll see what I mean). With one of Andy's formulas, the arrows changed whenever you added a vote. Now they don't!


    I wanted to use the ↔ to show a movie hadn't had any votes recently, but not specifying the number of weeks involved.


    Can we do this using columns D-IV2 for votes and not for weeks????


    If column D must be used to show last week's average (so we can compare C & D) that's fine with me, but I'd rather not do it that way because then I have to paste C's data into D column every week to keep it updated.

Participate now!

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