Is there a way to not have a blank or zero value not be counted in an average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is only
filled in through June. At the very end, I want it to average some of the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month, it
will average 7, so on and so forth. Right now, it is taking the 6 values and
dividing by the 12 cells, even though 6 of them contain nothing. I guess I
want the calculation to change as data is entered without having to change
the formula every month and add the new cell. Is this possible??

Yes. Assuming your data is in A1 thru A12 use an array formula:
=AVERAGE(IF(A1:A12<0,A1:A12,FALSE))
After entering the formula in the cell press CTRL+SHIFT+ENTER
This formula creates an in memory array and replaces blanks and 0's with the
value false.
It puts any numbers in A1:A12 in the array. Then averages the numbers in the
array.
The average function ignores logical values.


"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is
only
filled in through June. At the very end, I want it to average some of the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month,
it
will average 7, so on and so forth. Right now, it is taking the 6 values
and
dividing by the 12 cells, even though 6 of them contain nothing. I guess
I
want the calculation to change as data is entered without having to change
the formula every month and add the new cell. Is this possible??

Hi Treesy,
Not so sure what you're looking for but i give you a starter..

{=average(if(range0,range))}
array entered, C+S+E, remove brackets from above...


--
regards,
driller

*****
- dive with Jonathan Seagull



"Treesy" wrote:

Is there a way to not have a blank or zero value not be counted in an average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is only
filled in through June. At the very end, I want it to average some of the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month, it
will average 7, so on and so forth. Right now, it is taking the 6 values and
dividing by the 12 cells, even though 6 of them contain nothing. I guess I
want the calculation to change as data is entered without having to change
the formula every month and add the new cell. Is this possible??

I should further explain that your array formula will have { and }
surrounding it when you look at it in the formula bar. These identify an
array formula. Do not type these in. Pressing CTRL+SHIFT+ENTER places the {}
around the formula. So if in A1:A5 you had the values 2, 0, 4, blank, 6 the
in-memory array would look like: 2, false, 4, false, 6. The array function
would ignore the false values and average the 3 numbers 2, 4 and 6 for an
average of 4. If you averaged these in the spread sheet column, the average
function would average 2, 0, 4 , 6 for an average of 3.


"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is
only
filled in through June. At the very end, I want it to average some of the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month,
it
will average 7, so on and so forth. Right now, it is taking the 6 values
and
dividing by the 12 cells, even though 6 of them contain nothing. I guess
I
want the calculation to change as data is entered without having to change
the formula every month and add the new cell. Is this possible??

Hi Treesy,

The average function will ignore blanks but not zero values.
So if the formula in those cells is returning 0 alter it to return ""

Alternatively this formula may help.
=SUM(A1:A12)/COUNTIF(A1:A12,"0")
adjust the ranges to suit.

HTH
Martin

"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is
only
filled in through June. At the very end, I want it to average some of the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month,
it
will average 7, so on and so forth. Right now, it is taking the 6 values
and
dividing by the 12 cells, even though 6 of them contain nothing. I guess
I
want the calculation to change as data is entered without having to change
the formula every month and add the new cell. Is this possible??

now u can use Dave Thomas solution,,,

kinda weird of me why i use the "0", rather than "<0" for your worth of
data..

--
regards,
driller

*****
- dive with Jonathan Seagull



"Treesy" wrote:

Is there a way to not have a blank or zero value not be counted in an average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is only
filled in through June. At the very end, I want it to average some of the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month, it
will average 7, so on and so forth. Right now, it is taking the 6 values and
dividing by the 12 cells, even though 6 of them contain nothing. I guess I
want the calculation to change as data is entered without having to change
the formula every month and add the new cell. Is this possible??

excuse me Martin,

i send a late post, not seeing your one..
--
regards,
driller

*****
- dive with Jonathan Seagull



"MartinW" wrote:

Hi Treesy,

The average function will ignore blanks but not zero values.
So if the formula in those cells is returning 0 alter it to return ""

Alternatively this formula may help.
=SUM(A1:A12)/COUNTIF(A1:A12,"0")
adjust the ranges to suit.

HTH
Martin

"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is
only
filled in through June. At the very end, I want it to average some of the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month,
it
will average 7, so on and so forth. Right now, it is taking the 6 values
and
dividing by the 12 cells, even though 6 of them contain nothing. I guess
I
want the calculation to change as data is entered without having to change
the formula every month and add the new cell. Is this possible??

Treesy,

I use that exact need as the example on my Array Formulas web page. The
formula you want is

=AVERAGE(IF(A1:A120,A1:A12,FALSE))

Since this is an array formula, you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces { }.

Change the range A1:A12 to the range containing your data.

See www.cpearson.com/Excel/ArrayFormulas.aspx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is
only
filled in through June. At the very end, I want it to average some of the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month,
it
will average 7, so on and so forth. Right now, it is taking the 6 values
and
dividing by the 12 cells, even though 6 of them contain nothing. I guess
I
want the calculation to change as data is entered without having to change
the formula every month and add the new cell. Is this possible??

Are you assuming there are no negative numbers? Treesy doesn't state that
there aren't. Treesy just wanted to eliminate 0's and blanks.

"Chip Pearson" wrote in message
...
Treesy,

I use that exact need as the example on my Array Formulas web page. The
formula you want is

=AVERAGE(IF(A1:A120,A1:A12,FALSE))

Since this is an array formula, you must press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit it
later. If you do this properly, Excel will display the formula enclosed in
curly braces { }.

Change the range A1:A12 to the range containing your data.

See www.cpearson.com/Excel/ArrayFormulas.aspx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is
only
filled in through June. At the very end, I want it to average some of
the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month,
it
will average 7, so on and so forth. Right now, it is taking the 6 values
and
dividing by the 12 cells, even though 6 of them contain nothing. I guess
I
want the calculation to change as data is entered without having to
change
the formula every month and add the new cell. Is this possible??

Are you assuming there are no negative numbers? Treesy doesn't state that
there aren't. Treesy just wanted to eliminate 0's and blanks.

"MartinW" wrote in message
...
Hi Treesy,

The average function will ignore blanks but not zero values.
So if the formula in those cells is returning 0 alter it to return ""

Alternatively this formula may help.
=SUM(A1:A12)/COUNTIF(A1:A12,"0")
adjust the ranges to suit.

HTH
Martin

"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is
only
filled in through June. At the very end, I want it to average some of
the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month,
it
will average 7, so on and so forth. Right now, it is taking the 6 values
and
dividing by the 12 cells, even though 6 of them contain nothing. I guess
I
want the calculation to change as data is entered without having to
change
the formula every month and add the new cell. Is this possible??

sorry Sir Chip Pearson,

not aware of that,
yet the starter solution i gave seems the same considering that (just
guessing its the positive),
my logic is if 0 is out of the numeric range, then either the positive or
negative range will be a selected criteria
*and not necessarily both of it...* unless deem logical...in any sense.

maybe treesy has to specify now which range of data he/she prefer "the
positive or the negative"

cordially,
--
regards,
driller

*****
- dive with Jonathan Seagull



"Chip Pearson" wrote:

Treesy,

I use that exact need as the example on my Array Formulas web page. The
formula you want is

=AVERAGE(IF(A1:A120,A1:A12,FALSE))

Since this is an array formula, you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces { }.

Change the range A1:A12 to the range containing your data.

See www.cpearson.com/Excel/ArrayFormulas.aspx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is
only
filled in through June. At the very end, I want it to average some of the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month,
it
will average 7, so on and so forth. Right now, it is taking the 6 values
and
dividing by the 12 cells, even though 6 of them contain nothing. I guess
I
want the calculation to change as data is entered without having to change
the formula every month and add the new cell. Is this possible??

You're right Dave, I didn't allow for negatives.

So if the remaining cells are all blanks
=AVERAGE(A1:A12)

if the remaining cells are all zeroes
=SUM(A1:A12)/COUNTIF(A1:A12,"<0")

if the remaining cells are a mixture of blanks and zeroes
Then you will need Dave's array formula.


Regards
Martin

"Dave Thomas" wrote in message
t...
Are you assuming there are no negative numbers? Treesy doesn't state that
there aren't. Treesy just wanted to eliminate 0's and blanks.

"MartinW" wrote in message
...
Hi Treesy,

The average function will ignore blanks but not zero values.
So if the formula in those cells is returning 0 alter it to return ""

Alternatively this formula may help.
=SUM(A1:A12)/COUNTIF(A1:A12,"0")
adjust the ranges to suit.

HTH
Martin

"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is
only
filled in through June. At the very end, I want it to average some of
the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month,
it
will average 7, so on and so forth. Right now, it is taking the 6
values and
dividing by the 12 cells, even though 6 of them contain nothing. I
guess I
want the calculation to change as data is entered without having to
change
the formula every month and add the new cell. Is this possible??

Are you assuming there are no negative numbers?

Actually, yes I am assuming that. In practical terms, it wouldn't make sense
to average both positive and negative numbers and omit zero.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Dave Thomas" wrote in message
t...
Are you assuming there are no negative numbers? Treesy doesn't state that
there aren't. Treesy just wanted to eliminate 0's and blanks.

"Chip Pearson" wrote in message
...
Treesy,

I use that exact need as the example on my Array Formulas web page. The
formula you want is

=AVERAGE(IF(A1:A120,A1:A12,FALSE))

Since this is an array formula, you must press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit it
later. If you do this properly, Excel will display the formula enclosed
in curly braces { }.

Change the range A1:A12 to the range containing your data.

See www.cpearson.com/Excel/ArrayFormulas.aspx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is
only
filled in through June. At the very end, I want it to average some of
the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month,
it
will average 7, so on and so forth. Right now, it is taking the 6
values and
dividing by the 12 cells, even though 6 of them contain nothing. I
guess I
want the calculation to change as data is entered without having to
change
the formula every month and add the new cell. Is this possible??

His data could be all negative numbers for all I know. He said he just
wanted to eliminate 0's and blanks.
"Chip Pearson" wrote in message
...
Are you assuming there are no negative numbers?

Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Dave Thomas" wrote in message
t...
Are you assuming there are no negative numbers? Treesy doesn't state that
there aren't. Treesy just wanted to eliminate 0's and blanks.

"Chip Pearson" wrote in message
...
Treesy,

I use that exact need as the example on my Array Formulas web page. The
formula you want is

=AVERAGE(IF(A1:A120,A1:A12,FALSE))

Since this is an array formula, you must press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this properly, Excel will display the formula
enclosed in curly braces { }.

Change the range A1:A12 to the range containing your data.

See www.cpearson.com/Excel/ArrayFormulas.aspx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is
only
filled in through June. At the very end, I want it to average some of
the
data. The formula has 12 cells that I want averaged but if one of
those
cells is blank or zero, I want it ignored. 6 of the 12 cells have
data.
Right now, I want it to average the 6 cells that have data. Next
month, it
will average 7, so on and so forth. Right now, it is taking the 6
values and
dividing by the 12 cells, even though 6 of them contain nothing. I
guess I
want the calculation to change as data is entered without having to
change
the formula every month and add the new cell. Is this possible??

thanks all,
outta here!

--
regards,
driller

*****
- dive with Jonathan Seagull



"driller" wrote:

sorry Sir Chip Pearson,

not aware of that,
yet the starter solution i gave seems the same considering that (just
guessing its the positive),
my logic is if 0 is out of the numeric range, then either the positive or
negative range will be a selected criteria
*and not necessarily both of it...* unless deem logical...in any sense.

maybe treesy has to specify now which range of data he/she prefer "the
positive or the negative"

cordially,
--
regards,
driller

*****
- dive with Jonathan Seagull



"Chip Pearson" wrote:

Treesy,

I use that exact need as the example on my Array Formulas web page. The
formula you want is

=AVERAGE(IF(A1:A120,A1:A12,FALSE))

Since this is an array formula, you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces { }.

Change the range A1:A12 to the range containing your data.

See www.cpearson.com/Excel/ArrayFormulas.aspx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is
only
filled in through June. At the very end, I want it to average some of the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month,
it
will average 7, so on and so forth. Right now, it is taking the 6 values
and
dividing by the 12 cells, even though 6 of them contain nothing. I guess
I
want the calculation to change as data is entered without having to change
the formula every month and add the new cell. Is this possible??

Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero.

Hi Chip,

Where would zero come into the following?

1st Month: $100 loss
2nd Month: $100 loss
3rd Month: $500 profit

3 monthly average profit would be $100 profit.


Regards
Martin

hehe, have fun!
"driller" wrote in message
...
thanks all,
outta here!

--
regards,
driller

*****
- dive with Jonathan Seagull



"driller" wrote:

sorry Sir Chip Pearson,

not aware of that,
yet the starter solution i gave seems the same considering that (just
guessing its the positive),
my logic is if 0 is out of the numeric range, then either the positive or
negative range will be a selected criteria
*and not necessarily both of it...* unless deem logical...in any sense.

maybe treesy has to specify now which range of data he/she prefer "the
positive or the negative"

cordially,
--
regards,
driller

*****
- dive with Jonathan Seagull



"Chip Pearson" wrote:

Treesy,

I use that exact need as the example on my Array Formulas web page. The
formula you want is

=AVERAGE(IF(A1:A120,A1:A12,FALSE))

Since this is an array formula, you must press CTRL SHIFT ENTER rather
than
just ENTER when you first enter the formula and whenever you edit it
later.
If you do this properly, Excel will display the formula enclosed in
curly
braces { }.

Change the range A1:A12 to the range containing your data.

See www.cpearson.com/Excel/ArrayFormulas.aspx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it
is
only
filled in through June. At the very end, I want it to average some
of the
data. The formula has 12 cells that I want averaged but if one of
those
cells is blank or zero, I want it ignored. 6 of the 12 cells have
data.
Right now, I want it to average the 6 cells that have data. Next
month,
it
will average 7, so on and so forth. Right now, it is taking the 6
values
and
dividing by the 12 cells, even though 6 of them contain nothing. I
guess
I
want the calculation to change as data is entered without having to
change
the formula every month and add the new cell. Is this possible??

Actually the average would be 100, but your point is well taken. You can
have a mixture of positive and negative with no zeroes.


"MartinW" wrote in message
...
Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero.

Hi Chip,

Where would zero come into the following?

1st Month: $100 loss
2nd Month: $100 loss
3rd Month: $500 profit

3 monthly average profit would be $100 profit.


Regards
Martin

I misread your average. You are correct. But the point stands: it's quite
possible to have positive numbers and negative numbers in an average.

"MartinW" wrote in message
...
Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero.

Hi Chip,

Where would zero come into the following?

1st Month: $100 loss
2nd Month: $100 loss
3rd Month: $500 profit

3 monthly average profit would be $100 profit.


Regards
Martin

Hi Martin,

the sample is great, yet if there is no category for a *0* (b/w loss and
profit), then there will be be no existing *0* as a Data...

kinda llike..give me *0* but its not categorized in the Data list***then
what is the *0* data stands for? <g

--
regards,
driller

*****
- dive with Jonathan Seagull



"MartinW" wrote:

Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero.

Hi Chip,

Where would zero come into the following?

1st Month: $100 loss
2nd Month: $100 loss
3rd Month: $500 profit

3 monthly average profit would be $100 profit.


Regards
Martin

for the first time!!<g
--
regards,
driller

*****
- dive with Jonathan Seagull



"Dave Thomas" wrote:

hehe, have fun!
"driller" wrote in message
...
thanks all,
outta here!

--
regards,
driller

*****
- dive with Jonathan Seagull



"driller" wrote:

sorry Sir Chip Pearson,

not aware of that,
yet the starter solution i gave seems the same considering that (just
guessing its the positive),
my logic is if 0 is out of the numeric range, then either the positive or
negative range will be a selected criteria
*and not necessarily both of it...* unless deem logical...in any sense.

maybe treesy has to specify now which range of data he/she prefer "the
positive or the negative"

cordially,
--
regards,
driller

*****
- dive with Jonathan Seagull



"Chip Pearson" wrote:

Treesy,

I use that exact need as the example on my Array Formulas web page. The
formula you want is

=AVERAGE(IF(A1:A120,A1:A12,FALSE))

Since this is an array formula, you must press CTRL SHIFT ENTER rather
than
just ENTER when you first enter the formula and whenever you edit it
later.
If you do this properly, Excel will display the formula enclosed in
curly
braces { }.

Change the range A1:A12 to the range containing your data.

See www.cpearson.com/Excel/ArrayFormulas.aspx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it
is
only
filled in through June. At the very end, I want it to average some
of the
data. The formula has 12 cells that I want averaged but if one of
those
cells is blank or zero, I want it ignored. 6 of the 12 cells have
data.
Right now, I want it to average the 6 cells that have data. Next
month,
it
will average 7, so on and so forth. Right now, it is taking the 6
values
and
dividing by the 12 cells, even though 6 of them contain nothing. I
guess
I
want the calculation to change as data is entered without having to
change
the formula every month and add the new cell. Is this possible??

To extend your example:

Month 1. $200 Loss
Month 2. $100 Loss
Month 3. $0 Broke even
Month 4. $200 Profit
Month 5. $400 Profit.

Average Profit (including 0), $60, (excluding 0), $75.

"MartinW" wrote in message
...
Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero.

Hi Chip,

Where would zero come into the following?

1st Month: $100 loss
2nd Month: $100 loss
3rd Month: $500 profit

3 monthly average profit would be $100 profit.


Regards
Martin

I average patients from the time they are seen in relationship to their
appointments. Some times they are taken in early, some times late, however
the goal is the average of 0. meaning they are taken in on time. ;0)



"MartinW" wrote in message
...
Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero.

Hi Chip,

Where would zero come into the following?

1st Month: $100 loss
2nd Month: $100 loss
3rd Month: $500 profit

3 monthly average profit would be $100 profit.


Regards
Martin

Dave,
Which one of the similar sample average deems to fit the OP,,timewise...?
just curious really!

--
regards,
driller

*****
- dive with Jonathan Seagull



"Dave Thomas" wrote:

To extend your example:

Month 1. $200 Loss
Month 2. $100 Loss
Month 3. $0 Broke even
Month 4. $200 Profit
Month 5. $400 Profit.

Average Profit (including 0), $60, (excluding 0), $75.

"MartinW" wrote in message
...
Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero.

Hi Chip,

Where would zero come into the following?

1st Month: $100 loss
2nd Month: $100 loss
3rd Month: $500 profit

3 monthly average profit would be $100 profit.


Regards
Martin

Meebers,
u mean timewise..isnt it? but with 0.
--
regards,
driller

*****
- dive with Jonathan Seagull



"Meebers" wrote:

I average patients from the time they are seen in relationship to their
appointments. Some times they are taken in early, some times late, however
the goal is the average of 0. meaning they are taken in on time. ;0)



"MartinW" wrote in message
...
Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero.

Hi Chip,

Where would zero come into the following?

1st Month: $100 loss
2nd Month: $100 loss
3rd Month: $500 profit

3 monthly average profit would be $100 profit.


Regards
Martin

Yeah.
The only formula to work correctly in this situation is a simple
=AVERAGE(A1:A12)
With a caveat that zero values MUST be shown as zero and not blank
and blank values MUST be shown as blank and not zero.

Month 1. $200 Loss
Month 2. $100 Loss
Month 3. $0 Broke even
Month 4. $200 Profit
Month 5. $400 Profit.
Month 6. ""
Month 7. ""
Month 8. ""
etc.

Regards
Martin

The goal is to see the patients on time. If we are late, it is a negative
amount, early it is a positive. Yes the differences are in times, results
are formated as a number since negative #'s will not format as hh:mm.

"driller" wrote in message
...
Meebers,
u mean timewise..isnt it? but with 0.
--
regards,
driller

*****
- dive with Jonathan Seagull



"Meebers" wrote:

I average patients from the time they are seen in relationship to their
appointments. Some times they are taken in early, some times late,
however
the goal is the average of 0. meaning they are taken in on time. ;0)



"MartinW" wrote in message
...
Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero.

Hi Chip,

Where would zero come into the following?

1st Month: $100 loss
2nd Month: $100 loss
3rd Month: $500 profit

3 monthly average profit would be $100 profit.


Regards
Martin

"Dave Thomas" wrote...
Are you assuming there are no negative numbers? Treesy doesn't state that
there aren't. Treesy just wanted to eliminate 0's and blanks.
....

Name a physical, financial or not entirely esoteric process that could
produce positive and negative values but not zero values. The point is that
if a set of numbers can include positive and negative values, they very
likely could also contain legitimate zero values that shouldn't be ignored.

As for blanks, they're always ignored unless you mean "" and are (mis)using
AVERAGEA instead of AVERAGE or are averaging values through an external
reference to a closed workbook.

"Dave Thomas" wrote...
His data could be all negative numbers for all I know. He said he just
wanted to eliminate 0's and blanks.
....

In that case,

=AVERAGE(IF(dataset<0,dataset))

or getting really pedantic,

=CHOOSE(1+SIGN(COUNTIF(dataset,"<0"))+2*SIGN(COUNT IF(dataset,"0")),
0+N("no positivess or negatives, so live with 0"),
AVERAGE(IF(dataset<0,dataset)),AVERAGE(IF(dataset 0,dataset)),
AVERAGE(dataset)+N("both positives and negatives, so should include 0s"))

"Dave Thomas" wrote...
Actually the average would be 100, but your point is well taken. You can
have a mixture of positive and negative with no zeroes.
....

Actually the example completely misses the point. It'd be necessary to use
the column containing profit or loss in order to change signs. And the
lovely example provides a very nearly meaningless average - cumulative
return divided by number of periods is +100, but 2 out of 3 periods
generated negatives.

Further, there are no zeros. If there are no zeros, then just use AVERAGE.
If, however, there are zeros, e.g.,

Month 1 100 loss
Month 2 100 loss
Month 3 500 profit
Month 4 0 no return
Month 5 0 no return

is the average monthly return still 100 or is it now 60?

This has everything to do with real world processes. Averages of all
negatives, or averages of all positives, or averages of all numbers
(positive, negative AND zero) make sense. Averages of positives and
negatives but not zero never make sense.

"Meebers" wrote...
The goal is to see the patients on time. If we are late, it is a negative
amount, early it is a positive. Yes the differences are in times, results
are formated as a number since negative #'s will not format as hh:mm.
....

TIf you saw 60 patients, 50 on time, 8 5 minutes early and 2 30 minutes
late, would you want the average to be 20 seconds late or 2 miutes late? The
first includes the 50 on time (0) values, the latter doesn't.

You missed the point entirely. You should have looked at the original
message.

"Harlan Grove" wrote in message
...
"Dave Thomas" wrote...
Actually the average would be 100, but your point is well taken. You can
have a mixture of positive and negative with no zeroes.
...

Actually the example completely misses the point. It'd be necessary to use
the column containing profit or loss in order to change signs. And the
lovely example provides a very nearly meaningless average - cumulative
return divided by number of periods is +100, but 2 out of 3 periods
generated negatives.

Further, there are no zeros. If there are no zeros, then just use AVERAGE.
If, however, there are zeros, e.g.,

Month 1 100 loss
Month 2 100 loss
Month 3 500 profit
Month 4 0 no return
Month 5 0 no return

is the average monthly return still 100 or is it now 60?

This has everything to do with real world processes. Averages of all
negatives, or averages of all positives, or averages of all numbers
(positive, negative AND zero) make sense. Averages of positives and
negatives but not zero never make sense.

Does this crap go on all the time in these newsgroups?

"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is
only
filled in through June. At the very end, I want it to average some of the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month,
it
will average 7, so on and so forth. Right now, it is taking the 6 values
and
dividing by the 12 cells, even though 6 of them contain nothing. I guess
I
want the calculation to change as data is entered without having to change
the formula every month and add the new cell. Is this possible??

Once again, read the original message. In my response to it, I gave Treesy
what Treesy asked for. I don't care about your world of negatives, positives
and zeros. So calm down, read the original message and you'll see that my
response provided the answer sought. I do not know what Treesy's data is
used for nor do I care.


"Harlan Grove" wrote in message
...
"Dave Thomas" wrote...
Are you assuming there are no negative numbers? Treesy doesn't state that
there aren't. Treesy just wanted to eliminate 0's and blanks.
...

Name a physical, financial or not entirely esoteric process that could
produce positive and negative values but not zero values. The point is
that if a set of numbers can include positive and negative values, they
very likely could also contain legitimate zero values that shouldn't be
ignored.

As for blanks, they're always ignored unless you mean "" and are
(mis)using AVERAGEA instead of AVERAGE or are averaging values through an
external reference to a closed workbook.

Once again, read the original message. In my response to it, I gave Treesy
what Treesy asked for. I don't care about your world of negatives, positives
and zeros. So calm down, read the original message and you'll see that my
response provided the answer sought. I do not know what Treesy's data is
used for nor do I care.

"Harlan Grove" wrote in message
...
"Dave Thomas" wrote...
Are you assuming there are no negative numbers? Treesy doesn't state that
there aren't. Treesy just wanted to eliminate 0's and blanks.
...

Name a physical, financial or not entirely esoteric process that could
produce positive and negative values but not zero values. The point is
that if a set of numbers can include positive and negative values, they
very likely could also contain legitimate zero values that shouldn't be
ignored.

As for blanks, they're always ignored unless you mean "" and are
(mis)using AVERAGEA instead of AVERAGE or are averaging values through an
external reference to a closed workbook.

Once again, read the original message. In my response to it, I gave Treesy
what Treesy asked for. I don't care about your world of negatives, positives
and zeros. So calm down, read the original message and you'll see that my
response provided the answer sought. I do not know what Treesy's data is
used for nor do I care.

"Harlan Grove" wrote in message
...
"Dave Thomas" wrote...
Actually the average would be 100, but your point is well taken. You can
have a mixture of positive and negative with no zeroes.
...

Actually the example completely misses the point. It'd be necessary to use
the column containing profit or loss in order to change signs. And the
lovely example provides a very nearly meaningless average - cumulative
return divided by number of periods is +100, but 2 out of 3 periods
generated negatives.

Further, there are no zeros. If there are no zeros, then just use AVERAGE.
If, however, there are zeros, e.g.,

Month 1 100 loss
Month 2 100 loss
Month 3 500 profit
Month 4 0 no return
Month 5 0 no return

is the average monthly return still 100 or is it now 60?

This has everything to do with real world processes. Averages of all
negatives, or averages of all positives, or averages of all numbers
(positive, negative AND zero) make sense. Averages of positives and
negatives but not zero never make sense.

"Dave Thomas" wrote...
You missed the point entirely. You should have looked at the original
message.
....

I did. The OP's process is flawed if future months contain 0s rather than
are blank. That should be corrected by making those cells contain nothing or
"", which AVERAGE automatically skips.

So what's the correct answer if any of the OP's HISTORICAL months contain
0s?

"Dave Thomas" wrote...
Once again, read the original message. In my response to it, I gave Treesy
what Treesy asked for. . . .

Not always the best thing to do. Some of us share our own experience, which
includes advice for avoiding common unforseen errors. I accept the fact that
you don't understand that averaging positive and negative values but not
zeros is almost always an error, but not everyone who responds in this
newsgroup has experience (or learned from it).

. . . I don't care about your world of negatives, positives and zeros. . .

And you're obviously proud of missing the point.

. . . So calm down, read the original message and you'll see that my
response provided the answer sought. I do not know what Treesy's data is
used for nor do I care.

Which is why your response should be ignored.

"Dave Thomas" wrote...
Does this crap go on all the time in these newsgroups?
....

Only until the obtuse party finally realizes how obtuse they're being.

Then address your concerns to Treesy, not to me!

You sound like the kind of person who blames the messenger for the message.


"Harlan Grove" wrote in message
...
"Dave Thomas" wrote...
Once again, read the original message. In my response to it, I gave Treesy
what Treesy asked for. . . .

Not always the best thing to do. Some of us share our own experience,
which includes advice for avoiding common unforseen errors. I accept the
fact that you don't understand that averaging positive and negative values
but not zeros is almost always an error, but not everyone who responds in
this newsgroup has experience (or learned from it).

. . . I don't care about your world of negatives, positives and zeros. .
.

And you're obviously proud of missing the point.

. . . So calm down, read the original message and you'll see that my
response provided the answer sought. I do not know what Treesy's data is
used for nor do I care.

Which is why your response should be ignored.