Slope calculation : Find last valid number automatically

Hallo everyone,

I am trying to do following operation. Kindly look at the attached excel sheet for simple example.

It is regarding "Slope" calculation of line automatically depending on length of the data (that also considers the last valid number)
I need a single formula, which obeys all the 5 cases shown in "colored" column in the attached file (i.e) Slope .

slope_automatic.xls

I wanted to find a formula which can automatically perform "Slope" of the line calculation.

It should consider cases like : Skipping of the first or/and last number IF they are "Non-Available"number. Also depending on length of dataset (x and y set), the slope calculation should be automatically updated, depending on last valid cell address/value.

Please help me. I am doing it manually and I dont know how to use a valid formula to automate it.

regards,

- Excel'less excel user

Hello excelun,

Will there be any data under these columns, or will they be alone on the sheet?

Also, is 30 the maximum amount of rows of data you could have?

Lastly, do you have Excel 2007 like your profile states, or have you upgraded since then?

This will handle any of the situations that you have, provided a) you use the exact same format, b) your data points don't exceed row 100, c) the formula itself can't be in the slope column, but next to it is okay, and d) do NOT put anything else under the data that you're entering. To have self-adjusting columns, you cannot put anything below them.

Note: These formulas have to be entered as array formulas, Ctrl + Shift + Enter to leave the cell.

slope_automatic (1).xls

Edit: The calculation cant affect any cells in the calculation area (where you were storing it), so I mimicked the data in your slope columns, and hid the original columns.

kvsrinivasamurthy,

In your file, you assume the lowest row will be 13. I designed mine to dynamically expand the range depending on data points (assuming he doesn't put anything under it)