Automate calculation of multiple slopes from data set

Hi there. I'm doing some analysis on some data from a study we recently conducted, and we're trying to figure out a way to calculate the slopes of several different trends from one big data set. If there's a way to automate this process in Excel it'll make my life A LOT easier. Attached is the data output we get from a program we use to monitor and analyze blood pressure and heart rate during an exercise test. What we are attempting to do is calculate the slope of several sequences of varying durations. For instance, I've put a square around a few of the different sequences. Each sequence is distinguished in column G by Group. The durations are NOT equal lengths, as you can see by columns D, E, and F. Most span roughly 4 heart beats, but some are longer (like the 9 beat sequence that is circled in black).

Is there a way to write a formula that will select each sequence (or group) based on criteria and then calculate the slope of that group? In this case, I was thinking that perhaps we could automate the process by getting a formula to select a whole "Group" from column G (like group #1 for example) and then find the slope based on plotting "Column B - R-R Interval" vs "Column A - Systolic BP" from the corresponding rows within that group. We do not need an actual plot - just the slope and r-squared value if possible.

We have roughly 100 files of this nature, so manually doing it would become cumbersome very quickly. We have figured out a coding on R Studio that automates the slope calculation, but if any of you know of a way to do this on Excel, your help would be greatly appreciated.

Thanks!

BRS in Excel - Screenshot.png

try this array-formula in cell H2 to compute Slope.

Formula:

Please Login or Register  to view this content.

To compute R-Square, try this Array Formula:

Formula:

Please Login or Register  to view this content.

Note: Array Formula must be entered/confirmed with [Ctrl]+[Shift]+[Enter]
Do still upload a sample workbook so the forum members can advise simpler and better solutions/alternatives.
Cheers!

If the group number is in J2, this will return the slope of that group.

=SLOPE(OFFSET(INDEX(B:B, MATCH(J2, G:G, 0), 1),0,0,COUNTIF(G:G, J2)), OFFSET(INDEX(A:A, MATCH(J2, G:G, 0),1),0,0, COUNTIF(G:G, J2)))

Is your data separated into negatives and positives or do you want that done as well?

Is every worksheet named: "All" that contains every datapoint in every workbook that contains an output?

You could put this in H2 and drag down

=IF(G1<>G2, SLOPE(OFFSET(INDEX(B:B,MATCH(G2,G:G,0),1),0,0,COUNTIF(G:G,G2)),OFFSET(INDEX(A:A,MATCH(G2,G:G,0),1),0,0,COUNTIF(G:G,G2))), "")