Use of Offset function in array formula
 

I am struggling trying to get offset to work in an array formula.

Let me give some background. My spreadsheet has a matrix portion of it
where 3 categories of data are stored for each of 10 weeks. So there are 30
columns of data. I have been using array formulas in order to sum the like
columns since each sum should only count 10 of the 30 columns. Everything
works great with that. I have one more calculation I am trying to do though
where I am struggling.

Essentially i want the formula to look in a cell, use the value stored there
as the row offset data in an OFFSET function to retrieve another value and
then have the array function sum that. The offset function seems to fail
whenever the formula is entered as an array formula. As best I can tell, the
issue is that the row offset reference in the OFFSET function is now a range
(like K21:AN21) instead of a single cell. My hope was the array function
would cycle the cell entries just like it does for other portions of the
formula. It appears that the array formula approach is not working to get it
to cycle or something.

Any suggestions? I would really like to avoid having intermediate
calculations on the spreadsheet. My current workaround is to have the offset
lookups performed on a range of hidden cells and I sum those. But that is
certainly less than elegant. Am I trying to go too far with array formulas
here?

Use of Offset function in array formula
 

I usually prefer Index over Offset.

Post your formulas.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"scabHead" wrote in message
...
I am struggling trying to get offset to work in an array formula.

Let me give some background. My spreadsheet has a matrix portion of it
where 3 categories of data are stored for each of 10 weeks. So there are 30
columns of data. I have been using array formulas in order to sum the like
columns since each sum should only count 10 of the 30 columns. Everything
works great with that. I have one more calculation I am trying to do though
where I am struggling.

Essentially i want the formula to look in a cell, use the value stored there
as the row offset data in an OFFSET function to retrieve another value and
then have the array function sum that. The offset function seems to fail
whenever the formula is entered as an array formula. As best I can tell,
the
issue is that the row offset reference in the OFFSET function is now a range
(like K21:AN21) instead of a single cell. My hope was the array function
would cycle the cell entries just like it does for other portions of the
formula. It appears that the array formula approach is not working to get
it
to cycle or something.

Any suggestions? I would really like to avoid having intermediate
calculations on the spreadsheet. My current workaround is to have the
offset
lookups performed on a range of hidden cells and I sum those. But that is
certainly less than elegant. Am I trying to go too far with array formulas
here?

Use of Offset function in array formula
 

I have tried various approaches but not sure how to make it work with index
either. Tell you what, what approach would use for the following scenario?

Cells A2-A4 have 10, 7, 5 respectively stored in them. Consider that
columns B-D are exact duplicates.

Now A1-D1 have the values of 1, 0, 5, 2 respectively. Can you make an array
function that will read the values in A1-D1, look up the corresponding
entries in a table and then sum them? The answer should compute to 17 as the
0 and 5 entries should be trapped as they are outside the dimensions of the
"lookup table"

(This is a greatly simplified version of what I am trying to do. The
biggest simplification is that the columns are consecutive. In my real world
spreadsheet every third column is what I will be doing this on, which greatly
increases the complexity of the array function. The other simplification is
that the values in the "lookup table" are not pre-defined as they are in this
case. There are other calculations involved.)


"RagDyeR" wrote:

I usually prefer Index over Offset.

Post your formulas.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"scabHead" wrote in message
...
I am struggling trying to get offset to work in an array formula.

Let me give some background. My spreadsheet has a matrix portion of it
where 3 categories of data are stored for each of 10 weeks. So there are 30
columns of data. I have been using array formulas in order to sum the like
columns since each sum should only count 10 of the 30 columns. Everything
works great with that. I have one more calculation I am trying to do though
where I am struggling.

Essentially i want the formula to look in a cell, use the value stored there
as the row offset data in an OFFSET function to retrieve another value and
then have the array function sum that. The offset function seems to fail
whenever the formula is entered as an array formula. As best I can tell,
the
issue is that the row offset reference in the OFFSET function is now a range
(like K21:AN21) instead of a single cell. My hope was the array function
would cycle the cell entries just like it does for other portions of the
formula. It appears that the array formula approach is not working to get
it
to cycle or something.

Any suggestions? I would really like to avoid having intermediate
calculations on the spreadsheet. My current workaround is to have the
offset
lookups performed on a range of hidden cells and I sum those. But that is
certainly less than elegant. Am I trying to go too far with array formulas
here?

Use of Offset function in array formula
 

Oops, I should have been clear and stated that the columns of 10, 7, 5 are to
represent the lookup table for each value in row 1. So if a 1 is in row 1,
return 10, if a 3 is in row 1, return 5. In the real world scenario though I
have variable numbers from column to column.

"scabHead" wrote:

I have tried various approaches but not sure how to make it work with index
either. Tell you what, what approach would use for the following scenario?

Cells A2-A4 have 10, 7, 5 respectively stored in them. Consider that
columns B-D are exact duplicates.

Now A1-D1 have the values of 1, 0, 5, 2 respectively. Can you make an array
function that will read the values in A1-D1, look up the corresponding
entries in a table and then sum them? The answer should compute to 17 as the
0 and 5 entries should be trapped as they are outside the dimensions of the
"lookup table"

(This is a greatly simplified version of what I am trying to do. The
biggest simplification is that the columns are consecutive. In my real world
spreadsheet every third column is what I will be doing this on, which greatly
increases the complexity of the array function. The other simplification is
that the values in the "lookup table" are not pre-defined as they are in this
case. There are other calculations involved.)


"RagDyeR" wrote:

I usually prefer Index over Offset.

Post your formulas.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"scabHead" wrote in message
...
I am struggling trying to get offset to work in an array formula.

Let me give some background. My spreadsheet has a matrix portion of it
where 3 categories of data are stored for each of 10 weeks. So there are 30
columns of data. I have been using array formulas in order to sum the like
columns since each sum should only count 10 of the 30 columns. Everything
works great with that. I have one more calculation I am trying to do though
where I am struggling.

Essentially i want the formula to look in a cell, use the value stored there
as the row offset data in an OFFSET function to retrieve another value and
then have the array function sum that. The offset function seems to fail
whenever the formula is entered as an array formula. As best I can tell,
the
issue is that the row offset reference in the OFFSET function is now a range
(like K21:AN21) instead of a single cell. My hope was the array function
would cycle the cell entries just like it does for other portions of the
formula. It appears that the array formula approach is not working to get
it
to cycle or something.

Any suggestions? I would really like to avoid having intermediate
calculations on the spreadsheet. My current workaround is to have the
offset
lookups performed on a range of hidden cells and I sum those. But that is
certainly less than elegant. Am I trying to go too far with array formulas
here?

Use of Offset function in array formula
 

It's hard to understand what you want to do, although it sounds a bit like a
SUMIF - you're summing one column based on the value in another but the
column to be summed is variable?

Perhaps that's a bit simplistic but just to start the ball rolling then,
using INDEX as RagDyeR suggests

=SUMIF(A2:A100,"x",INDEX(B2:AE100,0,MATCH("y",B1:A E1,0)))

this will sum a variable column (based on matching a value in the header -
"y") when column A contains "x".

Is that close to what you want or nowhere?

"scabHead" wrote:

I am struggling trying to get offset to work in an array formula.

Let me give some background. My spreadsheet has a matrix portion of it
where 3 categories of data are stored for each of 10 weeks. So there are 30
columns of data. I have been using array formulas in order to sum the like
columns since each sum should only count 10 of the 30 columns. Everything
works great with that. I have one more calculation I am trying to do though
where I am struggling.

Essentially i want the formula to look in a cell, use the value stored there
as the row offset data in an OFFSET function to retrieve another value and
then have the array function sum that. The offset function seems to fail
whenever the formula is entered as an array formula. As best I can tell, the
issue is that the row offset reference in the OFFSET function is now a range
(like K21:AN21) instead of a single cell. My hope was the array function
would cycle the cell entries just like it does for other portions of the
formula. It appears that the array formula approach is not working to get it
to cycle or something.

Any suggestions? I would really like to avoid having intermediate
calculations on the spreadsheet. My current workaround is to have the offset
lookups performed on a range of hidden cells and I sum those. But that is
certainly less than elegant. Am I trying to go too far with array formulas
here?