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

=AVERAGE(A1:A10) includes zeros

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

hth
Vaya con Dios,
Chuck, CABGx3



"candice.sy@gmail.com" 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! =)
>
>

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
>
>
>
> "candice.sy@gmail.com" 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! =)
> >
> >

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


"candice.sy@gmail.com" 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! =)
>
>

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
>
>
> "candice.sy@gmail.com" 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! =)
> >
> >

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.


candice.sy@gmail.com 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
> >
> >
> > "candice.sy@gmail.com" 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! =)
> > >
> > >

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

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

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

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?


"candice.sy@gmail.com" 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.
>
>
> candice.sy@gmail.com 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
> > >
> > >
> > > "candice.sy@gmail.com" 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! =)
> > > >
> > > >
>
>

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?
>
>
> "candice.sy@gmail.com" 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.
> >
> >
> > candice.sy@gmail.com 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
> > > >
> > > >
> > > > "candice.sy@gmail.com" 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! =)
> > > > >
> > > > >
> >
> >