Subtracting values in cells from a total and counting the number of cells to reach <0

Thanks for any help! I have struggled with this for ages. Could i use an AGGREGATE function here??? Situation outlined below, good luck!:

Ultimately I am looking to subtract values in a row of cells from a total and count the number of cells it takes for the total to reach <0.


I am working on a spreadsheet which looks at calculate/forecast the date of strawberry ripening. The number of days from a flower to ripening is based on the sum of "24hr average temperatures".

The number of days it takes a strawberry flower to turn into a ripe fruit is based on a Sum of 24hr Avg Temperatures which is has to reach (this means the average temperature over each 24 hours is added together to reach the total REQUIRED).


I will be inputting the daily 24hr average temperatures as well as the number of flowers appearing each day.


I will also have the "REQUIRED sum of 24 hr average temperature" for a flower to reach ripening (for now lets call it 750*C).


For each individual count date (date when flowers are counted) I will subtract the "accumulated 24hr avg temperatures" over the period; which is a sum of all "24hr avg temps" from the "TOTAL REQUIRED sum of temps" to leave me with the "REMAINING TOTAL REQUIRED temp."(for example each day i add an OBSERVED 24hr avg temp, it will subtract the sum of all OBSERVED temps from the count date until the last OBSERVED temp, from the REQUIRED total).

To determine the remaining number of days until ripening i will use a forecast of "24hr avg temps." which will be in individual cells across the COLUMNS (each COLUMN is a different Date).

Bearing in mind that the forecasted 24hr average temperature will be different for each date.

I want to be able to calculate the number of days it will take for the REMAINING TOTAL REQUIRED to fall to <0 (less than 0).

This will have to involve using the REMAINING TOTAL REQUIRED and subtracting the forecasted temps in sequence until the REAMAINING TOTAL REQUIRED is less than 0, and the count number of dates/individual 24hr average temps it took to reach that point.

Either it will add the number of days on to the number of days already passed (a simple count function of dates with cells that arent empty and contain observed temps could look this up) or add them on to "today's" date (the date at which the data is being viewed).

I have attached the spreadsheeet which i intend to use. THere are different groups which refer to areas of strawberry fields which are not particularly important.

What i would like is to either retrieve a harvest DATE/number of days LEFT until ripening/total days unti ripening (including ACTUAL days passed + number of days left based on subtracting daily forecast temp from REQUIRED REMAINING temp)


Thanks for all your time!

All the best,

Tony