do not average cells that have "0" value
 

hi community

can some one kindly tell me why my output shows .. #N/A

the scenario is such:

I have 5 rows of numbers, example:

A1 = 1
A2 = 2
A3 = 0
A4 = 3
A5 = 4

I wanted to average out the 5 rows but I do not want the "A3" be inside the
average formula because it has a "0" value
I remembered seen this question with answer before in the Discussion Group,
and I tried it myself with this formula:

=average(if(a1:a5<0,a1:a5))

but the output shows "#N/A" and i checked the Error, it indicated the "#N/A"
is at the first set of cell range - a1:a5

can somebody kindly help to explain to me

thanks and very much appreciated :)
--
oldLearner57

do not average cells that have "0" value
 

Your formula =average(if(a1:a5<0,a1:a5)) is correct,
but you must commit it with Ctrl+Shift+Enter, not just Enter.

HTH
--
AP


"tikchye_oldLearner57" a
écrit dans le message de news:
...
hi community

can some one kindly tell me why my output shows .. #N/A

the scenario is such:

I have 5 rows of numbers, example:

A1 = 1
A2 = 2
A3 = 0
A4 = 3
A5 = 4

I wanted to average out the 5 rows but I do not want the "A3" be inside
the
average formula because it has a "0" value
I remembered seen this question with answer before in the Discussion
Group,
and I tried it myself with this formula:

=average(if(a1:a5<0,a1:a5))

but the output shows "#N/A" and i checked the Error, it indicated the
"#N/A"
is at the first set of cell range - a1:a5

can somebody kindly help to explain to me

thanks and very much appreciated :)
--
oldLearner57