Adding Arrows to ranking chart

Wigi · Dec 14th 2005

Re: Adding Arrows to ranking chart

I think I understand what you'd like to have. You just put the votes one after the other each week. Sometimes you have say 2 votes, sometimes 5, etc. But the average that is computed should reflect the change in average since last week. So you don't have exactly 5 votes each week, do you? That would complicate the formulas a lot, I guess.

Can you first confirm this before anyone starts changing the formulas?

Wigi

**Andy Pope** · Dec 14th 2005

Re: Adding Arrows to ranking chart

Hi wigi I think it's slight more complicated. A film can get none, one or more votes when the data is updated.
So in order to do a comparison average you need to know the number of votes this time and the number of votes last time for each record.

cirerita · Dec 14th 2005

Re: Adding Arrows to ranking chart

you got it! I did something very simple, you have to think in very simple terms!

column A - arrows reflecting positive/negative votes.
column B - movie names
column C - average (for all weeks; it's cumulative)
column D-IV2 - single votes (sometimes there's 1, sometimes 10)

there are 2 issues in the last formula:
-1st movie has number 2 on the left instead of number 1 (that's why there's a new row with descriptive names for each column). I guess that can be easily solved by adding a new column with numbers, right?
-when a movie only has 1-2 votes, there's an error in the formula. I think it's due to the +3 included in the formula, though I'm not sure.

if it's too complicated to include a ↔ when a movie gets no votes, then let's forget it. or maybe you can think of a better solution???

by the way, what the hell is this supposed to mean:

Quote

non-volatile formulas..

I mean, which is the advantage???

**Krishnakumar** · Dec 15th 2005

Re: Adding Arrows to ranking chart

Quote

by the way, what the hell is this supposed to mean:

Quote:
non-volatile formulas..

See this

cirerita · Dec 15th 2005

Re: Adding Arrows to ranking chart

thanks.

Quote

Avoid volatile functions wherever possible.

it sounds kind of discouraging, ¿huh?

Wigi · Dec 15th 2005

Re: Adding Arrows to ranking chart

Cirerita,

To resume the quest for the correct formulas in your excel sheet, in my opinion we must be able to put in a column how many votes are entered for each movie when the votes are updated - given that it can be 0, 1, ... . Or, alternatively, how many votes for each movie were already there on the sheet prior to the update. I think we'll have to put that in a separate column (which can be hidden or so), with a formula like COUNTA(). Then we do: copy - paste special - values to these formulas. Then, insert the new votes. But, we'll have to see whether to adjust the existing formulas and in case, how.

Wigi

cirerita · Dec 15th 2005

Re: Adding Arrows to ranking chart

I honestly think this is getting way too complicated, Wigi.

I think I'll use one of Andy's formulas where the arrows work fine and I get no errors when updating the ranking chart.

Of course, it's not perfect the way it is now (old movies' arrows seem to be "moving" up or down when they might be at a standstill, but I'll explain that minor issue in the Spanish forum so voters know what the arrows exactly mean), but I'm mostly happy with your efforts.

Thanks for your time and patience.

by the way, I made a search in the forum and it seems that there's no way to include animated gifs in a SIMPLE way, right?

the big issue in this ranking chart is that there's no way to tell new movies from old movies. I was thinking of including an animated gif, a flashing text saying "new" or something like that, but it seems pretty complicated to include animated gifs.

Is there any easier way to tell new movies from old movies??? Any simple, working idea will do.

Wigi · Dec 15th 2005

Re: Adding Arrows to ranking chart

I made it no so clear in my previous post. The formulas are indeed almost correct. But what you have to do is, each time with an update:

- copy the averages to somewhere below the votes data
- take PASTE SPECIAL - VALUES
- and we should make a minor adjustment in the formulas to see whether the average in column C is > or < or = than previous average (that you copied below)
- put the arrows on the basis of this. So ↔ or the up or down arrow.
- about the ↔ arrow for movies not which don't have recent votes: you should then also copy the number of votes prior to updating (use COUNTA formula) to below, e.g. just next to where you copied first
- change the formulas, but not drastically, just minor. We can do that if that's the problem.

If you're not caring about the ↔ arrow for not recent movies, go ahead with Andy's sheet.

Wigi

**Andy Pope** · Dec 15th 2005

Re: Adding Arrows to ranking chart

I think the key to the problem now is determining how you get the data and update it.
It should be possible then to sort the correct movement indicators and include "New" film markers. Not animated or flashing though

cirerita · Dec 15th 2005

Re: Adding Arrows to ranking chart

wigi,

I'm not saying I don't want to do it, but as I said before, I keep the ranking chart updated out of pure pleasure, this is not professional!

anyway, if you want to give it a try and make a new formula with all your suggestions, I'll try to follow all the steps you posted here.

andy,

Quote

I think the key to the problem now is determining how you get the data and update it.

I just put the new votes using the D-IV columns, that's how I do it. I don't see how can you add a "NEW" marker in the Column A and then change into an arrow (the next time the data is updated).

**Andy Pope** · Dec 15th 2005

Re: Adding Arrows to ranking chart

You manually type in all votes each time?

cirerita · Dec 15th 2005

Re: Adding Arrows to ranking chart

yep, I don't see any other way. Users vote in the forum and I copy the votes into the chart.

anyway, there are maybe 20-30 votes each time, it's not that much.

Wigi · Dec 15th 2005

Re: Adding Arrows to ranking chart

Quote from cirerita

yep, I don't see any other way. Users vote in the forum and I copy the votes into the chart.

anyway, there are maybe 20-30 votes each time, it's not that much.

If it were many votes each update, you could consider working in columns rather than rows.

The thing I said about making each time 2 formulas, fill then down for all movies, and then copy, paste as values: it can easily be done using VBA. So that's not the main obstacle.

About the NEW sign: we could put in column A an extra condition: if no votes are given, show the word NEW (or something similar)

Wigi

cirerita · Dec 15th 2005

Re: Adding Arrows to ranking chart

it's 20-30 votes for ALL the movies, not for a single movie. I don't post the full image, I capture the Excel window and then crop it. It's here

there are 50 movies in the ranking chart right now. When I update it every week I delete the older movies, trying to keep that 50 figure.

Quote

t can easily be done using VBA.

I've never used VBA before, so...

Quote

About the NEW sign: we could put in column A an extra condition: if no votes are given, show the word NEW (or something similar)

Problem is, new movies ALWAYS get votes, otherwise I don't include them in the chart.

Wigi · Dec 15th 2005

Re: Adding Arrows to ranking chart

Quote from cirerita

I've never used VBA before, so...

Someone over here can do that for you, if I have to do it, it will be tomorrow evening.

Quote from cirerita

Problem is, new movies ALWAYS get votes, otherwise I don't include them in the chart.

Oh, I see. Still we could try to do sth knowing that that movie has no votes prior to updating, so should be new. Is just a thought of mine.

I'll see what I can do, if nobody posts some adjustments to your sheet, I will do so by tomorrow evening. Kinda busy until then

Wigi

cirerita · Dec 15th 2005

Re: Adding Arrows to ranking chart

oh, man, take your time. this is not urgent!

btw, did you take a look at the Spanish forum page? that way it might be easier to see the big picture.

**Andy Pope** · Dec 15th 2005

Re: Adding Arrows to ranking chart

Here we go then. Changed the layout slightly to include 2 columns C:D which store number of current and previous votes.
The first macro CountPreviousVotes stores number of votes.
Then you update votes and add new films. Remembering to extend formulas in columns A and E
Then run second macro CountCurrentVotes
The marker in column A should be either Up/Down/Same/New

To test add some votes to the first couple of records such that you can tell the average should go up or down. Also add new film to end of list. Then run 2nd macro.

You will probably need a sort in there as well

cirerita · Dec 15th 2005

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

**Andy Pope** · Dec 15th 2005

Re: Adding Arrows to ranking chart

Procedure is correct.

I would add a column header and hide the vote counting columns.
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[vba]=(ROW()-1) & ". " & IF(E1>AVERAGE(F1:OFFSET(F1,0,D1-1)),"↑",
IF(E1<AVERAGE(F1:OFFSET(F1,0,D1-1)),"↓",IF(D1=0,"NEW","--")))[/vba]
You should be able to add films in anywhere as long as you make sure the formulas are applied to the new rows. But as you sort the list I'm not sure why you would not just add then to the end.
You can use conditional formatting on column A to make the New appear bold/coloured etc. Personally I would steer well clear of the idea of flashing text.

cirerita · Dec 15th 2005

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?

Adding Arrows to ranking chart

Participate now!

Share