Google Sheets – Get Average Function for Accumulative Input

google sheets

I am trying to figure out an average function for my Google spreadsheet, but I am not sure if it is possible, see screenshot:
enter image description here

I could not fit all the spreadsheet in the pic, but column A goes all the way down to Dec 31st, so that is cell A366. Other than that everything that the spreadsheet consists of is visible.

The spreadsheet is to keep track of my goal of writing 1,000,000 words in a year (for article based projects I am working on). I manually enter the number of words I have written each day into the chart in column B.

In columns D/E is the simple function "Total Words Written", which is simply a sum of column B; and function "Total Words Remaining" simply minuses the "Total Words Written" from the goal of 1,000,000 words.

Now, the function of "Daily Average Words To Reach Goal" is the one I don't now how to figure out, or if it is possible. The function needs to take the "Total Words Remaining" figure in cell E4 (which is presently 980586) and divide that by the number of cells in column B that have not been filled in. This will keep a daily running score of what I need to aim for each day. So as the spreadsheet presently stands, the number in E4 needs to be divided by 357. There are 357 empty cells in column B from B10:B36.

Of course, with each new day, there will be 1 less cell that needs to be divided by to create the average.

Let me know if this is possible with Google spreadsheet, or if I would have to settle for using Excel? I would rather keep it in the cloud.

Best Answer

This is another solution.

Formula

=INT(E4/COUNTBLANK(B2:B))

Explained

The COUNTBLANK formula will count the blank cells, independent of the range and E4 will be divided by it. The INT formula will simply round the value to the nearest integer, since it concerns days.

Example

I've created an example file for you: Count Blank Cells