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
    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! =)
     
    , Jun 16, 2006
    #1
    1. Advertisements

  2. Guest Guest

    =AVERAGE(A1:A10) includes zeros

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

    hth
    Vaya con Dios,
    Chuck, CABGx3



    "" 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! =)
    >
    >
     
    Guest, Jun 16, 2006
    #2
    1. Advertisements

  3. Guest Guest

    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


    "" 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! =)
    >
    >
     
    Guest, Jun 16, 2006
    #3
  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?

    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
    >
    >
    > "" 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! =)
    > >
    > >
     
    , Jun 17, 2006
    #4
  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
    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
    >
    >
    >
    > "" 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! =)
    > >
    > >
     
    , Jun 17, 2006
    #5
  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.


    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
    > >
    > >
    > > "" 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! =)
    > > >
    > > >
     
    , Jun 19, 2006
    #6
  7. DangerMouse Guest

    DangerMouse, Jun 19, 2006
    #7
  8. 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?action=getinfo&userid=27755
    > View this thread: http://www.excelforum.com/showthread.php?threadid=552656


    --

    Dave Peterson
     
    Dave Peterson, Jun 19, 2006
    #8
  9. Guest Guest

    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?


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

    >
    >
     
    Guest, Jun 19, 2006
    #9
  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
    =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?
    >
    >
    > "" 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.
    > >
    > >
    > > 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
    > > > >
    > > > >
    > > > > "" 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! =)
    > > > > >
    > > > > >

    > >
    > >
     
    , Jun 20, 2006
    #10
  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.


    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?action=getinfo&userid=27755
    > View this thread: http://www.excelforum.com/showthread.php?threadid=552656
     
    , Jun 20, 2006
    #11
  12. Guest Guest

    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?



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

    >
    >
     
    Guest, Jun 20, 2006
    #12
  13. Guest

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

    > >
    > >
     
    , Jun 22, 2006
    #13
  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.


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

    > >
    > >
     
    , Jun 22, 2006
    #14
  15. Guest Guest

    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?





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

    >
    >
     
    Guest, Jun 22, 2006
    #15
  16. Guest

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

    > >
    > >
     
    , Jun 23, 2006
    #16
  17. Guest Guest

    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?



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

    >
    >
     
    Guest, Jun 23, 2006
    #17
  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. =)


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

    > >
    > >
     
    , Jun 27, 2006
    #18
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Frank Kabel

    Include Leading Zeros in Cells

    Frank Kabel, Jul 27, 2004, in forum: Microsoft Excel Misc
    Replies:
    0
    Views:
    171
    Frank Kabel
    Jul 27, 2004
  2. Guest
    Replies:
    1
    Views:
    1,281
    Guest
    May 4, 2005
  3. Guest
    Replies:
    0
    Views:
    593
    Guest
    Mar 3, 2006
  4. santhu

    Essbase: Text zeros to number zeros

    santhu, Mar 23, 2007, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    692
    Guest
    Mar 23, 2007
  5. Guest

    Average non-adjacent cells & exclude zeros

    Guest, Sep 17, 2007, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    551
    David Biddulph
    Sep 18, 2007
  6. Guest
    Replies:
    1
    Views:
    357
    Dave Peterson
    Nov 16, 2007
  7. Tilsted
    Replies:
    1
    Views:
    537
    Tilsted
    Jul 7, 2011
  8. Tony O
    Replies:
    0
    Views:
    366
    Tony O
    Jun 29, 2012
Loading...