+ Reply to Thread
Results 1 to 3 of 3

Average Formula that counts blank cells

  1. #1
    Registered User
    Join Date
    01-19-2010
    Location
    Leeds, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Average Formula that counts blank cells

    Hi folks,

    Is there a formula for working out averages that INCLUDES empty cells in the calculation?

    Say you pay for something quarterly and it looks like this:

    Jan - £120
    Feb -
    Mar -
    Apr - £120
    May -
    Jun -
    July - £120
    Aug -
    Sep -
    Oct - £120
    Nov -
    Dec -
    Total - £480
    Average - £120

    Just using the "Average=" formula would return £120, and I'd like it to say £40.

    I know this is a bit tedious especially as there's 2 relatively easy and quick fixes in that I could:
    1) Manually type in the 0s in the empty cells
    2) Set the Average row formula to just be "Total / 12" instead of an average.

    I was just wondering if there was a "proper" way to do it?
    Attached Images Attached Images

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Average Formula that counts blank cells

    Here's one way.....

    =AVERAGE(INDEX(B2:B13+0,0))
    Audere est facere

  3. #3
    Registered User
    Join Date
    01-19-2010
    Location
    Leeds, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Average Formula that counts blank cells

    That does the job beautifully. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1