+ Reply to Thread
Results 1 to 18 of 18

Offset malfunction in Excel 2019

  1. #1
    Forum Contributor
    Join Date
    08-06-2012
    Location
    Brighton, UK
    MS-Off Ver
    MS Office 2021 and 2019
    Posts
    112

    Offset malfunction in Excel 2019

    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.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Offset malfunction in Excel 2019

    Hi,
    cant you post a small sample in excel to show where you encounter the problem?
    The dropbox requires a log in with google account..?

  3. #3
    Forum Contributor
    Join Date
    08-06-2012
    Location
    Brighton, UK
    MS-Off Ver
    MS Office 2021 and 2019
    Posts
    112

    Re: Offset malfunction in Excel 2019

    Thanks belinda for responding. I thought I'd replied earlier, but I cannot see my response now, so I'll try again.

    I regret I cannot submit a smaller spreadsheet, as the problem is embedded in the whole.

    I have trouble working out how to do a public link to my spreadsheet, but maybe this is the correct one:
    https://www.dropbox.com/scl/fi/kyzho...qfnile6t6tqxaz

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Offset malfunction in Excel 2019

    Please prepare a cut-down copy. We do not need to see the error multiple times in a large workbook, just a few times in a sample.

    Use the attachment facility here, please. There are instructions at the top of the page.

    There is no way I am going to download a 40MB workbook!!!
    Last edited by AliGW; 04-27-2022 at 10:22 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Contributor
    Join Date
    08-06-2012
    Location
    Brighton, UK
    MS-Off Ver
    MS Office 2021 and 2019
    Posts
    112

    Re: Offset malfunction in Excel 2019

    Thanks Ali. Apologies. I do not know if I can reduce my spreadsheet and still retain the problem, but I am trying - it is a lengthy process, but I'm on it.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Offset malfunction in Excel 2019

    Thank you, and thanks for your rep comment, which made me giggle!

  7. #7
    Forum Contributor
    Join Date
    08-06-2012
    Location
    Brighton, UK
    MS-Off Ver
    MS Office 2021 and 2019
    Posts
    112

    Re: Offset malfunction in Excel 2019

    I have finally managed to reduce my spreadsheet to hopefully a manageable size, OffSet sample now about 3MB:
    https://www.dropbox.com/scl/fi/9y9lq...6hgnx3pp8tr1vq

    The OffSet problem is shown in the HIIT NN50 sheet, where the OffSet 15-day average of the previous 15 days (of the 02May22 data in the NormData sheet) in row 500 is compared with the actual average directly calculated for the 15 days prior to 02May22 – shown in row 2294 of NormData).

    As will be seen, most of the two differently generated figures are effectively the same (for example, cols G,H, J, etc). But not always - as seen in cols I, K, L, etc.

    I hope someone can detect where the fault lies: in the actual OffSet function itself, or my usage of it.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Offset malfunction in Excel 2019

    We can't download the workbook without a DropBox account.

    And it's still way too big. You do not need to show us thousands of rows of data. Cut it down to 15-20 rows of data and under 1MB in size and use the attachment facility here, please.

  9. #9
    Forum Contributor
    Join Date
    08-06-2012
    Location
    Brighton, UK
    MS-Off Ver
    MS Office 2021 and 2019
    Posts
    112

    Re: Offset malfunction in Excel 2019

    Glad I made you giggle. In turn, you prompted me to do a necessary fuller investigation of my spreadsheet, unearthing many other faults in it along the way. Like they say, there's no wrongs, just learning experiences.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Offset malfunction in Excel 2019

    You're welcome and I'm sorry I can't help you with this.

  11. #11
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Offset malfunction in Excel 2019

    I don't understood why you use

    =IFERROR(AVERAGE(OFFSET(NormData!$A5076,$B$513,FW$2):OFFSET(NormData!$A5076,($B$513+$B$514),FW$2)),"")

    instead of

    =IFERROR(AVERAGE(OFFSET(NormData!$A5076,$B$513,FW$2,$B$513+$B$514)),"")

    Regards.

    Ps: I can't understand a long text, it might easier if you could show
    what is the target cell , desired result and how it calculate.

  12. #12
    Forum Contributor
    Join Date
    08-06-2012
    Location
    Brighton, UK
    MS-Off Ver
    MS Office 2021 and 2019
    Posts
    112

    Re: Offset malfunction in Excel 2019

    Thanks Ali for at least being prepared to try. Apologies for putting such large speadsheets up.

  13. #13
    Forum Contributor
    Join Date
    08-06-2012
    Location
    Brighton, UK
    MS-Off Ver
    MS Office 2021 and 2019
    Posts
    112

    Re: Offset malfunction in Excel 2019

    Hi Belinda,

    Apologies for having posted too large spreadsheet examples. I feared I would loose the cause of my problem.

    I have now reduced my spreadsheet example for the problem to about 400K, and attached it. In so doing, I have lost several columns which showed either OffSet not working, or working. But the few that remain will hopefully be adequate to show the difficulty.
    Attached Files Attached Files

  14. #14
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Offset malfunction in Excel 2019

    Maybe try

    Non-volatile formula at G3

    =IFERROR(AVERAGE(INDEX(NormData!G$3:G$173,MATCH($A3,NormData!$A$3:$A$173,)-15):INDEX(NormData!G$3:G$173,MATCH($A3,NormData!$A$3:$A$173,)-1)),"")

    or volatile formula at I3
    =IFERROR(AVERAGE(OFFSET(NormData!I$2,MATCH($A3,NormData!$A$3:$A$173,)-1,,-15)),"")
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    08-06-2012
    Location
    Brighton, UK
    MS-Off Ver
    MS Office 2021 and 2019
    Posts
    112

    Re: Offset malfunction in Excel 2019

    Thank you Bo_Ry. Both those formulae work fine - so I'll use the 'volatile' one, as it is shorter.

    I'm not familiar with the terms 'volatile' and 'non-volatile', so I'll need to read up on those. Similarly with regards, to MATCH - not a function I've ever used before.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Offset malfunction in Excel 2019

    Volatile functions can slow your workbook down, so use them sparingly. If this needs to work on several very large ranges, then go for the non-volatile option. Shorter is not always better.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

  17. #17
    Forum Contributor
    Join Date
    08-06-2012
    Location
    Brighton, UK
    MS-Off Ver
    MS Office 2021 and 2019
    Posts
    112

    Re: Offset malfunction in Excel 2019

    Thanks Ali.

    However, I'm already far through updating my dozens of sheets with the volatile version and my spreadsheet doesn't seem noticeably slower. But if that happens, I'll now know to change to the non-volatile one.

  18. #18
    Forum Contributor
    Join Date
    08-06-2012
    Location
    Brighton, UK
    MS-Off Ver
    MS Office 2021 and 2019
    Posts
    112

    Re: Offset malfunction in Excel 2019

    Hi Bo_Ry. Just a quick update: sadly these functions (both) also occasionally result in errors - a difference between an average of prior 15 days in the NormData and that generated by your Match function. I think less than the OffSedt ones I'd previously used.

    I'll now try to reduce my 180MB spreadsheet to less than 1MB but still showing the problem, so I can post it here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA (or function) to turn a date into a number i.e 12/31/2019 to 2019.12
    By lynnsong986 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-23-2019, 03:56 PM
  2. [SOLVED] IF error malfunction in excel 2016
    By pejoi78 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-03-2019, 08:35 AM
  3. Reflect TBF vacancies over 2018-2019/2019-2020 budget year
    By jennah63 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-28-2019, 07:32 PM
  4. [SOLVED] excel adding formula malfunction
    By Vit in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-20-2014, 05:24 PM
  5. Excel 2010 copy paste malfunction
    By ek2009 in forum Excel General
    Replies: 2
    Last Post: 04-15-2012, 06:41 PM
  6. Replies: 1
    Last Post: 08-01-2006, 05:30 PM
  7. Help Excel malfunction
    By mingster in forum Excel General
    Replies: 5
    Last Post: 07-21-2006, 04:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1