Posts by cirerita

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.

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.

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.

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

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.

Re: Adding Arrows to ranking chart

thanks.

Quote

Avoid volatile functions wherever possible.

it sounds kind of discouraging, ¿huh?

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

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.

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

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

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

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

andy,

now it's working like a charm! thanks a lot! :thanx:
by the way, did you already vote your movies?

Re: Adding Arrows to ranking chart

going crazy over here!

If I keep column D as last week's scores, when I paste the new scores (from column C) into column D, the values from C change! (I think that was what Wigi was saying before).

Couldn't I use the formula without last week's score? That is, column C would be current scores and column D first vote, not last week's scores?? Of course, keeping the arrows.

or else, I wouldn't mind keep column D as last week's scores, but there should be a way to paste values from column C into column D without getting column C changed!!!

by the way, arrows work wrongly! the higher the vote, I get a down arrow; the lower the vote, a I get an up arrow!!!

Re: Adding Arrows to ranking chart

thank you both!

is column D actually needed as latest score? what for? to compare it with last week's score?

I think the arrows already let you know if the movie is doing fine or getting poor votes. Besides, having the column D as the latest scores forces me to write down those scores every week, doesn't it? or can it be done automatically?

Re: Adding Arrows to ranking chart

thanks a bunch! it does work!

well, column D was not the latest score, it's actually the first vote. The only score is column C... but it doesn't matter, the formula works a treat!!!

well, the up and down arrows do work, but the other one, ↔, never appears.
I changed your formula
=IF(C1<MEAN(E1:N1); "↑";IF(C1>MEAN(E1:N1);"↓";"↔"))
=IF(C1<MEAN(E1:N1); "↑";IF(C1>MEAN(E1:N1);"↓";IF(C1=MEAN(E1:N1);"↔")))

but no luck... (I translated PROMEDIO as MEAN, but I don't know if that's the word which appear in the English version of Excel. Or is it AVERAGE?)

Re: Adding Arrows to ranking chart

The colours change when I hit "order" column C. Excel asks me if I want to extend the selection to B (movie names) and I say yes. The average column is reordered as well as column B (movie names). If an movie with an orange color gets poor votes, then it goes down to the pink section. That's how the colours change!!! I cannot apply that to arrows.

How can you store both ranks in the SAME column, as you suggest?

Re: Adding Arrows to ranking chart

yep, promedio is average.

I know the ranking has changed thanks to the colors (at least when they get mixed), otherwise, I can't.

But I could add a new column and make it work that way, right? That is, A1 for previous rank, B1 for current rank and C1 for movie names. or would you do it differently?

Re: Adding Arrows to ranking chart

thanks for the If formula, but I think it won't work the way the chart is right now.
as you can see, there's only one rank for each movie, there are not a previous and a current rank (though I could add a new column before the column with the movie names).

also, I double-clicked on one of the scores so you can see the only formula used in this sheet: average.

here's the image:
[Blocked Image: http://img200.imageshack.us/img200/6717/06867pc.jpg]

Re: Adding Arrows to ranking chart

thanks for the tip on the wingdings fonts. I'll give a try!

andy,
I already attrached an image of the chart. That's it. The only command used in that sheet is the addition to get the average score. Then I use "order" (the votes column) and the movies get ordered from the highest to the lowest score.

To determine the rank, colors are a good clue (when you reorder the movies, colors get mixed), but only within the same category, if you know what I mean (if you find an orange movie in the pink section you know the movie has gotten a lower score than the previous week).