Hi there.
I'm having difficulty getting a cell to accurately show an average of
specific cells with values. it is counting all of the zeros in the column
and I can't figure out how to make the cell not show zeros without going
through and deleting each one (hundreds!). (The cells I'm averaging have
formulas in them where they're copies from other cells and when those
original cells are empty, these cells then show a "0.00")

Can you change the original formulas in those cells?

I'm guessing that you have formulas like:
=sheet2!a1

If you replace those formulas with:
=if(sheet2!a1="","",sheet2!a1)

The cells will look blank when the "sending" cells are empty.

And =average() won't include them.

You could write your formula to ignore the cells with 0's in them. But what
happens if the sending cell really had a 0 in it? You average would be
incorrect.

If you wanted to average the non-zeros in a range:
=sum(a1:a10)/countif(a1:a10,"<"&0)

(adding 0 to the sum won't hurt, so I didn't bother to exclude them.)





ClintonOrlando wrote:

Hi there.
I'm having difficulty getting a cell to accurately show an average of
specific cells with values. it is counting all of the zeros in the column
and I can't figure out how to make the cell not show zeros without going
through and deleting each one (hundreds!). (The cells I'm averaging have
formulas in them where they're copies from other cells and when those
original cells are empty, these cells then show a "0.00")

--

Dave Peterson

Clinton in the cells were there are zeros type the following:
=if(iserror(average(A1:A100)," No Data",average(A1:A100)


"ClintonOrlando" wrote in message
...
Hi there.
I'm having difficulty getting a cell to accurately show an average of
specific cells with values. it is counting all of the zeros in the column
and I can't figure out how to make the cell not show zeros without going
through and deleting each one (hundreds!). (The cells I'm averaging have
formulas in them where they're copies from other cells and when those
original cells are empty, these cells then show a "0.00")

Hi, I'm hoping that someone can help me with my dilemma.

My report is automatically updated from another worksheet were daily
figures are encoded. Note that weekends (and holidays or non-working
days) are left blank. I have to average by the number of calendar days.
I tried using average(june1:june30) so that I wouldn't have to update
or change the divisor everyday. I tried using all sorts of formulas.

Is it possible to include blank cells and zeros altogether when using
the average function? Calculation show be sum/no. of working days(as in
this example, divided 8 as of June8).

For example, as of June 8:

Operations Dept (note average should equal to 15.5)
June1 (Mon) = 43 orders
June2 (Tue) = 0 orders
June3 (Wed) = 33 orders
June4 (Thur) = 21 orders
June5 (Fri) = 0 orders
June6 (Sat) = " " (blank) - non-working day
June7 (Sun) = " " (blank) - non-working day
June8 (Mon) = 27

June 9 onwards is blank.

Marketing (case scenario, operations has updated as of june8(latest
day) but marketing has not yet updated; note average should equal to
18.3)
June1 (Mon) = 23
June2 (Tue) = 0
June3 (Wed) = 56
June4 (Thur) = 0
June5 (Fri) = 49
June6 (Sat) = " " (blank)
June7 (Sun) = " " (blank)

June 8 onwards is blank.

Inputs from different departments are not aligned. Is it possible to
use the average function and divide by the number of calendar days(as
of june8 - latest date) without having to change the formula everytime
for all departments?

I hope I was able to explain everything clearly. I hope somebody out
there can help me. Thanks! =)




Dave Peterson wrote:
Can you change the original formulas in those cells?

I'm guessing that you have formulas like:
=sheet2!a1

If you replace those formulas with:
=if(sheet2!a1="","",sheet2!a1)

The cells will look blank when the "sending" cells are empty.

And =average() won't include them.

You could write your formula to ignore the cells with 0's in them. But what
happens if the sending cell really had a 0 in it? You average would be
incorrect.

If you wanted to average the non-zeros in a range:
=sum(a1:a10)/countif(a1:a10,"<"&0)

(adding 0 to the sum won't hurt, so I didn't bother to exclude them.)





ClintonOrlando wrote:

Hi there.
I'm having difficulty getting a cell to accurately show an average of
specific cells with values. it is counting all of the zeros in the column
and I can't figure out how to make the cell not show zeros without going
through and deleting each one (hundreds!). (The cells I'm averaging have
formulas in them where they're copies from other cells and when those
original cells are empty, these cells then show a "0.00")

--

Dave Peterson

It would be helpful if you told us the exact layout of the data
For example: for Ops - the dates are in A1:A30 , the number of orders in
B1:B30
Are the dates filled in before number of orders are empty or are they blank?
Where is the Marketing data?
tell all you can
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
Hi, I'm hoping that someone can help me with my dilemma.

My report is automatically updated from another worksheet were daily
figures are encoded. Note that weekends (and holidays or non-working
days) are left blank. I have to average by the number of calendar days.
I tried using average(june1:june30) so that I wouldn't have to update
or change the divisor everyday. I tried using all sorts of formulas.

Is it possible to include blank cells and zeros altogether when using
the average function? Calculation show be sum/no. of working days(as in
this example, divided 8 as of June8).

For example, as of June 8:

Operations Dept (note average should equal to 15.5)
June1 (Mon) = 43 orders
June2 (Tue) = 0 orders
June3 (Wed) = 33 orders
June4 (Thur) = 21 orders
June5 (Fri) = 0 orders
June6 (Sat) = " " (blank) - non-working day
June7 (Sun) = " " (blank) - non-working day
June8 (Mon) = 27

June 9 onwards is blank.

Marketing (case scenario, operations has updated as of june8(latest
day) but marketing has not yet updated; note average should equal to
18.3)
June1 (Mon) = 23
June2 (Tue) = 0
June3 (Wed) = 56
June4 (Thur) = 0
June5 (Fri) = 49
June6 (Sat) = " " (blank)
June7 (Sun) = " " (blank)

June 8 onwards is blank.

Inputs from different departments are not aligned. Is it possible to
use the average function and divide by the number of calendar days(as
of june8 - latest date) without having to change the formula everytime
for all departments?

I hope I was able to explain everything clearly. I hope somebody out
there can help me. Thanks! =)




Dave Peterson wrote:
Can you change the original formulas in those cells?

I'm guessing that you have formulas like:
=sheet2!a1

If you replace those formulas with:
=if(sheet2!a1="","",sheet2!a1)

The cells will look blank when the "sending" cells are empty.

And =average() won't include them.

You could write your formula to ignore the cells with 0's in them. But
what
happens if the sending cell really had a 0 in it? You average would be
incorrect.

If you wanted to average the non-zeros in a range:
=sum(a1:a10)/countif(a1:a10,"<"&0)

(adding 0 to the sum won't hurt, so I didn't bother to exclude them.)





ClintonOrlando wrote:

Hi there.
I'm having difficulty getting a cell to accurately show an average of
specific cells with values. it is counting all of the zeros in the
column
and I can't figure out how to make the cell not show zeros without
going
through and deleting each one (hundreds!). (The cells I'm averaging
have
formulas in them where they're copies from other cells and when those
original cells are empty, these cells then show a "0.00")

--

Dave Peterson

Thanks for the help.

My final worksheet is already pre-formatted. ColumnA1:A30 = 1 to 30
(for June1-30).
Rows are filled with data from different departments.

My final worksheet is linked to input files from different departments.
As such, some may update regularly while some may forget to update or
data may not be available yet as of latest calendar day. Say, marketing
has updated their inputs as of June 16 while operations has updated
only as of June 13.

My dilemma is, I need the daily average. I can't use the average
function because it does not include blank cells. As I've mentioned, I
leave weekends blank. And note that, it is also possible for weekdays
or working days to have zero orders so I cannot eliminate the zeros as
well. I wish to use a standard average function that will give me the
divisor as of the latest date inputed by various departments. I don't
want to update the divisor everytime a department updates their inputs.
Is this possible?

Bernard Liengme wrote:
It would be helpful if you told us the exact layout of the data
For example: for Ops - the dates are in A1:A30 , the number of orders in
B1:B30
Are the dates filled in before number of orders are empty or are they blank?
Where is the Marketing data?
tell all you can
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
Hi, I'm hoping that someone can help me with my dilemma.

My report is automatically updated from another worksheet were daily
figures are encoded. Note that weekends (and holidays or non-working
days) are left blank. I have to average by the number of calendar days.
I tried using average(june1:june30) so that I wouldn't have to update
or change the divisor everyday. I tried using all sorts of formulas.

Is it possible to include blank cells and zeros altogether when using
the average function? Calculation show be sum/no. of working days(as in
this example, divided 8 as of June8).

For example, as of June 8:

Operations Dept (note average should equal to 15.5)
June1 (Mon) = 43 orders
June2 (Tue) = 0 orders
June3 (Wed) = 33 orders
June4 (Thur) = 21 orders
June5 (Fri) = 0 orders
June6 (Sat) = " " (blank) - non-working day
June7 (Sun) = " " (blank) - non-working day
June8 (Mon) = 27

June 9 onwards is blank.

Marketing (case scenario, operations has updated as of june8(latest
day) but marketing has not yet updated; note average should equal to
18.3)
June1 (Mon) = 23
June2 (Tue) = 0
June3 (Wed) = 56
June4 (Thur) = 0
June5 (Fri) = 49
June6 (Sat) = " " (blank)
June7 (Sun) = " " (blank)

June 8 onwards is blank.

Inputs from different departments are not aligned. Is it possible to
use the average function and divide by the number of calendar days(as
of june8 - latest date) without having to change the formula everytime
for all departments?

I hope I was able to explain everything clearly. I hope somebody out
there can help me. Thanks! =)




Dave Peterson wrote:
Can you change the original formulas in those cells?

I'm guessing that you have formulas like:
=sheet2!a1

If you replace those formulas with:
=if(sheet2!a1="","",sheet2!a1)

The cells will look blank when the "sending" cells are empty.

And =average() won't include them.

You could write your formula to ignore the cells with 0's in them. But
what
happens if the sending cell really had a 0 in it? You average would be
incorrect.

If you wanted to average the non-zeros in a range:
=sum(a1:a10)/countif(a1:a10,"<"&0)

(adding 0 to the sum won't hurt, so I didn't bother to exclude them.)





ClintonOrlando wrote:

Hi there.
I'm having difficulty getting a cell to accurately show an average of
specific cells with values. it is counting all of the zeros in the
column
and I can't figure out how to make the cell not show zeros without
going
through and deleting each one (hundreds!). (The cells I'm averaging
have
formulas in them where they're copies from other cells and when those
original cells are empty, these cells then show a "0.00")

--

Dave Peterson