Re: Sorting Data Is Changing Formulas

Quote from Nippy

Thanks guys - and thanks JF, you are spot on, now when I sort the league, the formulas do not change. This is great......But!

JF - if we delete the scores from a match, (ie, assume that the league is not yet completed) - the number of games played does *not* reduce, it just stays stuck on '16'.

I thought this 'Plyd' column was counting up the number of times a team had an entry score in column C or column E...

The plot thickens! Any thoughts?

Display More

No, at present, it is counting the number of times the team name has been entered in the range (b5:b30).

There are a couple ways around this.

1. first, do not enter the names until the game is played (of course then you would need a seperate sheet for "Scheduled" games).

2. simply, change the formula to total the number Won, lost, Draw

3. You could use the sumproduct() formula to only count games where there is a score.

Some other info: Rather than use a static range, I would recommend using dynamiclly named ranges. This will allow for adding more teams if needed. But you should not use blank lines between the teams in this case. By using dynamic named ranges, you would not need to adjust the formulas if teams were added.

I also just noticed a flaw - at present you were count losses as a score/value of less than 5. Well if there is no score (not yet played) it interprets the null as <5 so counts it as a loss so the formula will need tweeking)

----- Added Comment ----

OK, I tweaked the formula to not count games where there is a blank for the score. FYI, I had to be able to look for a 0 as one of your lines had a value of 0 ) I also added a sheet that is using Dynamic ranges. Try adding a game at the bottom of the list with 2 new team names and give it scores, then adde the New Team Names to the Stats area (Once you do, the rst of the Stats will become visible)