Apr 25 2019 12:22 PM
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 Pixel Data'!$H$3:$H$300)
...and H7 is "=SLOPE('Data 300 Counts'!G1503:G1800, 'Laser Pixel Data'!$H$3:$H$300)
etc etc
The auto-increment behavior is to make the H6 range be G904:G1201, incrementing the values by one each time. I need the increment value to be +300 rather than +1.
Thank you.
Apr 25 2019 01:17 PM
Solution
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)
Apr 25 2019 01:23 PM
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'.
Apr 25 2019 02:18 PM
Apr 25 2019 02:19 PM
Apr 25 2019 02:25 PM
@Craig_Gallagher , you are welcome. That was misprint in the formula like 300**( instaed of 300*(
Sorry for that, mistake with copy/paste.
Apr 25 2019 02:48 PM
Dec 04 2019 11:52 AM
@Sergei BaklanI am working on a similar issue that is driving me nuts. I am trying to copy the formula below across many columns. This is a complex spreadsheet where we are referencing data vertically down through many rows, and then creating a horizontal map across many columns. What I basically need to do is increment the F30 to F31, F32, F33, etc... as the formula is copied across. With no dollar sign, the rows increment from F30 to G30, H30, etc., and with the dollar sign in place before F30, it freezes the F30 so that it shows up in each formula. The H15 is incrementing as it should to I15, J15, K15, etc...
=IF(ISERROR($F30),"",VLOOKUP(H15,$B$29:$F$9999,5,FALSE))
I would really appreciate any help you can provide
Apr 25 2019 01:17 PM
Solution
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)