Formula Help (display every 18th value on a new column)

[juliorevka]

I have a table with a column called Item Count that contains many values. I need to display every 18th value; i.e., I need to display F2, F20, F39, etc. on a new column under column K. How do I do this?

[NBVC]

Try:

=INDEX(F:F,18*(ROWS($A$1:A1)-1)+2)

[dilipandey]

Hi juliorevka,

Use following formula to pick up 18 value in column K from column F:-

Please Login or Register  to view this content.

Regards,
DILIPandey

<click on below 'star' if this helps>

[juliorevka]

Never mind I figured it out... Thanks!

If I enter new values into the Fth row, how do I make it automatically enter every 18th value into the Kth row?

[NBVC]

Just put the formula in one cell, then copy the formula down, by clicking and dragging the little black square at bottom right corner of the cell.

[juliorevka]

Can someone explain either the formula
"=OFFSET($F$1,18*ROW(F1)-18+1,0)"
or
"=INDEX(F:F,18*(ROWS($A$1:A1)-1)+2)"

to me, so I can apply it to different scenarios?

[dilipandey]

Hi juliorevka,

Below is the explanation of the formula which I have suggested: "=OFFSET($F$1,18*ROW(F1)-18+1,0)"

It is offsetting (moving) F1, to "18*ROW(F1)-18+1" every time you drag the formula down and picking up the respective value from Column F1. Below is the explanation of this movement:-

1st drag:-

18*ROW(F1)-18+1
18*1-18+1
18-18+1
0+1
1

2nd drag:-

18*ROW(F2)-18+1
18*2-18+1
36-18+1
18+1
19

3rd drag:-

18*ROW(F3)-18+1
18*3-18+1
54-18+1
36+1
37

--
--
--
and so on...

so final results are 1, 19, 37 and each has a gap of 18

I hope this clears your confusion on this formula.

Regards,
DILIPandey

<click on below 'star' if this helps>