Adding Arrows to ranking chart

**Andy Pope** · Dec 15th 2005

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?

cirerita · Dec 15th 2005

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!

**Andy Pope** · Dec 16th 2005

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.

cirerita · Dec 16th 2005

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?

Wigi · Dec 16th 2005

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

cirerita · Dec 16th 2005

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?

Wigi · Dec 16th 2005

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

cirerita · Dec 16th 2005

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.

Wigi · Dec 16th 2005

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

cirerita · Dec 16th 2005

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

Wigi · Dec 16th 2005

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

cirerita · Dec 16th 2005

Re: Adding Arrows to ranking chart

thanks a lot.

there's a minor issue, though.

the "new" marker is no longer bold; movie 50 is bold indeed!

when adding a movie, there's no 51, but 50 again. weird, huh?

see attached file, please...

Wigi · Dec 16th 2005

Re: Adding Arrows to ranking chart

here you go with the corrected version

wigi

cirerita · Dec 16th 2005

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.

Wigi · Dec 16th 2005

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

cirerita · Dec 16th 2005

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?

cirerita · Dec 17th 2005

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

**Andy Pope** · Dec 17th 2005

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

cirerita · Dec 17th 2005

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

Wigi · Dec 17th 2005

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

Adding Arrows to ranking chart

Participate now!

Share

Users Viewing This Thread