Adding Arrows to ranking chart

  • Re: Adding Arrows to ranking chart


    I hate flashy cells. By all means draw my attention with colour or font attributes but don't flash me.


    Which column currently contains the ranking number? I thought you were meaning you currently just use the row numbers as the rank.


    Ok how do you know which colour to make each film?

  • Re: Adding Arrows to ranking chart


    do you consider "flashy" the animated gifs I posted before? I don't find them too gaudy.


    yes, you're right, I use the row numbers as the rank. What I meant is that if I add a new row (above) for describing the columns (below) then the by-default row numbers wouldn't match the movies, and I should add a new column with the right numbers or use the formula you just posted (this is my guess).


    Quote

    Ok how do you know which colour to make each film?


    I don't know what you mean exactly. I use red for movies above 9 (or for the first movie if there's none above 9), orange for movies between 8-9, pink for movies between 6-8, dark yellow for movies between 5-6 and light yellow for movies below 5. I do this manually. That's why when I sort movies using the highest-to-lowest factor some of the colours get mixed up: let's say a movie with a 5,5 average score (dark yellow) gets 5 poor votes and when I update the ranking the new average is 4,5. Then I sort the movies and that movie appears in the LIGHT yellow section with a DARK yellow color. So I have to manually set that movie to LIGHT yellow.


    As I said before, this ranking chart was extremely simple... before I posted in this forum, of course!

  • Re: Adding Arrows to ranking chart


    So after you sort run a colour routine.[vba]Sub ColorFilms()
    'I use red for movies above 9
    '(or for the first movie if there's none above 9),
    ' orange for movies between 8-9,
    ' pink for movies between 6-8,
    ' dark yellow for movies between 5-6
    ' and light yellow for movies below 5.

    Dim sngMaxVote As Single
    Dim rngFilms As Range
    Dim rngFilm As Range
    Dim sngVote As Single
    Dim lngColor As Long

    With ActiveSheet
    Set rngFilms = .Range("B1:B" & .Range("B1").End(xlDown).Row)
    sngMaxVote = Application.WorksheetFunction.Max(rngFilms.Offset(0, 3))

    If sngMaxVote < 9 Then
    rngFilms.Offset(0, 0).Cells(1, 1).Interior.ColorIndex = 3
    ' exclude 1st film
    Set rngFilms = .Range("B2:B" & .Range("B1").End(xlDown).Row)
    End If
    For Each rngFilm In rngFilms
    sngVote = rngFilm.Offset(0, 3).Value
    If sngVote > 9 Then
    ' red
    lngColor = 3
    ElseIf sngVote >= 8 Then
    ' orange
    lngColor = 46
    ElseIf sngVote >= 6 Then
    ' pink
    lngColor = 38
    ElseIf sngVote > 5 Then
    ' dark yellow
    lngColor = 44
    ElseIf sngVote > 0 Then
    ' light yellow
    lngColor = 6
    End If
    rngFilm.Interior.ColorIndex = lngColor
    Next
    End With

    End Sub[/vba]

    Quote

    do you consider "flashy" the animated gifs I posted before? I don't find them too gaudy.

    For an excel worksheet yes I do.

  • Re: Adding Arrows to ranking chart


    thanks a lot.


    as I said before, VBA is not my thing, in fact I've never ever used it!


    how do I embed that VBA formula within my sheet? as a macro?


    also, what should I modify in your formula so the "new" marker appears in bold?

  • Re: Adding Arrows to ranking chart


    Quote

    how do I embed that VBA formula within my sheet? as a macro?


    copy the code into a new module. Go to VBA by pressing Alt-F11, then right-click on VBAProject, then take Insert, then Module. Päste the code in between the 2 lines of text given. Running that code (that Sub) can be done by choosing the name of that sub in the Excel menu: Tools > Macro > Macro's , then choosing the right name of the sub and executing.


    Quote

    also, what should I modify in your formula so the "new" marker appears in bold?


    Take conditional formatting. Select all the cells you used in column A, choose Format > Conditional Formatting, then put as first condition: CELL VALUE equal to ="NEW". The format to be applied is just a bold format.


    Regards
    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, both things do work, the VBA module and the bold format.


    just one more thing (ok, you can kill me now :rambo: )


    if I add a new row header (row number 1 to describe columns belows) the numbers on the left (the ones which appear by default when you open an Excel sheet) don't matcht the movie names; that is, the first movie would have number 2 instead of number 1.


    andy suggested two solutions for this, one adding a new column with the right numbering and other adding something to the formula, but I quite didn't get that part.


    any idea?

  • Re: Adding Arrows to ranking chart


    I suggest to do a couple of things, after you have inserted a new row with the headers:


    1. add to the formula in cell A1 (somewhat artificially, I admit):


    Code
    &"       "&ROW(B2)-1&"."


    change to your own preferences if it's not optimal to you


    2. fill the formula in A1 down to all the movies


    3. about the conditional formatting for the NEW marker in bold: that changes of course. Change the conditional formatting to: (i.e. select all used cells in column A, starting at row 2)


    Code
    =AND(E2=AVERAGE(F2:OFFSET(F2,0,D2-1)),D2=0)


    4. It COULD be that by inserting the row with the column headers, your VBA code will not function as expected. Let's ask Andy to determine if there need to be changes in the code, and if so, where.


    regards


    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


    Quote

    somewhat artificially, I admit):


    Quote

    It COULD be that by inserting the row with the column headers, your VBA code will not function as expected


    well, right now it's working very well, so I don't feel comfortable adding your suggestions because I'm sure I'll mess everything up!


    I'm uploading Andy's latest formula plus the module I just added and the "new" marker added to formulas in column A.

  • Re: Adding Arrows to ranking chart


    Quote from cirerita

    well, right now it's working very well, so I don't feel comfortable adding your suggestions because I'm sure I'll mess everything up!


    Then why did you ask for these modifications???

    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


    because I think they will improve the ranking charter, but I'm a total newbie and using formulas I've never seen before and I'm not familiar with kind of scare me, so to speak. adding a new module, as you kindly explained to me before, is one thing, and re-writing or modifyng an existing formula is an altogether different one, at least from my point of view.


    all I'm saying is that if you feel like trying a new formula (even if you're not 100% sure it's gonna work), go ahead and modify my sheet. I'll kindly test it!! :) :) :) :)

  • Re: Adding Arrows to ranking chart


    I applied my own suggestions from 2 posts above. Marker NEW is bold, and rankings run from 1 to 50.


    Formulas and VBA code needed to be changed, I did that as well. The result is attached.


    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


    yessssss! thanks again!


    one final thing. In column A, how can I put the number first and then the arrow? I tried to move the end part of the formula to the beginning, but I get an error.

  • Re: Adding Arrows to ranking chart


    see attach. In the formulas in cell A, & is just used to concatenate 2 subpieces of a formula. You tie 2 pieces together 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


    a big thank you!


    I think that's all... unless you want to suggest something to improve the chart.


    maybe hiding column D? column C is ok to show all the votes ever, but column D is not that useful for voters. Do I have to move it someplace else within the sheet to hide it? which is the standard way to do it?

  • Re: Adding Arrows to ranking chart


    procedure is no longer working!!!


    when I add new movies, I never get the "new" marker, instead I get arrows (they're the right arrows, but there should be a "new" marker instead).


    there's something weird, though. when I extend the A and E formulas to the new movies (after running the countprevious votes macro), I get arrows automatically instead of #¡DIV/0!


    also, if I write some text in the C and D column header, the text disappears when I run the macros!!!


    please see attached file with two new movies added getting arrows instead of the "new" marker ( I followed the procedure explained below the ranking chart).

  • Re: Adding Arrows to ranking chart


    You need to shuffle the formula a bit in order for the NEW test to work before averages.[vba]=" "&IF(D2=0,"NEW",
    IF(E2>AVERAGE(F2:OFFSET(F2,0,D2-1)),"↑",
    IF(E2<AVERAGE(F2:OFFSET(F2,0,D2-1)),"↓","↔")))
    &" "&ROW(B2)-1&"."[/vba]Also the conditional formatting can be based just on D2 being empty.[vba]=D2=0[/vba]As for the 2 helper columns C and D you can just hide them. Select both columns and right click to choose Hide Columns from popup menu

  • Re: Adding Arrows to ranking chart


    andy,


    I tried to copy and paste your modified formula into my sheet, but there's a language issue and it's not working. I think the English version uses commas, semicolons and quotation marks in a different way. However, if I open an English version the Spanish one translates everything "on the fly". But that doesn't work if you try to copy or paste the info.Would you be as kind as to paste the previous formula into the attached file????????? Please note the formula in the A cell is slighltly different from the one you just posted. It's a modification made by Wigi so there's a new numbering along with the arrows.


    thanks for the tipo on how to hide columns.


    as to the conditional formatting tip, that I didn't get. Is is to get the marker in bold?????

  • Re: Adding Arrows to ranking chart


    Quote from cirerita

    also, if I write some text in the C and D column header, the text disappears when I run the macros!!!



    What text would you like to have in these column headers? Let us know and I'll adapt the code a tiny bit.


    The other issues are also resolved, I'll upload the sheet when you give the C and D headers (don't have to do it twice).


    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) --

Participate now!

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