+ Reply to Thread
Results 1 to 18 of 18

Average Function (include Blank Cells and Zeros)

  1. #1

    Average Function (include Blank Cells and Zeros)

    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
    part.

    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! =)


  2. #2
    CLR
    Guest

    RE: Average Function (include Blank Cells and Zeros)

    =AVERAGE(A1:A10) includes zeros

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

    hth
    Vaya con Dios,
    Chuck, CABGx3



    "[email protected]" wrote:

    > 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
    > part.
    >
    > 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! =)
    >
    >


  3. #3
    Dominic LeVasseur
    Guest

    RE: Average Function (include Blank Cells and Zeros)

    Candice,

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

    You could use something like:

    =sum(b1:b10)/count(a1:a10)

    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.

    HTH


    "[email protected]" wrote:

    > 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
    > part.
    >
    > 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! =)
    >
    >


  4. #4

    Re: Average Function (include Blank Cells and Zeros)

    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?

    Dominic LeVasseur wrote:
    > Candice,
    >
    > The average function, as you know, won't include blank cells.
    >
    > You could use something like:
    >
    > =sum(b1:b10)/count(a1:a10)
    >
    > 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.
    >
    > HTH
    >
    >
    > "[email protected]" wrote:
    >
    > > 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
    > > part.
    > >
    > > 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! =)
    > >
    > >



  5. #5

    Re: Average Function (include Blank Cells and Zeros)

    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
    department.

    CLR wrote:
    > =AVERAGE(A1:A10) includes zeros
    >
    > =SUM(A1:A10)/COUNTIF(A1:A10,"<>0") does not include zeros
    >
    > hth
    > Vaya con Dios,
    > Chuck, CABGx3
    >
    >
    >
    > "[email protected]" wrote:
    >
    > > 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
    > > part.
    > >
    > > 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! =)
    > >
    > >



  6. #6

    Re: Average Function (include Blank Cells and Zeros)

    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.


    [email protected] wrote:
    > 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?
    >
    > Dominic LeVasseur wrote:
    > > Candice,
    > >
    > > The average function, as you know, won't include blank cells.
    > >
    > > You could use something like:
    > >
    > > =sum(b1:b10)/count(a1:a10)
    > >
    > > 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.
    > >
    > > HTH
    > >
    > >
    > > "[email protected]" wrote:
    > >
    > > > 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
    > > > part.
    > > >
    > > > 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! =)
    > > >
    > > >



  7. #7
    Registered User
    Join Date
    10-03-2005
    Posts
    28
    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?

    thanks

    steve

  8. #8
    Dave Peterson
    Guest

    Re: Average Function (include Blank Cells and Zeros)

    In cell in the same worksheet, you could use a formula like:
    =if(a1="","",a1)

    in a cell in a different worksheet in the same workbook:
    =if(sheet1!a1="","",sheet1!a1)

    Between two workbooks:
    =IF([book1.xls]Sheet1!$A$1="","",[book1.xls]Sheet1!$A$1)

    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.



    DangerMouse wrote:
    >
    > 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?
    >
    > thanks
    >
    > steve
    >
    > --
    > DangerMouse
    > ------------------------------------------------------------------------
    > DangerMouse's Profile: http://www.excelforum.com/member.php...o&userid=27755
    > View this thread: http://www.excelforum.com/showthread...hreadid=552656


    --

    Dave Peterson

  9. #9
    Dominic LeVasseur
    Guest

    Re: Average Function (include Blank Cells and Zeros)

    Candice,

    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

    =sum(a2:d2)/count(a1:d1)

    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?


    "[email protected]" wrote:

    > 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.
    >
    >
    > [email protected] wrote:
    > > 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?
    > >
    > > Dominic LeVasseur wrote:
    > > > Candice,
    > > >
    > > > The average function, as you know, won't include blank cells.
    > > >
    > > > You could use something like:
    > > >
    > > > =sum(b1:b10)/count(a1:a10)
    > > >
    > > > 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.
    > > >
    > > > HTH
    > > >
    > > >
    > > > "[email protected]" wrote:
    > > >
    > > > > 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
    > > > > part.
    > > > >
    > > > > 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! =)
    > > > >
    > > > >

    >
    >


  10. #10

    Re: Average Function (include Blank Cells and Zeros)

    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
    =sum(a2:ad2)/count(a1:ad1).

    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).

    Dominic LeVasseur wrote:
    > Candice,
    >
    > 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
    >
    > =sum(a2:d2)/count(a1:d1)
    >
    > 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?
    >
    >
    > "[email protected]" wrote:
    >
    > > 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.
    > >
    > >
    > > [email protected] wrote:
    > > > 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?
    > > >
    > > > Dominic LeVasseur wrote:
    > > > > Candice,
    > > > >
    > > > > The average function, as you know, won't include blank cells.
    > > > >
    > > > > You could use something like:
    > > > >
    > > > > =sum(b1:b10)/count(a1:a10)
    > > > >
    > > > > 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.
    > > > >
    > > > > HTH
    > > > >
    > > > >
    > > > > "[email protected]" wrote:
    > > > >
    > > > > > 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
    > > > > > part.
    > > > > >
    > > > > > 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! =)
    > > > > >
    > > > > >

    > >
    > >



  11. #11

    Re: Average Function (include Blank Cells and Zeros)

    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.


    DangerMouse wrote:
    > 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?
    >
    > thanks
    >
    > steve
    >
    >
    > --
    > DangerMouse
    > ------------------------------------------------------------------------
    > DangerMouse's Profile: http://www.excelforum.com/member.php...o&userid=27755
    > View this thread: http://www.excelforum.com/showthread...hreadid=552656



  12. #12
    Dominic LeVasseur
    Guest

    Re: Average Function (include Blank Cells and Zeros)

    Candice,

    I'm sorry, I misunderstood your spreadsheet layout.

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

    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?



    "[email protected]" wrote:

    > 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
    > =sum(a2:ad2)/count(a1:ad1).
    >
    > 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).
    >
    > Dominic LeVasseur wrote:
    > > Candice,
    > >
    > > 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
    > >
    > > =sum(a2:d2)/count(a1:d1)
    > >
    > > 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?
    > >
    > >
    > > "[email protected]" wrote:
    > >
    > > > 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.
    > > >
    > > >
    > > > [email protected] wrote:
    > > > > 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?
    > > > >
    > > > > Dominic LeVasseur wrote:
    > > > > > Candice,
    > > > > >
    > > > > > The average function, as you know, won't include blank cells.
    > > > > >
    > > > > > You could use something like:
    > > > > >
    > > > > > =sum(b1:b10)/count(a1:a10)
    > > > > >
    > > > > > 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.
    > > > > >
    > > > > > HTH
    > > > > >
    > > > > >
    > > > > > "[email protected]" wrote:
    > > > > >
    > > > > > > 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
    > > > > > > part.
    > > > > > >
    > > > > > > 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! =)
    > > > > > >
    > > > > > >
    > > >
    > > >

    >
    >


  13. #13

    Re: Average Function (include Blank Cells and Zeros)

    Dominic,

    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
    30.

    Thanks for all your patience and help. =)


    Dominic LeVasseur wrote:
    > Candice,
    >
    > I'm sorry, I misunderstood your spreadsheet layout.
    >
    > Perhaps you could accomplish what you want to do by using the "counta"
    > function.
    >
    > 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?
    >
    >
    >
    > "[email protected]" wrote:
    >
    > > 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
    > > =sum(a2:ad2)/count(a1:ad1).
    > >
    > > 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).
    > >
    > > Dominic LeVasseur wrote:
    > > > Candice,
    > > >
    > > > 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
    > > >
    > > > =sum(a2:d2)/count(a1:d1)
    > > >
    > > > 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?
    > > >
    > > >
    > > > "[email protected]" wrote:
    > > >
    > > > > 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.
    > > > >
    > > > >
    > > > > [email protected] wrote:
    > > > > > 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?
    > > > > >
    > > > > > Dominic LeVasseur wrote:
    > > > > > > Candice,
    > > > > > >
    > > > > > > The average function, as you know, won't include blank cells.
    > > > > > >
    > > > > > > You could use something like:
    > > > > > >
    > > > > > > =sum(b1:b10)/count(a1:a10)
    > > > > > >
    > > > > > > 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.
    > > > > > >
    > > > > > > HTH
    > > > > > >
    > > > > > >
    > > > > > > "[email protected]" wrote:
    > > > > > >
    > > > > > > > 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
    > > > > > > > part.
    > > > > > > >
    > > > > > > > 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! =)
    > > > > > > >
    > > > > > > >
    > > > >
    > > > >

    > >
    > >



  14. #14

    Re: Average Function (include Blank Cells and Zeros)

    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.


    Dominic LeVasseur wrote:
    > Candice,
    >
    > I'm sorry, I misunderstood your spreadsheet layout.
    >
    > Perhaps you could accomplish what you want to do by using the "counta"
    > function.
    >
    > 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?
    >
    >
    >
    > "[email protected]" wrote:
    >
    > > 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
    > > =sum(a2:ad2)/count(a1:ad1).
    > >
    > > 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).
    > >
    > > Dominic LeVasseur wrote:
    > > > Candice,
    > > >
    > > > 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
    > > >
    > > > =sum(a2:d2)/count(a1:d1)
    > > >
    > > > 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?
    > > >
    > > >
    > > > "[email protected]" wrote:
    > > >
    > > > > 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.
    > > > >
    > > > >
    > > > > [email protected] wrote:
    > > > > > 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?
    > > > > >
    > > > > > Dominic LeVasseur wrote:
    > > > > > > Candice,
    > > > > > >
    > > > > > > The average function, as you know, won't include blank cells.
    > > > > > >
    > > > > > > You could use something like:
    > > > > > >
    > > > > > > =sum(b1:b10)/count(a1:a10)
    > > > > > >
    > > > > > > 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.
    > > > > > >
    > > > > > > HTH
    > > > > > >
    > > > > > >
    > > > > > > "[email protected]" wrote:
    > > > > > >
    > > > > > > > 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
    > > > > > > > part.
    > > > > > > >
    > > > > > > > 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! =)
    > > > > > > >
    > > > > > > >
    > > > >
    > > > >

    > >
    > >



  15. #15
    Dominic LeVasseur
    Guest

    Re: Average Function (include Blank Cells and Zeros)

    Candice,

    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?





    "[email protected]" wrote:

    > 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.
    >
    >
    > Dominic LeVasseur wrote:
    > > Candice,
    > >
    > > I'm sorry, I misunderstood your spreadsheet layout.
    > >
    > > Perhaps you could accomplish what you want to do by using the "counta"
    > > function.
    > >
    > > 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?
    > >
    > >
    > >
    > > "[email protected]" wrote:
    > >
    > > > 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
    > > > =sum(a2:ad2)/count(a1:ad1).
    > > >
    > > > 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).
    > > >
    > > > Dominic LeVasseur wrote:
    > > > > Candice,
    > > > >
    > > > > 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
    > > > >
    > > > > =sum(a2:d2)/count(a1:d1)
    > > > >
    > > > > 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?
    > > > >
    > > > >
    > > > > "[email protected]" wrote:
    > > > >
    > > > > > 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.
    > > > > >
    > > > > >
    > > > > > [email protected] wrote:
    > > > > > > 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?
    > > > > > >
    > > > > > > Dominic LeVasseur wrote:
    > > > > > > > Candice,
    > > > > > > >
    > > > > > > > The average function, as you know, won't include blank cells.
    > > > > > > >
    > > > > > > > You could use something like:
    > > > > > > >
    > > > > > > > =sum(b1:b10)/count(a1:a10)
    > > > > > > >
    > > > > > > > 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.
    > > > > > > >
    > > > > > > > HTH
    > > > > > > >
    > > > > > > >
    > > > > > > > "[email protected]" wrote:
    > > > > > > >
    > > > > > > > > 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
    > > > > > > > > part.
    > > > > > > > >
    > > > > > > > > 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! =)
    > > > > > > > >
    > > > > > > > >
    > > > > >
    > > > > >
    > > >
    > > >

    >
    >


  16. #16

    Re: Average Function (include Blank Cells and Zeros)

    Dominic,

    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. =)

    Dominic LeVasseur wrote:
    > Candice,
    >
    > 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?
    >
    >
    >
    >
    >
    > "[email protected]" wrote:
    >
    > > 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.
    > >
    > >
    > > Dominic LeVasseur wrote:
    > > > Candice,
    > > >
    > > > I'm sorry, I misunderstood your spreadsheet layout.
    > > >
    > > > Perhaps you could accomplish what you want to do by using the "counta"
    > > > function.
    > > >
    > > > 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?
    > > >
    > > >
    > > >
    > > > "[email protected]" wrote:
    > > >
    > > > > 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
    > > > > =sum(a2:ad2)/count(a1:ad1).
    > > > >
    > > > > 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).
    > > > >
    > > > > Dominic LeVasseur wrote:
    > > > > > Candice,
    > > > > >
    > > > > > 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
    > > > > >
    > > > > > =sum(a2:d2)/count(a1:d1)
    > > > > >
    > > > > > 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?
    > > > > >
    > > > > >
    > > > > > "[email protected]" wrote:
    > > > > >
    > > > > > > 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.
    > > > > > >
    > > > > > >
    > > > > > > [email protected] wrote:
    > > > > > > > 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?
    > > > > > > >
    > > > > > > > Dominic LeVasseur wrote:
    > > > > > > > > Candice,
    > > > > > > > >
    > > > > > > > > The average function, as you know, won't include blank cells.
    > > > > > > > >
    > > > > > > > > You could use something like:
    > > > > > > > >
    > > > > > > > > =sum(b1:b10)/count(a1:a10)
    > > > > > > > >
    > > > > > > > > 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.
    > > > > > > > >
    > > > > > > > > HTH
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "[email protected]" wrote:
    > > > > > > > >
    > > > > > > > > > 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
    > > > > > > > > > part.
    > > > > > > > > >
    > > > > > > > > > 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! =)
    > > > > > > > > >
    > > > > > > > > >
    > > > > > >
    > > > > > >
    > > > >
    > > > >

    > >
    > >



  17. #17
    Dominic LeVasseur
    Guest

    Re: Average Function (include Blank Cells and Zeros)

    Candice,

    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?



    "[email protected]" wrote:

    > Dominic,
    >
    > 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. =)
    >
    > Dominic LeVasseur wrote:
    > > Candice,
    > >
    > > 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?
    > >
    > >
    > >
    > >
    > >
    > > "[email protected]" wrote:
    > >
    > > > 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.
    > > >
    > > >
    > > > Dominic LeVasseur wrote:
    > > > > Candice,
    > > > >
    > > > > I'm sorry, I misunderstood your spreadsheet layout.
    > > > >
    > > > > Perhaps you could accomplish what you want to do by using the "counta"
    > > > > function.
    > > > >
    > > > > 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?
    > > > >
    > > > >
    > > > >
    > > > > "[email protected]" wrote:
    > > > >
    > > > > > 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
    > > > > > =sum(a2:ad2)/count(a1:ad1).
    > > > > >
    > > > > > 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).
    > > > > >
    > > > > > Dominic LeVasseur wrote:
    > > > > > > Candice,
    > > > > > >
    > > > > > > 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
    > > > > > >
    > > > > > > =sum(a2:d2)/count(a1:d1)
    > > > > > >
    > > > > > > 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?
    > > > > > >
    > > > > > >
    > > > > > > "[email protected]" wrote:
    > > > > > >
    > > > > > > > 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.
    > > > > > > >
    > > > > > > >
    > > > > > > > [email protected] wrote:
    > > > > > > > > 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?
    > > > > > > > >
    > > > > > > > > Dominic LeVasseur wrote:
    > > > > > > > > > Candice,
    > > > > > > > > >
    > > > > > > > > > The average function, as you know, won't include blank cells.
    > > > > > > > > >
    > > > > > > > > > You could use something like:
    > > > > > > > > >
    > > > > > > > > > =sum(b1:b10)/count(a1:a10)
    > > > > > > > > >
    > > > > > > > > > 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.
    > > > > > > > > >
    > > > > > > > > > HTH
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > "[email protected]" wrote:
    > > > > > > > > >
    > > > > > > > > > > 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
    > > > > > > > > > > part.
    > > > > > > > > > >
    > > > > > > > > > > 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! =)
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > >
    > > > > >
    > > >
    > > >

    >
    >


  18. #18

    Re: Average Function (include Blank Cells and Zeros)

    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. =)


    Dominic LeVasseur wrote:
    > Candice,
    >
    > 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?
    >
    >
    >
    > "[email protected]" wrote:
    >
    > > Dominic,
    > >
    > > 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. =)
    > >
    > > Dominic LeVasseur wrote:
    > > > Candice,
    > > >
    > > > 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?
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > "[email protected]" wrote:
    > > >
    > > > > 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.
    > > > >
    > > > >
    > > > > Dominic LeVasseur wrote:
    > > > > > Candice,
    > > > > >
    > > > > > I'm sorry, I misunderstood your spreadsheet layout.
    > > > > >
    > > > > > Perhaps you could accomplish what you want to do by using the "counta"
    > > > > > function.
    > > > > >
    > > > > > 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?
    > > > > >
    > > > > >
    > > > > >
    > > > > > "[email protected]" wrote:
    > > > > >
    > > > > > > 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
    > > > > > > =sum(a2:ad2)/count(a1:ad1).
    > > > > > >
    > > > > > > 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).
    > > > > > >
    > > > > > > Dominic LeVasseur wrote:
    > > > > > > > Candice,
    > > > > > > >
    > > > > > > > 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
    > > > > > > >
    > > > > > > > =sum(a2:d2)/count(a1:d1)
    > > > > > > >
    > > > > > > > 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?
    > > > > > > >
    > > > > > > >
    > > > > > > > "[email protected]" wrote:
    > > > > > > >
    > > > > > > > > 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.
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > [email protected] wrote:
    > > > > > > > > > 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?
    > > > > > > > > >
    > > > > > > > > > Dominic LeVasseur wrote:
    > > > > > > > > > > Candice,
    > > > > > > > > > >
    > > > > > > > > > > The average function, as you know, won't include blank cells.
    > > > > > > > > > >
    > > > > > > > > > > You could use something like:
    > > > > > > > > > >
    > > > > > > > > > > =sum(b1:b10)/count(a1:a10)
    > > > > > > > > > >
    > > > > > > > > > > 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.
    > > > > > > > > > >
    > > > > > > > > > > HTH
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > > "[email protected]" wrote:
    > > > > > > > > > >
    > > > > > > > > > > > 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
    > > > > > > > > > > > part.
    > > > > > > > > > > >
    > > > > > > > > > > > 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! =)
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > >
    > > > > > > > >
    > > > > > >
    > > > > > >
    > > > >
    > > > >

    > >
    > >



+ 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