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

Discuss the spreadsheet application

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

Postby mxdog » Sat Nov 23, 2019 9:45 pm

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

Postby FJCC » Sat Nov 23, 2019 10:36 pm

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
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7492
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby mxdog » Sat Nov 23, 2019 11:16 pm

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

Postby mxdog » Sat Nov 23, 2019 11:52 pm

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


Return to Calc

Who is online

Users browsing this forum: No registered users and 13 guests