Average Function (include Blank Cells and Zeros)

Discussion in 'Microsoft Excel Misc' started by candice.sy@gmail.com, Jun 16, 2006.

  1. Guest

    Hi, can someone help me?

    My "final" worksheet is linked to another "input" worksheet. I wish for
    my final worksheet to mirror exactly whatever is in the input
    worksheet. For example, weekends/non-working days/holidays are "blank"
    while weekdays/working days are filled with numbers (including zeros on
    working days whenever there's no order). I've already accomplished this

    However, another dilemma arises when I have to average the daily
    figures by the number of calendar days for all departments. Note that
    all departments update their figures on different times. For example,
    as of June 8, Operations dept may have already updated their figures as
    of latest date (June8) while Marketing has only updated as of June 7.

    Also, I don't want to update the daily average calculation (changing
    the divisor depending upon the latest no. of calendar days) everytime a
    department updates their figures. Is it possible to just have a
    standard average formula for all departments?

    My "final" worksheet as of June 8,
    Operations Dept (sum divided by 8 working days)
    June1(Mon) = 34
    June2(Tue) = 31
    June3(Wed) = 0
    June4(Thur) = 23
    June5(Fri) = 21
    June6(Sat) = " "
    June7(Sun) = " "
    June8(Mon) = 23
    June 9 onwards is still blank.

    Marketing Dept (sum divided by 7 working days)
    June1(Mon) = 34
    June2(Tue) = 31
    June3(Wed) = 0
    June4(Thur) = 23
    June5(Fri) = 21
    June6(Sat) = " "
    June7(Sun) = " "
    June8 onwards is still blank.

    Is there a solution to my problem? Thanks in advance for the help! =)
    , Jun 16, 2006
    1. Advertisements

  2. Guest Guest

    =AVERAGE(A1:A10) includes zeros

    =SUM(A1:A10)/COUNTIF(A1:A10,"<>0") does not include zeros

    Vaya con Dios,
    Chuck, CABGx3
    Guest, Jun 16, 2006
    1. Advertisements

  3. Guest Guest


    The average function, as you know, won't include blank cells.

    You could use something like:


    Where your data is in column B and your dates are in column A

    You would need to create a formula for each department range.

    Guest, Jun 16, 2006
  4. Guest

    Hi, I appreciate all the help.

    I can't use =sum(b1:b30)/count(a1:a30).

    It seems that the count function does not include or consider blank
    spaces in its calculation. As mentioned, weekends are left blank on my
    worksheet. I need to divide by the number of calendar days (as of
    latest day of inputs encoded by each department). So for example, if
    today is June 16 and marketing has updated their inputs as of June 16,
    my divisor should be 16. On the other hand, operations has not yet
    updated their inputs and their inputs is as of June 15, my divisor
    should be 15.

    As much as possible, I don't want to update the divisor everytime a
    department refreshes their inputs. Is this possible?
    , Jun 17, 2006
  5. Guest

    I need to include the zeros as well because I am averaging by the
    number of calendar days thus far. Weekends are left blank. Meanwhile,
    some working days have zero orders.

    Is it possible to have a standard average function for all departments?
    The divisor being the number of calendar days last updated by every
    , Jun 17, 2006
  6. Guest

    My bad, my formula is wrong.

    A1:AD1 = number of calendar days (June1 to June30)
    A2:AD2 = data (for example, no. of orders)
    A3:AD3 = data
    so on and so forth.
    , Jun 19, 2006
  7. DangerMouse Guest

    I dont wish to hi-jack this thread, but might I ask how you accomplished
    the displaying of blank cells as blanks in a linked workbook?


    DangerMouse, Jun 19, 2006
  8. In cell in the same worksheet, you could use a formula like:

    in a cell in a different worksheet in the same workbook:

    Between two workbooks:

    I let excel do the heavy lifting when the formula gets too long for me.

    I edit|Copy the cell
    then Edit|paste special|links
    and just mimic what it pasted in my =IF() statement.
    Dave Peterson, Jun 19, 2006
  9. Guest Guest


    Yes, exactly.

    That is why you use the count function on the date row, not the data row.

    So, for example:

    A1 - June1
    B1 - June2
    C1 - June3
    D1 - June4

    A2 - 10
    B2 - 15
    C2 - blank
    D2 - blank


    This gives you the result 6.25

    Isn't this what you are looking for?

    You don't ever have to change the divisor if you leave 31 columns for date
    data as you mention.

    Does that help?
    Guest, Jun 19, 2006
  10. Guest

    Thanks for your help, really appreciate it. =)

    However, using the formula that you've suggested, I think I'll still
    have to change the average function for all departments daily.

    My worksheet is already pre-formatted. A1 to AD1 contains 1 to 30 (for
    the month of June).

    Assuming today is June 20, my average function is set to

    My average will be wrong since I'm averaging for the whole month of
    June already. My divisor would be 30 now instead of 20. I need to
    compute the average as of the current date that a department updates
    (for example, mktg has updated as of june 19 while operations has
    updated as of june 20).
    , Jun 20, 2006
  11. Guest

    Steve, just us the IS function.

    If you want to your final worksheet to mirror exactly your input
    worksheet, use =IF(ISBLANK(inputworksheetA1," ",inputworksheetA1).

    Final worksheet will no longer reflect zeros whenever your input
    worksheet is blank.
    , Jun 20, 2006
  12. Guest Guest


    I'm sorry, I misunderstood your spreadsheet layout.

    Perhaps you could accomplish what you want to do by using the "counta"

    Instead of returning a empty string "" when there is no value in the input
    sheet, you could return a space " ".

    Then for your average you could use: =sum(a2:ad2)/counta(a2:ad2)

    This should give you what you want.

    Does that help?
    Guest, Jun 20, 2006
  13. Guest


    What's the difference between an empty string and a space? How do I
    return a space? Currently, my final worksheet uses the ISBLANK function
    to exactly mirror/reflect my input worksheet.

    As you would know, linking worksheets would reflect zeros on days
    ahead. For example, my input worksheet has data up to June 22 only
    while June 23 onwards is blank. Consequently, my final worksheet would
    have 0s on June 23 onwards. I've already eliminated this problem by
    using the ISBLANK function.

    Going back to my problem, I've tried using the counta function already.
    The problem now is it counts all the blank cells. For example, today is
    June 22. Using counta function should give me 22.

    My final worksheet looks like A1 to AD1 = June 1 to June 30.
    A2 to AD2 = Data
    A3 to AD3 = Data
    so on and so forth....

    I'm sorry I'm being too redundant but just to give you a clearer
    picture, my final worksheet is the exact image of my input worksheet.
    Therefore, as you can imagine, June 23 onwards is still blank.

    Using a "standard" (meaning, I won't have to change the average
    function anymore everytime a dept updates) average function of
    =sum(A2:AD2)/counta(A2:AD2) as of June 22, would give me a divisor of

    Thanks for all your patience and help. =)
    , Jun 22, 2006
  14. Guest

    Sorry, it's me again. Just realized that I get what you're saying now.

    You said, Instead of returning a empty string "" when there is no value
    in the input
    sheet, you could return a space " ".

    My final worksheet uses this pre-formatted function (for June1 to June
    30) =if(ISBLANK(InputWrksheetA2)," ",InputWrksheetA2)).

    But using the counta function for June 22 still gives me a divisor of
    30, instead of 22.
    , Jun 22, 2006
  15. Guest Guest


    You are currently returning a space " " instead of an empty string "" when
    the input sheet cell is blank. I would change this to return an empty string
    "", there is no space between the quotes.

    This will eliminate the counta function from counting those cells. However,
    you now need to account for the weekend days that are left blank. Is there a
    reason you don't want to put a zero in the weekend days? You want these
    included in the average correct? I would suggest using a zero for these days
    unless there is reason not to.

    If you do have reason not to, then I would suggest putting a space in those
    weekend cells, so that the counta function will count these cells in the
    average functions.

    Does that make sense?
    Guest, Jun 22, 2006
  16. Guest


    You must be shaking your head wondering why am I making my work
    complicated. I've figured a very simple solution to my problem but I
    just wanted to try my luck and see if there really is a better
    solution. I guess I've exhausted all my options already and my last
    resort was to ask for help as I was finding for some answers in google.

    As you've mentioned, a simple solution would be putting 0s on weekends.
    I wanted to keep my final worksheet clean (only a couple of 0s will
    appear occasionally on some working days) and also, to be able to
    differentiate right away the working days from the non-working days.

    I guess the best option afterall is to return 0s on weekends.

    For the Nth time, thanks so much for all the help and patience. =)
    , Jun 23, 2006
  17. Guest Guest


    You're welcome. But if you don't want 0's for weekends please read the rest
    of my last post. You could also use a "space" for the weekend (input a space
    in the input sheet). This will appear blank, but will be included in the
    counta function.

    You will also need to change your isblank formula in the final worksheet to
    return an empty string "" if there is no value in the input worksheet.

    Does this make sense?
    Guest, Jun 23, 2006
  18. Guest

    I did try that also.

    I changed my isblank formula to return an empty string instead but the
    problem is,
    if today is June 27, my counta function would return 30 instead of 27.

    How do I input a "space" in my input worksheet? Currently, what I do is
    to just leave it blank, is this right? Aside from the previous
    weekends, June 28 onwards is also still blank as well.

    My final worksheet is already preset to =if(ISBLANK(A2),"",A2) to
    =if(ISBLANK(AD2),"",AD2). This is for June 1 to 30. And AE1 is preset
    to = sum(A2:AD2)/counta(A2:AD2).

    Row 1 = No. of calendar days (A1 to AD1 = 1 to 30)
    Row 2 = Mktg data (A2:AD2)
    Row 3 = Ops (A3:AD3), so on and so forth.

    My counta should return the latest day the inputs were updated, which
    may vary for each department.

    Many thanks again. =)
    , Jun 27, 2006
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.