Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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" wrote in message oups.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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unexpected (?) behaviour of OFFSET() in array formulas
http://tinyurl.com/73erq
-- HTH RP (remove nothere from the email address if mailing direct) "vezerid" wrote in message oups.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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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" wrote in message oups.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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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" wrote in message ups.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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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" wrote in message ups.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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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" wrote in message ups.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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unexpected (?) behaviour of OFFSET() in array formulas
Thanks for the tip, Peo.
Regards Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OFFSET and array formulae | Excel Discussion (Misc queries) | |||
Using wild card characters in array formulas | Excel Worksheet Functions | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |