Formula to Detect Last Cell in a Column with Data Entered

I work on a farm and I use a spreadsheet to help calculate when crops are ready for harvest. I enter in new temperatures every day based on the previous days highs and lows. Using these numbers and a formula in Excel I can figure out the degree days a plant has received (the ammount of energy it has taken in.) Getting the degree days, or GDD as it is labeled in my spreadsheet, is easy. However, I want to be able to see the total ammount of degree days for a particular crop. I can do this manually by simply adding up all the degree days from when the crop was planted until the last day I have temperatures for, but it gets very old having to manually update the formula for a extra day for 300+ crops daily. Essentially, this is what I need help with:

I need a formula that will automatically detect the last cell in my column of GDD that has data in it. That way, every morning when I put in the previous day's temperatures and it spits out the GDD value for the previous day, the sum of degree days for the crops all update by themselves.

Right now, here is the formula I'm using to find out how many degree days a crop has received:

=SUM(GDD!E27:GDD!E85)

Every day I have to change "GDD!E85" to 86, 87, 88, etc.


I don't know if this helps, but here is the formula I use to calulcute GDD for a single day:

=((C8+B8)/2)-D8 or =((TEMPmax+TEMPmin)/2)-TEMPbase


Anyone have any ideas?

Thanks.

Hi Tyler & Welcome to the Forum,

First thank you for the title update...

Give this a try...

=SUM(GDD!$E$27:INDEX(GDD!E:E,MATCH(9.99999999999999E+307,GDD!E:E)))

if you have nothing below the data, couldnt you just use.....

=SUM(GDD!E27:GDD!E1000)

You are very welcome. We are glad to help and thanks for the feedback

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save