Posts by cirerita

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?

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

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

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

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

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

wigi,

thanks a lot.

everything works nice & easy!

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

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

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

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

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

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

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

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

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

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

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

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

yes, I think I'll add the column header but I'll probably keep the counting columns, or at least the total votes column (column C). How can I make D invisible? Or is it better to place it somewhere else in the sheet?

Quote

If you want the actual rank you can either use another column and adjust all code references. Or add more to the formula in column A

I don't get this. A new column like the one I already have???

Quote

But as you sort the list I'm not sure why you would not just add then to the end.

I'll tell you why: when I update the chart and then sort it according to the votes values, colours are sorted, too, and they get mixed up. So some orange appears in the pink section or the yellow one in the pink one (it depends on the votes). Then I have to manually change the displaced colours (wish that could be done that automatically!!!) So before I update the rank, I take a quick look at the votes, make a mental calculation and place the new movie within the proper colour section (so I can save some time when I sort the movies since I don't have to manually change the colours).

Quote

Personally I would steer well clear of the idea of flashing text.

Because it's complicated or because it's way too kitsch?

Re: Adding Arrows to ranking chart

beautiful!!!!

it's working!
let's repeat the steps (please corrrect me if I'm wrong).
1) Open the document
2) Run countprevious macro
3) add new movies and add votes to new & existing movies. Extend formulas.
4) Run countcurrent macro
5) sort movies from highest to lowest vote.

a few additional things:

-would you leave columns C and D visible? I mean, so much information could be overwhelming!!
-if you think it's better to leave them, then I think a new row is requiered to describe each column. Problem is, if we add a new row, then the numbers on the left don't match the rank (1st movie has number 2 and so on).
-can we put the "new" marker using bold or red or blue? (or a flashing text? something like this [Blocked Image: http://www.animationlibrary.com/Animation11/Words/New/arrow_2.gif] 
or this [Blocked Image: http://www.animationlibrary.com/Animation11/Words/New/small_oval_2.gif] ???
-is it compulsory to add new movies using the last row (after the last movie)? or can I insert a new row anywhere in the chart and then extend formulas A & E?
-