J
Jessica Walton
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 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
_______
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]