## Tuesday, July 30, 2013

### yogi_Set Up Array Formula With Open Ended Range For Finding The Current Trend In A Sheet

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul 30, 2013
Finding the current trend in a sheet
I keep a spreadsheet of how much I have charged a client, and how much time I spent on work for that client.  I use this information to get the average of how much per hour I am charging.  I then use the trend function to figure out whether I'm improving or holding steady.  The problem is, everytime I enter in new information, I have to manually update which cells the trend should apply to (if I simply have the trend for the whole column, the formula breaks).  I want to be able to lock the sheet so that I can have other (less tech savvy) staff use the spreadsheet without fear.

I've tried messing around with lookup functions, but I haven't had any luck.  Right now, my trend formula is =TREND(G34:G57,A34:A57). I just need a way for the "57" to update to "58" if there is data in 58.
---
Here is a mock-up of the sheet I'm working on:

As you can see, for the last two entries, the trend column hasn't updated itself.

Otávio, I tried your formula, but it said a circular dependency was detected.

Yogi:
a) I thought it would be a simple matter to change "F34:F56" to something like "F34:(formula)".
b) I'm looking to have the trend be in column G.
c) Expected result is to have the trend automatically calculate in the same way that I have the hourly rate calculate itself.  So, a new client pays us, I put in the amount and the time worked, and the spreadsheet figures out the hourly rate and the trend.

Thanks in advance for the help.
-------------------------------------------------------------------------------------------------------------------------------------------------------