help on averaging function

[Guest]

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??

 

[Dave Thomas]

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.

 

[Guest]

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

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


--
regards,
driller

*****
- dive with Jonathan Seagull

 

[Dave Thomas]

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.

 

[MartinW]

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

 

[Guest]

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

 

[Guest]

excuse me Martin,

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

*****
- dive with Jonathan Seagull

 

[Chip Pearson]

Treesy,

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

=AVERAGE(IF(A1:A12>0,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)

 

[Dave Thomas]

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.

 

[Dave Thomas]

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.

 

[Guest]

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

 

[MartinW]

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

 

[Chip Pearson]

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]

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

 

[Guest]

thanks all,
outta here!

--
regards,
driller

*****
- dive with Jonathan Seagull

 

[MartinW]

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]

hehe, have fun!

thanks all,
outta here!

--
regards,
driller

*****
- dive with Jonathan Seagull

 

[Dave Thomas]

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

 

[Dave Thomas]

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.

 

[Guest]

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