+ Reply to Thread
Results 1 to 12 of 12

Unexpected (?) behaviour of OFFSET() in array formulas

  1. #1
    vezerid
    Guest

    Unexpected (?) behaviour of OFFSET() in array formulas

    Hi all,

    Ten numbers in A1:A10. Want to experiment with OFFSET in loop formulas,
    so trying the following:
    =SUM(OFFSET(A1, ROW(1:10)-1,0)) (array-entered of course).

    As I understand it, the formula will loop over 1:10 and calculate, in
    each turn, a reference to Ai. Thus, according to the spirit that has
    worked in numerous instances of either SUMPRODUCT() or {SUM()}, namely
    that a computed array is passed as argument, {SUM()} in this case has
    10 cells to sum. Yet, the formula only recognizes the first cell A1.

    Then I try to enter in cells B1:B10 as an array formula
    =OFFSET($A$1,ROW(1:10)-1,0)
    hoping that I will get the mirror of A1:A10. I get #VALUE! in each
    cell. Why am I not getting #VALUE! in the first formula? Seems
    inconsistent to me. At least, if I got a #VALUE! in the first case, I
    could attribute it the the computed array being an array of #VALUE!
    (but it does not behave this way).

    Furthermore, I am trying the more complex variant,
    =SUM(IF(OFFSET(A1,ROW(1:10)-1,0)>3, OFFSET(A1,ROW(1:10)-1,0),0))
    and I am getting #VALUE!

    According to the documentation, OFFSET() will return #VALUE! if the
    first argument is NOT a contiguous range. This is not happening. SUM()
    will of course produce #VALUE! if one of the cells in the range already
    has #VALUE!, but here we have the inconsistent behavior.

    Will someone enlighten please?

    TIA
    Kostis Vezerides


  2. #2
    Ron Coderre
    Guest

    RE: Unexpected (?) behaviour of OFFSET() in array formulas

    As I believe Peo recently explained, in your situation, the OFFSET function
    needs a second evaluation to make it behave properly. Try this:

    =SUM(N(OFFSET(A1, ROW(1:10)-1,0)))
    Commit that array formula by holding down [Ctrl]+[Shift] when you press
    [Enter]

    Does that help?

    ***********
    Regards,
    Ron


    "vezerid" wrote:

    > Hi all,
    >
    > Ten numbers in A1:A10. Want to experiment with OFFSET in loop formulas,
    > so trying the following:
    > =SUM(OFFSET(A1, ROW(1:10)-1,0)) (array-entered of course).
    >
    > As I understand it, the formula will loop over 1:10 and calculate, in
    > each turn, a reference to Ai. Thus, according to the spirit that has
    > worked in numerous instances of either SUMPRODUCT() or {SUM()}, namely
    > that a computed array is passed as argument, {SUM()} in this case has
    > 10 cells to sum. Yet, the formula only recognizes the first cell A1.
    >
    > Then I try to enter in cells B1:B10 as an array formula
    > =OFFSET($A$1,ROW(1:10)-1,0)
    > hoping that I will get the mirror of A1:A10. I get #VALUE! in each
    > cell. Why am I not getting #VALUE! in the first formula? Seems
    > inconsistent to me. At least, if I got a #VALUE! in the first case, I
    > could attribute it the the computed array being an array of #VALUE!
    > (but it does not behave this way).
    >
    > Furthermore, I am trying the more complex variant,
    > =SUM(IF(OFFSET(A1,ROW(1:10)-1,0)>3, OFFSET(A1,ROW(1:10)-1,0),0))
    > and I am getting #VALUE!
    >
    > According to the documentation, OFFSET() will return #VALUE! if the
    > first argument is NOT a contiguous range. This is not happening. SUM()
    > will of course produce #VALUE! if one of the cells in the range already
    > has #VALUE!, but here we have the inconsistent behavior.
    >
    > Will someone enlighten please?
    >
    > TIA
    > Kostis Vezerides
    >
    >


  3. #3
    Biff
    Guest

    Re: Unexpected (?) behaviour of OFFSET() in array formulas

    Hi!

    Although I can't explain the exact technical reason of why it won't work
    like that:

    =SUM(OFFSET(A1, ROW(1:10)-1,0))

    Try including the [height] argument and it will then work:

    =SUM(OFFSET(A1, ROW(1:10)-1,,10))

    For the "mirror" situation, again, I can't explain the technical reason, but
    try:

    =N(OFFSET(A$1,ROW(1:10)-1,,))

    But why would you use that versus:

    =OFFSET(A$1,,,10)

    Biff

    "vezerid" <vezerid@act.edu> wrote in message
    news:1134414592.573441.228410@g44g2000cwa.googlegroups.com...
    > Hi all,
    >
    > Ten numbers in A1:A10. Want to experiment with OFFSET in loop formulas,
    > so trying the following:
    > =SUM(OFFSET(A1, ROW(1:10)-1,0)) (array-entered of course).
    >
    > As I understand it, the formula will loop over 1:10 and calculate, in
    > each turn, a reference to Ai. Thus, according to the spirit that has
    > worked in numerous instances of either SUMPRODUCT() or {SUM()}, namely
    > that a computed array is passed as argument, {SUM()} in this case has
    > 10 cells to sum. Yet, the formula only recognizes the first cell A1.
    >
    > Then I try to enter in cells B1:B10 as an array formula
    > =OFFSET($A$1,ROW(1:10)-1,0)
    > hoping that I will get the mirror of A1:A10. I get #VALUE! in each
    > cell. Why am I not getting #VALUE! in the first formula? Seems
    > inconsistent to me. At least, if I got a #VALUE! in the first case, I
    > could attribute it the the computed array being an array of #VALUE!
    > (but it does not behave this way).
    >
    > Furthermore, I am trying the more complex variant,
    > =SUM(IF(OFFSET(A1,ROW(1:10)-1,0)>3, OFFSET(A1,ROW(1:10)-1,0),0))
    > and I am getting #VALUE!
    >
    > According to the documentation, OFFSET() will return #VALUE! if the
    > first argument is NOT a contiguous range. This is not happening. SUM()
    > will of course produce #VALUE! if one of the cells in the range already
    > has #VALUE!, but here we have the inconsistent behavior.
    >
    > Will someone enlighten please?
    >
    > TIA
    > Kostis Vezerides
    >




  4. #4
    vezerid
    Guest

    Re: Unexpected (?) behaviour of OFFSET() in array formulas

    Ron, Biff,
    thank you both for your answers. I now have a workaround but I still
    cannot understand the behavior. I wonder, is it a glitch or is it
    supposed to be the expected behavior?

    BTW, I searched in .misc with keywords: OFFSET evaluation Peo Sjoblom
    but it returned an irrelevant thread only. Does one of you have the
    link to the thread?

    Regards,
    Kostis Vezerides


  5. #5
    Bob Phillips
    Guest

    Re: Unexpected (?) behaviour of OFFSET() in array formulas

    http://tinyurl.com/73erq

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "vezerid" <vezerid@act.edu> wrote in message
    news:1134418108.009768.104540@g14g2000cwa.googlegroups.com...
    > Ron, Biff,
    > thank you both for your answers. I now have a workaround but I still
    > cannot understand the behavior. I wonder, is it a glitch or is it
    > supposed to be the expected behavior?
    >
    > BTW, I searched in .misc with keywords: OFFSET evaluation Peo Sjoblom
    > but it returned an irrelevant thread only. Does one of you have the
    > link to the thread?
    >
    > Regards,
    > Kostis Vezerides
    >




  6. #6
    Ron Coderre
    Guest

    Re: Unexpected (?) behaviour of OFFSET() in array formulas

    Biff, pretty much summed it up just fine.
    But the reference I recalled can be found if you search the Excel forum for:
    +peo +"match and some other functions"

    ***********
    Regards,
    Ron


    "vezerid" wrote:

    > Ron, Biff,
    > thank you both for your answers. I now have a workaround but I still
    > cannot understand the behavior. I wonder, is it a glitch or is it
    > supposed to be the expected behavior?
    >
    > BTW, I searched in .misc with keywords: OFFSET evaluation Peo Sjoblom
    > but it returned an irrelevant thread only. Does one of you have the
    > link to the thread?
    >
    > Regards,
    > Kostis Vezerides
    >
    >


  7. #7
    Peo Sjoblom
    Guest

    Re: Unexpected (?) behaviour of OFFSET() in array formulas

    There was a dsicussion using indirect some years ago which pertains to the
    same behaviour as offset where to be able to evaluate the array you need to
    do it twice, here's a link to that discussion

    http://tinyurl.com/7umpp

    note that apart from N a double SUM like in

    =SUM(SUM(OFFSET(A1, ROW(1:10)-1,0)))

    or

    =SUMPRODUCT(SUM(OFFSET(A1, ROW(1:10)-1,0)))

    (entered normally)

    and also TRANSPOSE

    =SUM(TRANSPOSE(OFFSET(A1, ROW(1:10)-1,0)))

    will work, I have put the question aside into the strange Excel behaviour
    vault


    --

    Regards,

    Peo Sjoblom

    "vezerid" <vezerid@act.edu> wrote in message
    news:1134418108.009768.104540@g14g2000cwa.googlegroups.com...
    > Ron, Biff,
    > thank you both for your answers. I now have a workaround but I still
    > cannot understand the behavior. I wonder, is it a glitch or is it
    > supposed to be the expected behavior?
    >
    > BTW, I searched in .misc with keywords: OFFSET evaluation Peo Sjoblom
    > but it returned an irrelevant thread only. Does one of you have the
    > link to the thread?
    >
    > Regards,
    > Kostis Vezerides
    >




  8. #8
    vezerid
    Guest

    Re: Unexpected (?) behaviour of OFFSET() in array formulas

    Thanks everybody for the replies. Several issues were clarified
    reading the older threads.

    I have a question to all of you: Are you using some special software
    for archiving the messages? All of you often come up with links to very
    specific threads. How do you do it? Do you have your own database, in
    which you record the URL's with some keywords, is there a front end
    which allows management of information such as this? Or is it simply
    that you use smarter searches?

    Thanks anyway,
    Kostis Vezerides


  9. #9
    Peo Sjoblom
    Guest

    Re: Unexpected (?) behaviour of OFFSET() in array formulas

    Smarter searches? <bg> I use advanced google searches plus my memory, Ron De
    Bruin has an excel add-in that will do google searches

    http://www.rondebruin.nl/Google.htm




    --

    Regards,

    Peo Sjoblom


    "vezerid" <vezerid@act.edu> wrote in message
    news:1134499312.614476.22290@g44g2000cwa.googlegroups.com...
    > Thanks everybody for the replies. Several issues were clarified
    > reading the older threads.
    >
    > I have a question to all of you: Are you using some special software
    > for archiving the messages? All of you often come up with links to very
    > specific threads. How do you do it? Do you have your own database, in
    > which you record the URL's with some keywords, is there a front end
    > which allows management of information such as this? Or is it simply
    > that you use smarter searches?
    >
    > Thanks anyway,
    > Kostis Vezerides
    >




  10. #10
    Bob Phillips
    Guest

    Re: Unexpected (?) behaviour of OFFSET() in array formulas

    Same as Peo, but I also save some of the KB entries and better replies in MS
    Code Librarian.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "vezerid" <vezerid@act.edu> wrote in message
    news:1134499312.614476.22290@g44g2000cwa.googlegroups.com...
    > Thanks everybody for the replies. Several issues were clarified
    > reading the older threads.
    >
    > I have a question to all of you: Are you using some special software
    > for archiving the messages? All of you often come up with links to very
    > specific threads. How do you do it? Do you have your own database, in
    > which you record the URL's with some keywords, is there a front end
    > which allows management of information such as this? Or is it simply
    > that you use smarter searches?
    >
    > Thanks anyway,
    > Kostis Vezerides
    >




  11. #11
    Bob Phillips
    Guest

    Re: Unexpected (?) behaviour of OFFSET() in array formulas

    Oh, and of course, if you have been around here a while, a few things will
    stick in your memory, which will help make a smarter search. Sometimes I
    remember a keyword, or even better, a poster.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "vezerid" <vezerid@act.edu> wrote in message
    news:1134499312.614476.22290@g44g2000cwa.googlegroups.com...
    > Thanks everybody for the replies. Several issues were clarified
    > reading the older threads.
    >
    > I have a question to all of you: Are you using some special software
    > for archiving the messages? All of you often come up with links to very
    > specific threads. How do you do it? Do you have your own database, in
    > which you record the URL's with some keywords, is there a front end
    > which allows management of information such as this? Or is it simply
    > that you use smarter searches?
    >
    > Thanks anyway,
    > Kostis Vezerides
    >




  12. #12
    vezerid
    Guest

    Re: Unexpected (?) behaviour of OFFSET() in array formulas

    Thanks for the tip, Peo.

    Regards
    Kostis Vezerides


+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1