Help with Formula Scenarios from yesterday

[Guest]

Repost with responses

Hi AlfD

Well, here's the problem, all of the cells which are being averaged contain formulas which until other cells are filled weekly, will contain zeros. So, I wrote those to ignore the zeros. I've instructed the person who will be populating the weekly, currently empty cells to use "n/a" or "---" to show that the cell has been addressed but will not contain a value, so I'm in a quandry. I think it's best to ignore the zeros, but what's making my averages come up wrong. So, I think leave that condition alone.

What I need, AlfD, is something added to the formula which will get rid of the current "#DIV/0!" error which exists because of the zeros that are currently holding place in the thirteen cells. Read below and see what you think. How can I combine a condition that will take away the "#DIV/0"

Here's the current formula again

={AVERAGE(IF(R20:R32<>0,R20:R32))} using [CTRL-SHIFT-RETURN] to create an array formul

I've tried:=AVERAGE(IF(R20:R32<>0,R20:R32),(IF(ISERROR(R20:R32),,(R20:R32)))
and I can't figure out what I'm doing wrong...when there's only a 46 in one field of thirteen, it's giving me an average of 7??? HELP

Jessica

_______

Hi

Are you wanting to accept _any_ value in R20:R32
Your present array formula expressly says "not = to zero"
If positive,negative AND zero values are to be handled, is there a nee
for a condition at all
=AVERAGE(R20:R32) (ordinary enter) will, for example, average 12 and
and give 6. Is that what you want it to do? It will also ignor
blanks

_______

Hi Guys

Many thanks for the help yesterday. Here's today's wrinkle

I have the following formula in use: ={AVERAGE(IF(R20:R32<>0,R20:R32))} (using CTRL-SHIFT-ENTER) to
average a column of 13 numbers (quarterly totals). I know that this formula covers the possibility that my
numbers are more than zero, less than zero, and if the field is blank, the average command ignores blank
fields. What if the fields DO CONTAIN zeros? How can I insert an if statement that will: 1( allow the zeros
without giving me an error msg, and 2) still have accurate averages

What brought this to my attention is that I had only one entry, the number 46 in the table and it was giving me
an average of 7??? How is this possible

Also, in another column I have this formula: ={AVERAGE(IF(ISERROR(Q20:Q32),,(Q20:Q32)))}, with the
same concerns. Will my averages be true if 1) there are zeros leading into this average and 2) if there are
blank fields [avg. command handles this one, right]

 

[Jason Morin]

=AVERAGE(IF(ISNUMBER(R20:R32),IF(R20:R32<>0,R20:R32)))

HTH
Jason
Atlanta, GA

-----Original Message-----
Repost with responses:

Hi AlfD:

Well, here's the problem, all of the cells which are

being averaged contain formulas which until other cells
are filled weekly, will contain zeros. So, I wrote those
to ignore the zeros. I've instructed the person who will
be populating the weekly, currently empty cells to
use "n/a" or "---" to show that the cell has been
addressed but will not contain a value, so I'm in a
quandry. I think it's best to ignore the zeros, but
what's making my averages come up wrong. So, I think
leave that condition alone.

What I need, AlfD, is something added to the formula

which will get rid of the current "#DIV/0!" error which
exists because of the zeros that are currently holding
place in the thirteen cells. Read below and see what you
think. How can I combine a condition that will take away
the "#DIV/0"?

Here's the current formula again:

={AVERAGE(IF(R20:R32<>0,R20:R32))} using [CTRL-SHIFT-

RETURN] to create an array formula

I've tried:=AVERAGE(IF(R20:R32<>0,R20:R32),(IF(ISERROR (R20:R32),,(R20:R32))))
and I can't figure out what I'm doing wrong...when

there's only a 46 in one field of thirteen, it's giving me
an average of 7??? HELP!

Jessica

________

Hi!

Are you wanting to accept _any_ value in R20:R32?
Your present array formula expressly says "not = to zero".
If positive,negative AND zero values are to be handled, is there a need
for a condition at all?
=AVERAGE(R20:R32) (ordinary enter) will, for example, average 12 and 0
and give 6. Is that what you want it to do? It will also ignore
blanks.

________

Hi Guys:

Many thanks for the help yesterday. Here's today's

wrinkle:

I have the following formula in use: ={AVERAGE(IF

average a column of 13 numbers (quarterly totals). I know that this formula covers the possibility that my
numbers are more than zero, less than zero, and if the field is blank, the average command ignores blank
fields. What if the fields DO CONTAIN zeros? How can I insert an if statement that will: 1( allow the zeros
without giving me an error msg, and 2) still have

accurate averages?

What brought this to my attention is that I had only one entry, the number 46 in the table and it was giving me
an average of 7??? How is this possible?

Also, in another column I have this formula: ={AVERAGE (IF(ISERROR(Q20:Q32),,(Q20:Q32)))}, with the
same concerns. Will my averages be true if 1) there are zeros leading into this average and 2) if there are
blank fields [avg. command handles this one, right].

.

 

[Guest]

Thanks, Jason. The formula works, but when the data fields populating the totals which populate this formula are empty, it still gives me a #DIV/0! msg. Any way of avoiding that? I just don't think it looks nice when printed. I really don't care of the field is blank or zeroed, etc...just don't like seeing an error msg. in print I guess

Let me know

Jessic

----- Jason Morin wrote: ----

=AVERAGE(IF(ISNUMBER(R20:R32),IF(R20:R32<>0,R20:R32))

HT
Jaso
Atlanta, G

-----Original Message----
Repost with responses

being averaged contain formulas which until other cells
are filled weekly, will contain zeros. So, I wrote those
to ignore the zeros. I've instructed the person who will
be populating the weekly, currently empty cells to
use "n/a" or "---" to show that the cell has been
addressed but will not contain a value, so I'm in a
quandry. I think it's best to ignore the zeros, but
what's making my averages come up wrong. So, I think
leave that condition alone.which will get rid of the current "#DIV/0!" error which
exists because of the zeros that are currently holding
place in the thirteen cells. Read below and see what you
think. How can I combine a condition that will take away
the "#DIV/0"

Here's the current formula again
={AVERAGE(IF(R20:R32<>0,R20:R32))} using [CTRL-SHIFT RETURN] to create an array formul
I've tried:=AVERAGE(IF(R20:R32<>0,R20:R32),(IF(ISERRO

(R20:R32),,(R20:R32)))
and I can't figure out what I'm doing wrong...when

there's only a 46 in one field of thirteen, it's giving me
an average of 7??? HELP

Jessica
_______
Hi
Your present array formula expressly says "not = to zero"
If positive,negative AND zero values are to be handled, is there a nee
for a condition at all
=AVERAGE(R20:R32) (ordinary enter) will, for example, average 12 and
and give 6. Is that what you want it to do? It will also ignor
blanks

average a column of 13 numbers (quarterly totals). I know that this formula covers the possibility that my
numbers are more than zero, less than zero, and if the field is blank, the average command ignores blank
fields. What if the fields DO CONTAIN zeros? How can I insert an if statement that will: 1( allow the zeros
without giving me an error msg, and 2) still have accurate averages

What brought this to my attention is that I had only one

entry, the number 46 in the table and it was giving me
an average of 7??? How is this possible

Also, in another column I have this formula: ={AVERAG

(IF(ISERROR(Q20:Q32),,(Q20:Q32)))}, with the
same concerns. Will my averages be true if 1) there are zeros leading into this average and 2) if there are
blank fields [avg. command handles this one, right]

 

[Dave Peterson]

Just count the number of numbers first:

=if(count(r20:r32)=0,"",AVERAGE(IF(ISNUMBER(R20:R32),IF(R20:R32<>0,R20:R32))))
(still an array formula)

Thanks, Jason. The formula works, but when the data fields populating the totals which populate this formula are empty, it still gives me a #DIV/0! msg. Any way of avoiding that? I just don't think it looks nice when printed. I really don't care of the field is blank or zeroed, etc...just don't like seeing an error msg. in print I guess.

Let me know.

Jessica

----- Jason Morin wrote: -----

=AVERAGE(IF(ISNUMBER(R20:R32),IF(R20:R32<>0,R20:R32)))

HTH
Jason
Atlanta, GA

-----Original Message-----
Repost with responses:

being averaged contain formulas which until other cells
are filled weekly, will contain zeros. So, I wrote those
to ignore the zeros. I've instructed the person who will
be populating the weekly, currently empty cells to
use "n/a" or "---" to show that the cell has been
addressed but will not contain a value, so I'm in a
quandry. I think it's best to ignore the zeros, but
what's making my averages come up wrong. So, I think
leave that condition alone.which will get rid of the current "#DIV/0!" error which
exists because of the zeros that are currently holding
place in the thirteen cells. Read below and see what you
think. How can I combine a condition that will take away
the "#DIV/0"?

Here's the current formula again:
={AVERAGE(IF(R20:R32<>0,R20:R32))} using [CTRL-SHIFT- RETURN] to create an array formula
I've tried:=AVERAGE(IF(R20:R32<>0,R20:R32),(IF(ISERROR

(R20:R32),,(R20:R32))))
and I can't figure out what I'm doing wrong...when

there's only a 46 in one field of thirteen, it's giving me
an average of 7??? HELP!

Jessica
________
Hi!
Are you wanting to accept _any_ value in R20:R32?
Your present array formula expressly says "not = to zero".
If positive,negative AND zero values are to be handled, is there a need
for a condition at all?
=AVERAGE(R20:R32) (ordinary enter) will, for example, average 12 and 0
and give 6. Is that what you want it to do? It will also ignore
blanks.
Alf
________
Hi Guys:
Many thanks for the help yesterday. Here's today's wrinkle:
I have the following formula in use: ={AVERAGE(IF

average a column of 13 numbers (quarterly totals). I know that this formula covers the possibility that my
numbers are more than zero, less than zero, and if the field is blank, the average command ignores blank
fields. What if the fields DO CONTAIN zeros? How can I insert an if statement that will: 1( allow the zeros
without giving me an error msg, and 2) still have accurate averages?
What brought this to my attention is that I had only one entry, the number 46 in the table and it was giving me
an average of 7??? How is this possible?
Also, in another column I have this formula: ={AVERAGE (IF(ISERROR(Q20:Q32),,(Q20:Q32)))}, with the
same concerns. Will my averages be true if 1) there are zeros leading into this average and 2) if there are
blank fields [avg. command handles this one, right].
.