+ Reply to Thread
Results 1 to 15 of 15

How to replace Offset() with non-volatile formulas?

  1. 10-12-2012, 09:52 PM #1
    ceeyee
    ceeyee is offline
    Registered User
    Join Date
    07-31-2011
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    22

    How to replace Offset() with non-volatile formulas?

    Hi,

    I have a formula

    =Sum(Offset(A100,,,B1))
    where the height in B1 is a user input.

    Is there any efficient way to change the Offset part with non-volatile formulas?
    (I have tens of thousands of these so I need them to be non-volatile to speed up.)

    Thanks.
    Last edited by ceeyee; 10-12-2012 at 10:07 PM.
    Reply With QuoteRegister To Reply
  2. 10-12-2012, 10:01 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,946

    Re: How to replace Offset() with non-volatile formulas?

    not sure if this will help, but try this instead...

    =SUM(INDIRECT("A1:A"&B1))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 10-12-2012, 10:07 PM #3
    ceeyee
    ceeyee is offline
    Registered User
    Join Date
    07-31-2011
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: How to replace Offset() with non-volatile formulas?

    Thanks but Indirect is volatile which is not better.
    Reply With QuoteRegister To Reply
  4. 10-12-2012, 10:13 PM #4
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: How to replace Offset() with non-volatile formulas?

    Use INDEX,

    =SUM(INDEX(A:A,ROW(A100)):INDEX(A:A,ROW(A100)+B1))
    Regards,
    Haseeb Avarakkan
    __________________________________
    "Feedback is the breakfast of champions"
    Reply With QuoteRegister To Reply
  5. 10-12-2012, 10:14 PM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,946

    Re: How to replace Offset() with non-volatile formulas?

    ok, sorry then. if you have a user-input to determine the size of your range, by implication that means that your range will always be volatile
    Reply With QuoteRegister To Reply
  6. 10-12-2012, 10:24 PM #6
    Teethless mama
    Teethless mama is offline
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: How to replace Offset() with non-volatile formulas?

    =sum(a100:index(a100:a1000,b1))
    Reply With QuoteRegister To Reply
  7. 10-12-2012, 10:26 PM #7
    ceeyee
    ceeyee is offline
    Registered User
    Join Date
    07-31-2011
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: How to replace Offset() with non-volatile formulas?

    Quote Originally Posted by Haseeb A View Post
    Use INDEX,

    =SUM(INDEX(A:A,ROW(A100)):INDEX(A:A,ROW(A100)+B1))

    This method is non-volatile but it references to the whole A:A column making it slow.

    Is there a more efficient way to do it?
    Reply With QuoteRegister To Reply
  8. 10-12-2012, 10:28 PM #8
    ceeyee
    ceeyee is offline
    Registered User
    Join Date
    07-31-2011
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: How to replace Offset() with non-volatile formulas?

    Quote Originally Posted by Teethless mama View Post
    =sum(a100:index(a100:a1000,b1))

    Thanks, but B1 can be a small or a very large number overflowing the A1000.
    Reply With QuoteRegister To Reply
  9. 10-12-2012, 10:30 PM #9
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: How to replace Offset() with non-volatile formulas?

    Even if you are referencing to whole column, INDEX will only make a range A100:nputed row
    Reply With QuoteRegister To Reply
  10. 10-12-2012, 11:23 PM #10
    ceeyee
    ceeyee is offline
    Registered User
    Join Date
    07-31-2011
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: How to replace Offset() with non-volatile formulas?

    Quote Originally Posted by Haseeb A View Post
    Even if you are referencing to whole column, INDEX will only make a range A100:nputed row

    What is “nputed” row?

    Is it the number in cell B1 in the above example? or is it the last used row in the sheet?
    Thanks.

    (By the way, would =SUM(A100:INDEX(A:A,ROW(A100)+B1)) make it a little bit simpler in your example?)
    Last edited by ceeyee; 10-13-2012 at 02:15 AM.
    Reply With QuoteRegister To Reply
  11. 12-06-2018, 02:49 PM #11
    Sam1989
    Sam1989 is offline
    Registered User
    Join Date
    08-10-2015
    Location
    N/A
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    14

    Re: How to replace Offset() with non-volatile formulas?

    Hi - I'm trying to replace an OFFSET function with INDEX but my issue is that the OFFSET function is to go and read numbers from left so that's how it looks:

    =OFFSET(H24,0,-$B$27)

    I've been trying to re-write this with INDEX but not sure how to go negative columns?

    Hope this makes sense!
    Reply With QuoteRegister To Reply
  12. 12-06-2018, 04:50 PM #12
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How to replace Offset() with non-volatile formulas?

    Try this.

    =INDEX(24:24,,8-$B$27)
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  13. 12-06-2018, 05:18 PM #13
    Sam1989
    Sam1989 is offline
    Registered User
    Join Date
    08-10-2015
    Location
    N/A
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    14

    Re: How to replace Offset() with non-volatile formulas?

    Thanks Norie. So here's the full story. In Cell H27 I have this formula: =OFFSET(H24,0,-$B$27)*OFFSET(H23,0,-$B$27)*OFFSET(H26,0,-$B$27)
    I replaced it with this formula: =INDEX(24:24,,8-$B$27)*INDEX(23:23,,8-$B$27)*INDEX(26:26,,8-$B$27)
    for reference, I'm setting B27=1
    I'm running this formula from Cell H27 to AH27 but the sum is 0 which is not correct.

    Thanks again!
    Reply With QuoteRegister To Reply
  14. 12-06-2018, 06:05 PM #14
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How to replace Offset() with non-volatile formulas?

    Try replacing 8 with COLUMN().

    =INDEX(24:24,,COLUMN()-$B$27)*INDEX(23:23,,COLUMN()-$B$27)*INDEX(26:26,,COLUMN()-$B$27)
    Reply With QuoteRegister To Reply
  15. 12-06-2018, 06:40 PM #15
    Sam1989
    Sam1989 is offline
    Registered User
    Join Date
    08-10-2015
    Location
    N/A
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    14

    Re: How to replace Offset() with non-volatile formulas?

    That's perfect! Thanks you very much!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1