Page 1 of 1

[Solved] Function to get a series of cells with an offset

PostPosted: Sat Nov 23, 2019 9:45 pm
by mxdog
This has probably been asked before but I didn't find it or a good way to do it.

I have a sheet I want to get get the values from a range of cells that are offset 10 rows in this case all in the same column
I thought the offset function would do this but I can not get it figured out.
So is there was way to do this other then...

=A10+A20+A30+A40+A50+A60+A70+A80+A90+A100+A?...........

I thought it would be along the lines of =sum(a10:offset(10,10))
conceptually anyway with A10 being the start the 1st 10 being the offset and the 2nd 10 being how many elements
but the actual offset doesn't look like it works that way i.e no way to assign how many elements
OFFSET(reference; row_offset; col_offset; new_height; new_width)

any ideas how I can do this ?

Re: Is there a function to get a series of cells with an off

PostPosted: Sat Nov 23, 2019 10:36 pm
by FJCC
This formula will return the sum of A10 + A20 + A30 + A40
Code: Select all   Expand viewCollapse view
=SUMPRODUCT(A1:A41;MOD(ROW(A1:A41);10) = 0)

Re: Is there a function to get a series of cells with an off

PostPosted: Sat Nov 23, 2019 11:16 pm
by mxdog
Wow, no wonder I never got that figured out...not at all how I pictured a function like that would work.

Thank you much that will simplify things immensely for me.

Re: Is there a function to get a series of cells with an off

PostPosted: Sat Nov 23, 2019 11:52 pm
by mxdog
I was thinking about this and it does solve my immediate problem but the question is still valid I think.

I will look more into the other array functions down the line. Summing them will take care of 99% of what I need the ability for anyway.

I was thinking along the lines of what if the cells where text and I wanted to build a string from the contents every n'th cell.......