Find slope where changes in X are dates

**lilybickel** · 04-29-2021, 05:59 AM

Hi everyone,
Is it possible to find a gradient where the changes in X are dates?

I have attached an example database to show the set up I am working with: essentially I need the green section at the end of the table to output the gradients of change in Variables A, C, and E between Date X and Date Y or Date Z (only if there is no Date Y) for each row.

Date 1 corresponds to Variable-A 1, Variable-C 1 and Variable-E 1 and Date 2 corresponds to Variable-A 2, Variable-C 2 and Variable-E 2 etc.
For all Dates 1-5 that are >= Date X and <= Date Y then I want both the dates and corresponding variables to be included in a calculation that will find the gradient of each variable's change in relation to the dates they were recorded.

Any ideas on how to do this??
Thanks

**6StringJazzer** · 04-29-2021, 09:37 AM

I think we need to clarify terminology, and also clarify what result you are looking for.

Your title says "slope" but then you refer to "gradients." They are not the same. Slope is the measure of how steeply a line rises, and gradient is a much more complex concept in n-space involving partial-derivative calculus.

Second, you have boiled out the context of what you are doing so much that it's hard to tell what you need and why, and therefore the best way to get it. A set of data points, taken by themselves, do not have a slope. A straight line has a slope. Are you asking to determine a linear trendline for the data points for a particular variable, and then determine the slope of that trendline? For example, let's look at your data for Variable A on the first row. You seem to be concerned with one row at a time. This is evidently fake data, and does result in very nearly a straight line. If we extract just the Variable A data, get the trend, then calculate slope, we get this:

Values as displayed

	A	B	C	D
1	Date	Variable-A	Linear Regression	Slope
2	1/1/2019	100	100.0626258	-0.3343771
3	2/1/2019	90	89.69693581
4	3/1/2019	80	80.3343771
5	4/1/2019	70	69.96868709
6	5/1/2019	60	59.93737419

Underlying formulas

	A	B	C	D
1	Date	Variable-A	Linear Regression	Slope
2	43466	100	=TREND(B2:B6,A2:A6)	=(C6-C2)/(A6-A2)
3	43497	90
4	43525	80
5	43556	70
6	43586	60

The slope means that the value of the variable decreases -0.334377097 each day along the trendline.

I have no idea if that is what you want.

All that being said, the organization of your data makes this a difficult problem for Excel. It would be better to have one row for each date, rather than all dates in the same row. Do the multiple rows represent multiple trials, multiple subjects, or something else? It would help to have the bigger context of what you are doing.

**lilybickel** · 04-29-2021, 10:58 AM

Thanks for taking the time to reply...
I didn't realise gradient has a different meaning, you are right I'm just after the slope. I recognise that the data is organised badly for this type of calculation, however it's not really realistic to rearrange it. Each row represents the data from a different person (in reality there are 1000s) who has carried out a test multiple times, obtaining 5 results (A-E) each time. I have only filled in example data for the columns A,C, and E as these are the only ones I am interested in finding a slope for the data sets of, however I included the other columns in case these had to be taken into account when doing the calculation.
Dates X,Y and Z represent variables occurring in the testing timeframe; every person has variables X and Z but only some have variable Y. I am only interested in looking at the slope of the data after Date X and before Date Y (if Date Y is included) or after Date X and before Date Z (if Date Y is not included).
I was thinking something along the lines of having a VBA code to identify all the test dates between DateX and DateY, or DateX and DateZ and their corresponding values (ie. for dataset A that would be the values in cells offset (0, -5) from the date), and then somehow having that data used in the slope function to find the amount each person's result changes in the timeframe frame of the tests between variables X and Y/Z.
I don't know if that has explained it any better? I am busy few days so will get back to you after the bank holiday with another example spreadsheet that hopefully makes some more sense in terms of what I'm after!

**6StringJazzer** · 04-29-2021, 11:26 AM

Originally Posted by lilybickel

having that data used in the slope function to find the amount each person's result changes in the timeframe frame of the tests between variables X and Y/Z.

The SLOPE function gives the slope through a linear regression, which is basically what I did above in two steps instead of going straight to SLOPE (same result).

However, this is not "the amount each person's result changes." It is the rate of change--the average change per day. Is that what you want?

I will take a closer look to see how to do this with VBA. It might be possible to do only with formulas, but I'm not positive.

**lilybickel** · 05-07-2021, 06:22 AM

Yes I am after the average change per day not the total amount each person's result changes, sorry my wording is ambiguous :/
I have made a new example to hopefully illustrate better what I am thinking.
In reality we have >1000 people and up to 100 tests per person rather than 5 (I only included 5 to try and simplify the layout) so I am not sure if a formula would be realistic to use for the larger data sets?
the example workings that I have included are only for working out the slope of ResutA so I have greyed out the other results. The data that is relevant to the calculations (ie. falls within the desired date ranges of DateX-DateY / DateX-DateZ) is in black and the data that is irrelevant to the calculation has been coloured red. In the first table I have calculated the average of the relevant ResultA data using a UDF names "ResultAav" to give you an idea of what I'm thinking. I have tried to then adapt this to work out a median and slope of the data (UDFs named "ResultAmed" and "ResultAslope") however both of these come out with a #Value! error. I only included the workings for the average and median as steps for getting to the slope - I'm not sure why the median function isn't working but the slope function is the one I am kind of guessing at how to code for at the moment. I don't know if these might help or not?
I also manually calculated the Slope using the grid below. I did change out the dates for the the relative number of days from the first test of each person as using dates for the 'change in X' threw me off a bit. Essentially I just used the grid to find the Slope of the relevant data.
In the tables below I did a similar thing but used formulas to populate the calculation grid with only the relevant data so that it hopefully illustrates with formulas what I am after from the VBA.
I hope this makes sense, please ask any more questions and let me know if there would be an easier way to do it? Thank you!