I would appreciate help in either confirming that the Offset function of Excel 2019 is failing, or I am doing something wrong with it.
The link to the large spreadsheet is https://www.dropbox.com/preview/Life...?role=personal I apologise for the size, but as the problem is embedded in many cross-links, I don’t think my problem can be shown any other way.
There are several outstanding other problems with this spreadsheet, due to my tidying several issues up. Please ignore those – I do not think any of those issues relate to the OffSet one.
The two sheets to look at are NormData and HIIT NN50. The problem probably exists elsewhere, but these two will show the problem.
Sheet HIIT NN50 links to NormData via the OffSet function.
In the HIIT NN50 sheet, the OffSet function seems to works fine for most columns, but not all. Why?
I use the OffSet function to generate an average of the previous 15 days prior to the reference date – so in sheet HIIT NN50 for column FW, row 496, I use:
=IFERROR(AVERAGE(OFFSET(NormData!$A5076,$B$513,FW$2):OFFSET(NormData!$A5076,($B$513+$B$514),FW$2)),"")
This takes the reference as A5076 in NormData, goes up one row ($B$513, -1), and along to column 179 (in FW2); then the average is calculated from 15 rows above (that is the $B$13+$B$14)
Separately, I calculate the 15 day average, back from the day before, on the NormData itself for each column (shown in row 5090).
I then copy this to the HIIT NN50 sheet for comparison – row 499 on that sheet.
I then calculate the difference between the OffSet result for the HIIT NN40 (row 497) and calculate the difference between that and the now-copied NormData row. That is, the cells in row 497 and that of the copied into row 499. I use an IF function to generate the word BAD if there is a significant difference.
If you look at this row 501, you will see that there are many instances of major differences between what the OffSet function computes and the real 15-day averages in the NormData sheet.
Given that the OffSet functions works correctly for most columns in the HIIT NN50 sheet, why not all? Am I doing something wrong for those columns’ results? I visually checked to see if columns were misaligned (that is had moved along one or two columns), but couldn’t see this.
Bookmarks