SOLVED
Home

Change the row-to-row increment value when copying down a column, from +1 to +300

%3CLINGO-SUB%20id%3D%22lingo-sub-482966%22%20slang%3D%22en-US%22%3EChange%20the%20row-to-row%20increment%20value%20when%20copying%20down%20a%20column%2C%20from%20%2B1%20to%20%2B300%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482966%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20formula%20in%20cell%20H5%20is%20%22%3DSLOPE('Data%20300%20Counts'!G903%3AG1200%2C%20'Laser%20Pixel%20Data'!%24H%243%3A%24H%24300)%22%3CBR%20%2F%3E%3CBR%20%2F%3EI%20want%20to%20copy%20this%20down%20the%20column%20so%20that%20H6%20is%20%22%3DSLOPE('Data%20300%20Counts'!G1203%3AG1500%2C%20'Laser%20Pixel%20Data'!%24H%243%3A%24H%24300)%3CBR%20%2F%3E...and%20H7%20is%20%22%3DSLOPE('Data%20300%20Counts'!G1503%3AG1800%2C%20'Laser%20Pixel%20Data'!%24H%243%3A%24H%24300)%3CBR%20%2F%3Eetc%20etc%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20auto-increment%20behavior%20is%20to%20make%20the%20H6%20range%20be%20G904%3AG1201%2C%20incrementing%20the%20values%20by%20one%20each%20time.%26nbsp%3B%20I%20need%20the%20increment%20value%20to%20be%20%2B300%20rather%20than%20%2B1.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-482966%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-483098%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20the%20row-to-row%20increment%20value%20when%20copying%20down%20a%20column%2C%20from%20%2B1%20to%20%2B300%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-483098%22%20slang%3D%22en-US%22%3ENo%20trouble!%20I%20found%20it%20pretty%20quickly.%20You%20got%20me%2099.9999%25%20of%20the%20way%20there.%20Thanks%20again.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-483045%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20the%20row-to-row%20increment%20value%20when%20copying%20down%20a%20column%2C%20from%20%2B1%20to%20%2B300%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-483045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F326557%22%20target%3D%22_blank%22%3E%40Craig_Gallagher%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome.%20That%20was%20misprint%20in%20the%20formula%20like%20300**(%20instaed%20of%20300*(%3C%2FP%3E%0A%3CP%3ESorry%20for%20that%2C%20mistake%20with%20copy%2Fpaste.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-483042%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20the%20row-to-row%20increment%20value%20when%20copying%20down%20a%20column%2C%20from%20%2B1%20to%20%2B300%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-483042%22%20slang%3D%22en-US%22%3EThank%20you%20for%20the%20response.%20I%20got%20it%20to%20do%20what%20I%20needed.%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20is%20my%20first%20experience%20attempting%20to%20use%20expressions%20to%20fill%20in%20the%20inputs%20into%20other%20functions.%20I've%20always%20been%20able%20to%20just%20do%20the%20smaller%20operations%20in%20their%20own%20cells%2C%20and%20then%20piece%20them%20together%2C%20but%20I%20had%20so%20many%20iterations%20this%20time%20it%20would%20have%20taken%20too%20long%20to%20construct%20them%20all%20that%20way.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-483040%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20the%20row-to-row%20increment%20value%20when%20copying%20down%20a%20column%2C%20from%20%2B1%20to%20%2B300%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-483040%22%20slang%3D%22en-US%22%3EYes%2C%20that%20did%20the%20trick%2C%20thank%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-483001%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20the%20row-to-row%20increment%20value%20when%20copying%20down%20a%20column%2C%20from%20%2B1%20to%20%2B300%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-483001%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F326557%22%20target%3D%22_blank%22%3E%40Craig_Gallagher%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20way%20of%20achieving%20this%20is%20to%20use%20OFFSET%3C%2FP%3E%3CP%3E%3D%20SLOPE(%20OFFSET(%20CountsBlock%2C%20300*k%2C%200%20)%2C%20PixelData%20)%3C%2FP%3E%3CP%3Ewhere%20'k'%20is%20a%20relative%20reference%20to%20the%20helper%20column%20%7B0%2C1%2C2%2C%20%E2%80%A6%7D%20and%20'CountsBlock'%20is%20the%20first%20block%3C%2FP%3E%3CP%3E%3D%3CSPAN%3E'Data%20300%20Counts'!G903%3AG1200.%26nbsp%3B%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EOther%20options%20include%20using%20INDEX%3AINDEX%20for%20a%20non-volatile%20solution%20or%20cobbling%20something%20together%20using%20ROW%20to%20give%20a%20formula%20for%20the%20sequence%20'k'.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482995%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20the%20row-to-row%20increment%20value%20when%20copying%20down%20a%20column%2C%20from%20%2B1%20to%20%2B300%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482995%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F326557%22%20target%3D%22_blank%22%3E%40Craig_Gallagher%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20idea%20is%3C%2FP%3E%0A%3CPRE%3E%3DSLOPE(INDEX('Data%20300%20Counts'!G%3AG%2C903%2B300**(ROW()-ROW(%24A%245)))%3AINDEX('Data%20300%20Counts'!G%3AG%2C1200%2B300**(ROW()-ROW(%24A%245)))%2C%20'Laser%20Pixel%20Data'!%24H%243%3A%24H%24300)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Craig_Gallagher
Occasional Contributor

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.

6 Replies
Solution

@Craig_Gallagher ,

 

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)

@Craig_Gallagher 

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'.

Highlighted
Yes, that did the trick, thank you.
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.

@Craig_Gallagher , you are welcome. That was misprint in the formula like 300**( instaed of 300*(

Sorry for that, mistake with copy/paste.

No trouble! I found it pretty quickly. You got me 99.9999% of the way there. Thanks again.
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies