Hello all,
I've got a table with date and time of sportmatches being played. In another sheet I would like to have the most 7 upcoming games.
a short/simple example:
<tbody>
</tbody>yearfrac =>
MOD(YEARFRAC($H$2-1;EDATE(B13;-12));1)
$H$2 = date today (14-aug).
C13 = column with dates.
As you can see the dates are not sorted.
On the first sheet I have this overview of the upcoming matches.
A short/simple example:
<tbody>
</tbody>
The first (5) I find with the formula =MATCH(G4; D$13:D$25; 0).
That one is easy. For the corresponding dates I'm using INDEX as you can see.
But here it comes. For the second upcoming match I first used OFFSET but i learned that's a volatile formula so I changed this with INDEX.
But still when I opened and closed the file i had the 'save?' question so something was still volatile.
But then I learned on http://www.decisionmodels.com/calcsecretsi.htm that "Using INDEX as the second part of a range reference, for example A$2:INDEX(A$2:A$8,7,), will also cause the reference to be flagged as 'dirty' when the workbook is opened".
AH! that was the explanation I was looking for.
So for the second, third, etc.. upcoming match I'm now using a sort-of-dynamic INDEX instead of OFFSET.
the formula in H5 and lower = MATCH(G5;INDEX(D$13:D$25;H4+1):D$25;0)+H4
Is there another way to change this formula ( "INDEX(D$13:D$25;H4+1):D$25" ) so it is not dirty anymore and Excel wont recalculate with every trigger.
Ciao!
Ricardo
I've got a table with date and time of sportmatches being played. In another sheet I would like to have the most 7 upcoming games.
a short/simple example:
| A | B | C | D | E | |
| 12 | Round | Date | Time | yearfrac | key/row# |
| 13 | 1 | 11 aug | 18:00 | 0,005555556 | 1 |
| 14 | 12 aug | 20:45 | 0,002777778 | 2 | |
| 15 | 12 aug | 18:00 | 0,002777778 | 3 | |
| 16 | 13 aug | 20:45 | 4 | ||
| 17 | 15 aug | 18:00 | 0,994444444 | 5 | |
| 18 | 13 aug | 20:30 | 6 | ||
| 19 | 2 | 19 aug | 20:45 | 0,983333333 | 7 |
| 20 | 18 aug | 18:00 | 0,986111111 | 8 | |
| 21 | 19 aug | 18:00 | 0,983333333 | 9 | |
| 22 | 19 aug | 18:00 | 0,983333333 | 10 | |
| 23 | 19 aug | 20:30 | 0,983333333 | 11 | |
| 24 | 20 aug | 20:30 | 0,980555556 | 12 | |
| 25 | 21 aug | 20:30 | 0,977777778 | 13 |
<tbody>
</tbody>
MOD(YEARFRAC($H$2-1;EDATE(B13;-12));1)
$H$2 = date today (14-aug).
C13 = column with dates.
As you can see the dates are not sorted.
On the first sheet I have this overview of the upcoming matches.
A short/simple example:
| G | H | I | J | |
| 2 | Date today: | 14-aug | ||
| 3 | Biggest | row# | Date | Time |
| 4 | 0,994444444 | 5 | 15-aug | INDEX($C$13:$C$25;H4) |
| 5 | 0,986111111 | 8 | 18-aug | |
| 6 | 0,983333333 | 7 | 19-aug | |
| 7 | 0,983333333 | 9 | 19-aug | |
| 8 | 0,983333333 | 10 | 19-aug |
<tbody>
</tbody>
The first (5) I find with the formula =MATCH(G4; D$13:D$25; 0).
That one is easy. For the corresponding dates I'm using INDEX as you can see.
But here it comes. For the second upcoming match I first used OFFSET but i learned that's a volatile formula so I changed this with INDEX.
But still when I opened and closed the file i had the 'save?' question so something was still volatile.
But then I learned on http://www.decisionmodels.com/calcsecretsi.htm that "Using INDEX as the second part of a range reference, for example A$2:INDEX(A$2:A$8,7,), will also cause the reference to be flagged as 'dirty' when the workbook is opened".
AH! that was the explanation I was looking for.
So for the second, third, etc.. upcoming match I'm now using a sort-of-dynamic INDEX instead of OFFSET.
the formula in H5 and lower = MATCH(G5;INDEX(D$13:D$25;H4+1):D$25;0)+H4
Is there another way to change this formula ( "INDEX(D$13:D$25;H4+1):D$25" ) so it is not dirty anymore and Excel wont recalculate with every trigger.
Ciao!
Ricardo
