I'm struggling since I'm not the best with working out the most efficient way to do these things.
Check the following:
"First row with data: 10".
Calculated by a formula that looks for the first data in a column.
"Last row with data: 1128". Calculated by a formula that looks for the last row with data - the value calculated above.
"Data range AH10:AH1128 Calculated by a formula that creates the result from using the previous two result cells.
This then needs to feed into two further calculations:
C: = EXP(INDEX(LINEST(**AH10:AH1128**,AO10:AO1128,,),1,2)) B: = INDEX(LINEST(**AH10:AH1128**,AO10:AO1128,,),1)
At present I have to change the above formulas manually every time the data changes!
The data is based on a Pivot that updates monthly and the range with expand dynamically. The bottom two formulas calculate the power trend-line C: and B: values which then feed a ready-reckoner calculation in another linked sheet.
I have multiple sheets doing the same job but for filtered results as I cannot see any way to make the formulas work all from one dataset.
It is particularly the first problem though that I need to solve.
Can anyone suggest an idea to fix the issue?
Hi Raj - I find that the formulae that you suggested goes doolally when you have blank cells and cells with formulas in that return #N/A, etc results in the list of cells to be counted. How do you adjust to remedy? Thanks