Excel Spreadsheet problem

[HughMartin]

I would really appreciate some help with a simple scoring spreadsheet I have constructed.

Column A, rows 3 - 38 are team names
Columns B to X are weekly scores
Column Y is cumulative total score
Column Z is ranking.

For the ranking I have used the RANK function.

The formula for the ranking for row 3 is therefore =RANK.EQ(Y3,Y$3:Y$38)

This works fine, but I want to get an equals sign after any equal ranking, ie;-

1
2
3=
3=
4
5
etc

Can anyone offer the solution? any help greatly appreciated.
Cheers
HM

[jimtherev]

Easiest solution which involves no techonwizardry at all is to insert a narrow column which contains the = sign in every cell.
Or am I missing something?

[HughMartin]

I'm afraid you are missing something Jim. I only need an = sign to be placed alongside numbers of equal ranking and I want the prog to put the = sign in automatically. Thanks anyway.

[Sprogget]

Could you not use IF to compare points & insert a '=' in an adjoining coloumn? So, If Z4=Z5, "=",0

Or similar along those lines might work for you.

[bnt]

Like Sprogget says , and if you need the = in the same cell as the number, use "&" to join them together.

[HughMartin]

Hi Sprogget,
Thanks for your suggestion and it looks like you are on the right lines but due to my complete lack of knowledge of excel beyond simple adding up rows or columns I will need something more specific. Each potential "=" cell needs to compare the value in the adjoining ranking cell with all the other ranking cells (Z3 to Z38). I am sure the formula won't be too difficult or long but I just don't know excel syntax or expressions.

[Sprogget]

The actual expression would look like =IF((Z3=Z4),"=",0) You're telling the cell in which the formula is written to compare Z3 &Z4 and if they're equal, to place an equals sign in the cell.

The syntax is =IF ( logical_test, value_if_true, value_if_false ).

You can muck around with the placing of the formula in order to get it to display correctly. Have a look at this for a decent tutorial. What I do with new formulae is save a copy of my spreadsheet & experiment on that copy until I've got it right, then go from there.

IF Function - Excel 2010 IF Function Quick Tutorial

[ExGrunt]

Hugh,

An interesting challenge.

Because you want to test the rows dynamically to calculate the matches and the equally ranked cells may not be next to each other you will have to use a two column solution. Otherwise you will create a circular error - ie the value of a cell (A) depends on the value of a cell (B) which is calculated using the result of the original cell (A).

So add column aa after column z and put this formula in cell aa3 and then copy it down:

=IF(COUNTIF(Y$3:Y$38,Y3)>1,"=","")

HTH

EG

[jimtherev]

Originally Posted by HughMartin

I'm afraid you are missing something Jim. I only need an = sign to be placed alongside numbers of equal ranking and I want the prog to put the = sign in automatically. Thanks anyway.

Oops! apologies. Read the question, Jim.

[Sprogget]

may not be next to each other

If I understand Hugh's purpose correctly, he's creating the = to denote teams who are jointly sharing a position in a league table, so by definition, they would be next to each other in the rows.

[ExGrunt]

Sprogget,

he's creating the = to denote teams who are jointly sharing a position in a league table, so by definition, they would be next to each other in the rows.

As Hugh enters the weekly scores in columns B-X each team's total score will change and hence their ranking on the leader board. There is no guarantee that they will be in the correct order unless the table is resorted each week on the values in column Y. For your statement to be correct you have to assume that that step will be taken, which was not given in the original brief in post 1.

I have written a solution which works for the original brief and does not assume any action not explicitly stated. I raise this not as a criticism, but as a learning point as it is a neat illustration of how software projects go wrong.

HTH

EG

[Sprogget]

Agreed, but if it's a league table as I believe it to be, then it's a cast iron assumption that that sorting step will be taken. Otherwise it's not a league table, it's just a list of teams or people with their current scores next to them and the desire to indicate a jointly held position by using '=' is kind of a mahoosive clue that that is the intention of the original poster.

[bnt]

Originally Posted by Sprogget

The actual expression would look like =IF((Z3=Z4),"=",0) You're telling the cell in which the formula is written to compare Z3 &Z4 and if they're equal, to place an equals sign in the cell.

The syntax is =IF ( logical_test, value_if_true, value_if_false ).

That's the way I would do it, except I would use a "" (empty string) instead of a 0 if false.

I've written a quick example spreadsheet to show what I mean:
http://dl.dropbox.com/u/85261/EqualScores.xls
It has the "working area" off to the right (column H on), then the final string to display or print is assembled in column A. I did separate formulas for "equal to the score above" and "equal to the score below", so you get an = sign in both matching scores.

I always try to split problems up in to multiple cells, spread it out over the sheet, rather than try and do it all in one cell with one big formula. Simpler is always better, and you have literally millions of cells to play in! You can always hide whole rows and columns, or just not display or print the workings. Makes it easier to write, and to troubleshoot later when you've forgotten what you did last year ...

[MacBoero]

Even if the table is being sorted into ranking order, ExGrunt's approach is better, because Sprogget's approach will not put the '=' in the right places. For example:
.....A...... B
1 Team A 1
2 Team B 2
3 Team C 2
4 Team D 2
5 Team E 3

You would expect to see an = in lines 2, 3 and 4. But by only comparing each line with the line above, Sprogget's approach would only result in an '=' in lines 3 and 4. ExGrunts approach takes each line in turn and produces of count of the number of ranks with the same value and compares it to 1. So lines 1 and 5 will not have an = because only 1 line has the same rank in each case, lines 2, 3 and 4 will, because the count will be 3 in each case.

[Sprogget]

That's true, but I discounted it as a convention in league tables is to have a team in say 1st position & then the following team displayed in joint 1st position. So for clarity, the formula only highlights the lower team as being in joint position, but it would be easy enough to redo a formula to place the = on both teams in a table.

[HughMartin]

I didn't mean to set a challange for you guys but I do appreciate your input. I am afraid I am a turkey and not an eagle in Excel matters.

It is not strictly speaking a league table in that there is not the intention to resort the team names every week. But having said that, it might be easier to do so.

I will have a play with your various suggestions this evening.

Thanks again
HM

[HughMartin]

ExGrunt, you are the ace of the base - a perfect solution, thank you.

To those others who offered solutions, I apologise if I didn't explain myself adequately. You are all obviously highly competent in all things Excel. I must go and buy myself an Excel for Dummies.

[Sprogget]

Job jobbed is job job jobbed.

Excel lives to fight another day.