Forum Discussion
Solved
Change the row-to-row increment value when copying down a column, from +1 to +300
My formula in cell H5 is "=SLOPE('Data 300 Counts'!G903:G1200, 'Laser Pixel Data'!$H$3:$H$300)" I want to copy this down the column so that H6 is "=SLOPE('Data 300 Counts'!G1203:G1500, 'Laser Pixe...
The idea is
=SLOPE(INDEX('Data 300 Counts'!G:G,903+300**(ROW()-ROW($A$5))):INDEX('Data 300 Counts'!G:G,1200+300**(ROW()-ROW($A$5))), 'Laser Pixel Data'!$H$3:$H$300)
One way of achieving this is to use OFFSET
= SLOPE( OFFSET( CountsBlock, 300*k, 0 ), PixelData )
where 'k' is a relative reference to the helper column {0,1,2, …} and 'CountsBlock' is the first block
='Data 300 Counts'!G903:G1200.
Other options include using INDEX:INDEX for a non-volatile solution or cobbling something together using ROW to give a formula for the sequence 'k'.
- Thank you for the response. I got it to do what I needed.
This is my first experience attempting to use expressions to fill in the inputs into other functions. I've always been able to just do the smaller operations in their own cells, and then piece them together, but I had so many iterations this time it would have taken too long to construct them all that way.
