Sort data whn represeted by formulas

[stacy]

Hey everyone...

I have a simple football tracking spreadsheet that keeps track of 3
players and their winning percentage. It is basically the number of
wins, number of losses, and games back.

Player Wins Losses Games Back
A 33 27 0
B 31 29 2
C 29 31 4

My problem is, the numbers for wins and losses are cell references to
another part of the spreadsheet. Basically, '33' is really =F18, and
'27' is =F19, and so on. The formula I am using to calculate games
back is as follows:

=(((D26+E26)-($D$26+$E$26))*0.5)+($D$26-D26)

However, this formula is dependent on the data to be sorted by always
having the highest number of wins player listed first, hence my sorting
issue. Whenever I sort the data in the grid above, using the row
containing wins as the control, it flips out and does some strange
things... Changing numbers and references.

Any ideas on how to solve this (simple I am sure) problem would be
great. Thanks as always ahead of time!!!

 

[Guest]

A simple way to do it is to copy the relevant cells and paste only their
values back to the same cells (pastespecial). Then a sort would work on the
values rather than the formulas.

Mike

 

[Guest]

Hi
When sorting with cells referencing other parts of your sheet in te way you
describe you should use absolute addressing i.e. the '33' should be =$F$18

Your formula =(((D26+E26)-($D$26+$E$26))*0.5)+($D$26-D26) appears to equate
to zero...

 

[Dave Peterson]

Excel won't sort the way you want when you have formulas like that in your data.

I'd try a different formula--but that formula will depend on the other data.

Maybe you could use:
=vlookup(a2,sheet2!a:d,2,false)
If you have a table with all the wins/losses in one spot.

Or if you have a giant worksheet with Names in column A and W/L/T's in column B:

=sumproduct(--(sheet2!a1:a100=a2),--(sheet2!b1:b100="W"))
similarly for L's
=sumproduct(--(sheet2!a1:a100=a2),--(sheet2!b1:b100="L"))

(extend the range to what you need, but don't use the whole column.)

but that's just a guess based on the other data.

Ps. Since each player will "play" the same number of games, you can figure
games behind just by subtracting wins

=max($B$2:$B$4)-B2
(dragged down.)