Adding Arrows to ranking chart

Re: Adding Arrows to ranking chart

Quote from Andy Pope

Hi Wigi,

Doesn't the use of <= prevent the ↔ from ever being true?
[vba]=IF(D3>AVERAGE(INDIRECT(ADDRESS(ROW(),5) &":" &ADDRESS(ROW(),COLUMN($N3)))),"↑",
IF(D3<=AVERAGE(INDIRECT(ADDRESS(ROW(),5) &":" &ADDRESS(ROW(),COLUMN($N3)))),"↓","↔"))[/vba]

Of course, sorry a typo. Take, respectively, ..... > ........ < ......

@ Cirerita:

you can adjust the formulas if you want to type the votes in the other order, so the latest score at the end of the row. My suggestion was one way to do it. With many votes, columns could easily be long. So inserting a new column D could be better than. Do as you prefer (but change the formulas if needed)

"having the column D as the latest scores forces me to write down those scores every week"

That's correct. Perhaps then, add the votes at the end of each row (also in case you only have a few new votes), but this changes the formulas. However, the basic picture suggested by Andy and worked out by me, remains.

Greetz,

Wigi

Re: Adding Arrows to ranking chart

Quote from cirerita

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

Display More

Why do you want to copy C to D? C is just the average of existing votes, and sometimes you add a new vote. But you're right suggesting that the last vote should be at the end of a row. Or am I misinterpreting the structure of your worksheet?

Wigi

Re: Adding Arrows to ranking chart

Here is a revision that handles the latest scores being added to the end of the row.
[vba]=IF(C2>AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN()+1)&":"&ADDRESS(ROW(),COUNTA(D2:N2)+2))),
"↑",IF(C2<AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN()+1)&":"&ADDRESS(ROW(),COUNTA(D2:N2)+2))),"↓","↔"))[/vba]

Re: Adding Arrows to ranking chart

Quote

by the way, did you already vote your movies?

Can you not tell from my example workbook I'm a Wallace & Gromit fan :lol:

Re: Adding Arrows to ranking chart

Well if nobody votes then the vote count must be zero, no?

Re: Adding Arrows to ranking chart

You need to store, in another column, the fact that the film received no votes for this period.
That way you can adjust the formula such that non voted for films always get the <-> marker.
Films with votes then have the original formula applied to determine whether they went up, down or remained the same.

Re: Adding Arrows to ranking chart

Cirerita,

perhaps you should insert column headings in row 1, as Andy did in his file. Then you know how many columns / votes are being used maximally (just counting the number of headers if each column corresponds to 1 moment of votes). If a movie has no vote in the last column, you know that is thas to be ↔ arrow. If it has, you should evaluate whether it is or ↔ or ↑ or ↓. Use COUNT() formula, as well as ADDRESS(), INDIRECT, ROW() and COLUMN(), but start from the existing formulas in your sheet.

If you're not following anymore, wait until me or someone else posts the formulas.

Wigi

Re: Adding Arrows to ranking chart

I have inserted a new column A which contains a 1 if the film received a vote in the lasted set of figures or 0 if not.
So the revised formula would be[vba]=IF(A2=1,
IF(D2>AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN()+1)&":"&ADDRESS(ROW(),COUNTA(E2:O2)+2))), "↑",
IF(D2<AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN()+1)&":"&ADDRESS(ROW(),COUNTA(E2:O2)+2))),"↓","↔")),
"↔")[/vba]

Re: Adding Arrows to ranking chart

Hi,

I think Andy's formulas can be replaced with the following non-volatile formulas..

In D2 and copy down,

=AVERAGE(E2:INDEX(E2:IV2,MATCH(9E+99+307,E2:IV2)))

In B2 and copy down,

=IF(A2=1,IF(D2>AVERAGE(E2:INDEX(E2:IV2,MATCH(9.999E+307,E2:IV2)-1)),"↑",IF(D2<AVERAGE(E2:INDEX(E2:IV2,MATCH(9.999E+307,E2:IV2)-1)),"↓","↔")),"↔")

Re: Adding Arrows to ranking chart

Hello Krishnakumar,

just interested in your formula:

where does the 9E+99+307 come from? I understand you're just looking for the number of votes in each row and that you use a MATCH(), but why 9E+99+307? Especially the 307.

Wigi

Re: Adding Arrows to ranking chart

Hi Wigi,

It was a typo.

Actually it meant to be 9.99999999e+307.

HTH

Re: Adding Arrows to ranking chart

Quote from Krishnakumar

Hi Wigi,

It was a typo.

Actually it meant to be 9.99999999e+307.

HTH

Display More

Then you're just having luck as the 307 doesn't change things over here.

BTW, why don't we make the A-column with a formula (or alternatively, in the formula in the B-column, replace A2=1, by a formula). This in the spirit of Krishnakumar's suggestion above.

Something like this, in B2:

=IF(IF(MATCH(9.999E+307,E2:IV2)=COUNTA($E$1:$IV$1),1,0)=1,IF(D2>AVERAGE(E2:INDEX(E2:IV2,MATCH(9.999E+307,E2:IV2)-1)),"↑",IF(D2<AVERAGE(E2:INDEX(E2:IV2,MATCH(9.999E+307,E2:IV2)-1)),"↓","↔")),"↔")

and copied down

Wigi

Re: Adding Arrows to ranking chart

Hi,

Quote

BTW, why don't we make the A-column with a formula (or alternatively, in the formula in the B-column, replace A2=1, by a formula). This in the spirit of Krishnakumar's suggestion above.

Something like:

VBA:
=If(MATCH(9.9999E+307,E2:IV2)=COUNTA($E$1:$IV$1),1,0)

and copied down

Display More

May be...

Delete Col A,

In A2 (earlier B2),

=IF(MATCH("zzzzzzzzz",$D$1:$IV$1)=MATCH(9.9999E+307,D2:IV2),IF(C2>AVERAGE(D2:INDEX(D2:IV2,MATCH(9.999E+307,D2:IV2)-1)),"↑",IF(C2<AVERAGE(D2:INDEX(D2:IV2,MATCH(9.999E+307,D2:IV2)-1)),"↓","↔")),"↔")

HTH

Re: Adding Arrows to ranking chart

See attached. I kinda summarized the discussion with the formulas in the sheet.

Good luck with it

Wigi