Sports League Head to Head Tie Breaker causing circular references

Hello all,

Im currently working on sports league standings table that auto-updates as scores are entered each week.(Attached)

Im supposed to be ranking the teams based on these criteria below, but I have only been successfully accomplishing this by manually adjusting the standings when Head to Head tie breakers are necessary. If the teams havent played yet or are not tied in the points column the standings work correctly.

1. Points(For Wins & Ties)
2. Head To Head Result
3. Conference Wins
4. Runs Allowed (Less Runs are ranked higher)

I have worked out a formula in column Q that does a check of the current standings (Without H2H) to see if the in the team in the row is tied in points and has won a game against the team currently ranked above them. If so, a "1" is displayed, if not a "0".

I was hoping to use this 1 value that was output by the formula and add it back into the ranking calculations to correctly display my rankings based on all the criteria above. But this causes a circular reference in my calcs.

Does anyone have any idea on how to get around this problem? I would greatly appreciate it.

Thanks.

It's not really an Excel problem. The problem is that the logic you are trying to use is circular logic. You are trying to use the current standings to determine if head-to-head is 1, and then are you using whether head-to-head is 1 to determine the current standings. What you really need is two-step logic that may require a macro.

Can you walk through the logic you are using to determine the standings?

That works. I would think you would have to copy the final version back to the original for the next round.