Support Board
Date/Time: Fri, 28 Feb 2025 13:49:10 +0000
Post From: Overlaying Pivot Points
| [2021-08-12 14:58:09] |
| Sawtooth - Posts: 4177 |
|
Doesn't K3 count as 1 cell with row 3, so we need to give it 997 offset to get the last value in the column, if J30=1000?
J30 is the number of chart bars that the spreadsheet sees, where each chart bar is a row, and the current bar is in row 3.If J30 is 1000, then there are 1002 rows in the spreadsheet, because of the header rows of 1 & 2. The idea to subtract 2 from J30 is to compensate for the header rows, so really the -2 is not accurate, as you point out. It should actually be -1 to get the last row, which is 1002 if J30 is 1000. The offset that this OFFSET uses in this situation is not critical. The goal is to count any number of days covered by the sheet. This would be a better L3 formula, as it only looks at the existing rows of the sheet: =MAX(K3:OFFSET(K3,$J$30-ROW()+2,0)) Actually, after taking a closer look at this, you don't even need the formula in L3, for this day count setup. Your M3 formula could be: =AND(K3=$K$3,TODAY()=INT(A3)) In fact, you could eliminate L3 and M3 and use this in O3: =IF(AND(K3=$K$3,TODAY()=INT(A3)),ID3.SG12@3,0) Date Time Of Last Edit: 2021-08-12 19:50:13
|