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

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

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 ?
Last edited by mxdog on Sat Nov 23, 2019 11:27 pm, edited 2 times in total.
OpenOffice 4.1.5
mxdog

Posts: 3
Joined: Sat Nov 23, 2019 9:22 pm

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

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)`
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7494
Joined: Sat Nov 08, 2008 8:08 pm

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

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.
OpenOffice 4.1.5
mxdog

Posts: 3
Joined: Sat Nov 23, 2019 9:22 pm

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

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.......
OpenOffice 4.1.5
mxdog

Posts: 3
Joined: Sat Nov 23, 2019 9:22 pm

Who is online

Users browsing this forum: MSN [Bot] and 7 guests