# Possible To Get Average Function For Accumulative Input?

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

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.

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

• Excellent - thank you. So with "COUNTBLANK(B2:B)" I assume the final "B" only works if there are exactly 366 cells in the column? And if there was a few trailing blank cells that went up to column 369, then that would through the count? In which case I need to either keep things neat and tidy and make sure things end at cell 366, or I could use "COUNTBLANK(B2:B366)"? Jan 10, 2014 at 17:44
• @Darren You made all correct statements. Using `B2:B366` will work as well. Jan 10, 2014 at 18:37

You could count the number of cells that contain the daily word amount, and subtract this from 365.

``````E6 = E4 / (365 - Count(B2:B366))