Macro to calculate slopes of multiple lines and place values into a column

Poweder Dryer.xlsx

Need help creating a Macro that will calculate the slopes (flow rates) of multiple lines and place them in a column. On each of 8 worksheets in one workbook, I have one X data column and forty Y data columns, therefore I need forty slopes for each worksheet. So far I have been creating a graph and manually inputting the slopes into a column but need to automate this process. Please see attached images. For some reason, I am unable to attach an excel file. Your help would be much appreciated.

Take a look at the LINEST function.

What rows / columns contain your Y-values, what rows / columns contain your X-values?

The X's are in the Time (s) column. The Y's are Batch 1 through 40.
the rows start from the first top value until the first 0 value in each column.

The formulas you want are in column BA.

I suggest you read up on the LINEST function; it appears that it can be a big help to you in the line of work you are in. LINEST doesn’t only calculate slope and intercept, but also other metrics associated with the correlation of the points to the equation. It can also be set up to produce the coefficients for higher order polynomials. Do a web search on LINEST and polynomial .

LINEST provides a higher degree of accuracy for coefficients than is provided by the trend line formula provided by the chart. So my numbers are slightly different than yours.

In its simplest form LINEST is =LINEST(y-values, x-values). Y and X must have the same number of data points. LINEST is an array formula so you have to enter it with CTRL-SHIFT-ENTER. Normally you would select the an array of cells and LINEST will fill in the array with the coefficients and other metrics.

Since all you want is slope and slope happens to be the first element of the array, I could use a one by one array: in other words a single cell. I still had to enter it as an array formula.

There is more to this task than just providing the X’s and Y’s. I am assuming that once a value reaches zero, you don’t want to include those other zeros. So we just can’t use rows 4 to 54. We only want to use rows that are non-zero.

Fortunately your data are contiguous descending – there are no “embedded: zeros. So COUNTIF(Range,”>0”) will give me the number of rows I need.

To define the ranges to use in the LINEST command we need to use the OFFSET command.

The offset command for the X-values is: OFFSET($B$4,0,0,COUNTIF($C$4:$C$54,">0"),1)

Here is a blurb on how offset works: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges

OFFSET takes 5 parameters.
- A start cell
- Number of cells to go down
- Number of cells to go right
- Number of rows to return
- Number of columns to return

So in the above command. We start in cell B4, go down zero rows and over zero columns, so we are still at cell B4. From that point we want to look at a range COUNTIF(C4:C54) rows deep and one column wide.

In the case of column C, we only want to look at 35 rows. Other columns compute to different values.

A similar calculation is made for the number of Y’s to look at.

You will note that for every column, the number of X’s to look at depends on the number of non-zero Y’s to look at.

Because the data are and desired results are transposed – the batch data is across the columns and the batch results are down the rows – you just can’t copy and paste the formulas and expect relative addressing to take care of pointing to the correct cells.

I did the first couple of rows in column BA. You need to copy the formula in cell B4 all the way down (Copy and paste, do not drag – you can’t drag array formulas). Then you have to go in cell by cell and change all references to $C in the formula to the column letter associated with the batch. Once you make the correction, confirm the formula with CTRL-SHIFT-ENTER.

LINEST will do the work for you. You don't need VBA for this.

Good luck with your project.

I just noticed something in the wording of your request. COUNTIF($C$4:$C$54,">0") includes all non-zeros. If you want it to include the first zero, change it wherever you see it to COUNTIF($C$4:$C$54,">0") + 1

Thank you so much for your help @dflak. This technique is very helpful in calculating the slope and has taken me a step further into completing my project.
To take it a step further, I would like a situation where I could input my batch data, hit a calculate button and have the slope column automatically populate.
Can I achieve this through the use of a macro?