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?

enter image description here

enter image description here

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

  • Welcome to Superuser! Please have a look at the tour, you can also attach screenshots. – Ahmed Ashour Nov 22 '18 at 9:47
  • @Ross Bames,, better you create a dynamic defined named Range, using such formula =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) and use the Named Range in Formula. – Rajesh S Nov 22 '18 at 11:27
  • Cont... or you may use this one too, =$A$2:INDEX($1:$100,COUNTA($A:$A),COUNTA($1:$1)) – Rajesh S Nov 22 '18 at 11:29
  • Hi Raj - sorry for my ignorance but tried these and not worked. What I think you are suggesting trying to do is create dynamic defined named ranges from data held in a column of data. This needs to exclude some blank cells at the top and bottom and also exclude the data title in AH9. when I try and use your suggestions and adapt for my spreadsheet neither seem to work? – Ross Barnes Nov 22 '18 at 15:45
  • Show us the formulas you use to get Row 10 and Row 1128, these can be combined to make a dynamic NAMED range for you – PeterH Nov 22 '18 at 15:55

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.