Hi,
I'm looking to show how many games behind a division leader each team in my division. I would also like to replace any 0s with "-" (an en dash).
The formula I was using to show Games Back was: =INDIRECT("$B$14")-B15 etc. (in case you still weren't sure what sort of newb I was...). To replace 0s with dashes, I would move that to a reference cell off to the side, and then in the important cell I'd use something like: =IF(Q14=0,"-",Q14). Obviously if I put this two formulas together I'd wind up with a circular reference.
Is it possible to calculate games back using an absolute reference while also replacing 0s (for first place teams) with a dash?
Here's what things look like now:
Thanks for any and all feedback!
R
I'm looking to show how many games behind a division leader each team in my division. I would also like to replace any 0s with "-" (an en dash).
The formula I was using to show Games Back was: =INDIRECT("$B$14")-B15 etc. (in case you still weren't sure what sort of newb I was...). To replace 0s with dashes, I would move that to a reference cell off to the side, and then in the important cell I'd use something like: =IF(Q14=0,"-",Q14). Obviously if I put this two formulas together I'd wind up with a circular reference.
Is it possible to calculate games back using an absolute reference while also replacing 0s (for first place teams) with a dash?
Here's what things look like now:
| Excel 2007 | ||||||
|---|---|---|---|---|---|---|
| B | C | D | E | |||
| 3 | W | L | W % | GB | ||
| 4 | ||||||
| 5 | 4 | 3 | 0.571 | - | ||
| 6 | 4 | 3 | 0.571 | - | ||
| 7 | 4 | 3 | 0.571 | - | ||
| 8 | 3 | 4 | 0.429 | 1 | ||
| 9 | 3 | 4 | 0.429 | 1 | ||
| 10 | 2 | 5 | 0.286 | 2 | ||
Standings | ||||||
Thanks for any and all feedback!
R
