I have a column of numbers (created by formulas) that I now want to have another column where I can display the value of every 5th row. I have searched and only find formula's for Excel which when I use one I get a syntax error. Can Numbers even do this?
Mac Pro (2019)
One way is to use OFFSET, like this:
In B2, filled down:
=IFERROR(OFFSET($A$2, (ROW()−2)*5, 0),"")
If you don't have a Header Row in the table then you could use:
=IFERROR(OFFSET($A$1, (ROW()−1)*5, 0),"")
The IFERROR is cosmetic, to prevent out of range warning triangles.
SG
One way is to use OFFSET, like this:
In B2, filled down:
=IFERROR(OFFSET($A$2, (ROW()−2)*5, 0),"")
If you don't have a Header Row in the table then you could use:
=IFERROR(OFFSET($A$1, (ROW()−1)*5, 0),"")
The IFERROR is cosmetic, to prevent out of range warning triangles.
SG
Greyghostturbo wrote:
mine began with it selected a row 29 rows below.
Why would you do that? In Numbers you typically have multiple smaller tables on a sheet rather than one big grid of cells as you have in Excel. (see the built-in templates at File > New for examples). So quite often you would start on the second row as in my example.
Perhaps you could post a screenshot of your table showing where your data is and where you want the formula to go?
shift-command-4, drag to select area, release, start new post here and use the mountains-and-moon 'Image insertion' icon beneath the compose window to include the screenshot from the Desktop.
SG
Thank you, I inserted the formula noted above changing A2 to my first data point in the column. Rather than begin with the first data point row mine began with it selected a row 29 rows below. I tried both formulas with the same response. Do you have any thoughts? thanks again
Thanks for your help. I am attaching screenshots showing how using the formula which I start with row 6 (I changed the formula to accommodate this and then see the result from row 31. The resulting values for the two columns is correctly matched, it's just they do not start 5 rows down.
Try varying the value after the -, e.g. instead of -2 maybe -6?
However, if I were you I would not have all those (apparently) blank rows and columns. And I'd put the formula in a separate table rather than on the same big grid as the other values the way you might do in Excel.
SG
I do now also see the OFFSET parameters need to be changed.
Thanks so much for engaging on this post. I have learned a new formula. I did make the change to -6 and all fell into place.
Selecting every 5th value in a column
