1

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 the 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 column 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.

migrated from superuser.com Jan 10 '14 at 5:23

This question came from our site for computer enthusiasts and power users.

1

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

  • 1
    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)"? – Darren Haynes Jan 10 '14 at 17:44
  • @Darren You made all correct statements. Using B2:B366 will work as well. – Jacob Jan Tuinstra Jan 10 '14 at 18:37
2

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

This gives you your divisor for your words remaining.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.