hi, my formula looks at a column of values and gets the average.
problem is when there are no values in the column, it gives an error of #VALUE.

the formula is: =AVERAGE(IF(ISERROR(AP155:AP184<>0),"NA",AP155:AP184))

can any1 help me please?

i have kind of got my statement to work,

=ISERROR(AVERAGE(IF(AN155:AN184<>0,"mcdonalds",AN155:AN184<>0)))


it should say mcdonalds, but comes back as True instead. any help on this?

Howdy. That means that there is an error in the formula result. What are you trying to do with your IF statement.

i want the formula to: if the coloumn of numbers are all blank, then the output is zero, otherwise calculate the average price of the values in the column.

Then I think you need something like this:

=IF(AND(MAX(AN155:AN184)=0,MIN(AN155:AN184)=0),0,A VERAGE(AN155:AN184))

hi, my formula looks at a column of values and gets the average.
problem is when there are no values in the column, it gives an error of #VALUE.

the formula is: =AVERAGE(IF(ISERROR(AP155:AP184<>0),"NA",AP155:AP184))

can any1 help me please?

yikes!

are you trying to find the non zero average?

how about

{=AVERAGE(IF(AP155:AP184=0,,AP155:AP184))}

?

you know what an array formula is?

If you begin checking with a few test values, it depends on what "average" you (OP) want. Do you want the average if only two items are filled, then my formula works. If you want the average including all blank cells, then the array formula gives you what you want. They produce different results.

If you begin checking with a few test values, it depends on what "average" you (OP) want. Do you want the average if only two items are filled, then my formula works. If you want the average including all blank cells, then the array formula gives you what you want. They produce different results.


hi, i only want to average the list of values which are over 1, if they are 0, then i dont want it to be averaged.

e.g. my list of values:

40
334
0
35
0
233

i want my average statement to average this list ignoring the 0's, which is what this statement does: =AVERAGE(IF(AP155:AP184<>0, AP155:AP184,""))
. but if my list is made up all of 0 value figures, then it returns a Div# error. instead of this error being displayed i would like it to display a NA or a 0.

any help on this?

any help on this?

{=AVERAGE(IF(A1:A6=0,,A1:A6))}

works

couple of points:-

"" is an empty string and is treated differently to a null (a blank) sometimes

the function IF only works on one cell in a range, you need to turn it into an array formula to IF them all

hi, i have attached the file, using the formula you have supplied darth v, i get 666.667. i should be getting 20,000 as this is the only value in the list.

can you have a look and see whats going with the calculation please?

hi, my formula looks at a column of values and gets the average.
problem is when there are no values in the column, it gives an error of #VALUE.

the formula is: =AVERAGE(IF(ISERROR(AP155:AP184<>0),"NA",AP155:AP184))

can any1 help me please?
Try a sumproduct :-
=SUMPRODUCT((AP155:AP184>0)*AP155:AP184)/SUMPRODUCT(--(AP155:AP184>0))Should work OK and is NOT an array.

thanks helicopter, that sum formula has done the trick.
btw, im a bit confused as how the formula works?

hi, i have attached the file, using the formula you have supplied darth v, i get 666.667. i should be getting 20,000 as this is the only value in the list.

can you have a look and see whats going with the calculation please?


using a null means it does =SUM(A2:A31)/30 = 667

using "" means it does =SUM(A2:A31)/1 = 2000

oh i suppose mine is the same as AVERAGE(a2:a31)

:)

thanks helicopter, that sum formula has done the trick.
btw, im a bit confused as how the formula works?


edit, the formula doesnt work properly if all list is 0. still gives div# error

thanks helicopter, that sum formula has done the trick.
btw, im a bit confused as how the formula works?


edit, the formula doesnt work properly if all list is 0. still gives div# error

if you look in the attached tracker2 file, in the 2nd coloum, the average price returns a div# error. this is what i want to aviod, i would rather have a 0 in place of that div#.

if(sum(ap155:ap184)=0,0,=sumproduct((ap155:ap184>0)*ap155:ap184)/sumproduct(--(ap155:ap184>0)))

helicopter, that throws up an error.

i was thinking, would and Is Error combined with my IF statement make the div0! go away?

Sorry, my mistake the "=" was not moved when I adjusted the formula
=if(sum(ap155:ap184)=0,0,sumproduct((ap155:ap184>0)*ap155:ap184)/sumproduct(--(ap155:ap184>0)))
You don't have an error to detect for so this is unecessary, what you did have is a crap formula (I appologise again), I should have tested it against actual data, The Magenta "0" is what you get if you just have zero's (well actually "nothing greater than 0" (so same with -5 etc.) you could change that to "Empty" (with quotes) if you prefer.

As to how it works :-
Sumproduct allows you to deal with ranges, more than that, it allows you to deal with individual values within ranges normally only available via Array formula, so ...
ap155:ap184>0 tells you if each value is greater than zero, true = yes and false = no
--() is a double uniary which turns true/false into 1/0
Sumprodduct then adds these 1's and 0's to get the count of figures greater than 0 (you could also use countif but seeing as we were using sumproduct anyway ...)
the other bit ## sumproduct((ap155:ap184>0)*ap155:ap184) ## asks again if the value is greater than 0 and mutilplies the result (i.e. 1 or 0) by the individual value.
for example (1,1,0,1,0,1) * (10,100,0,10,0,10) gives a result of (10, 100, 0, 10, 0, 10) which are then summed to 130.
This isn't the best example so assume that the test is that the value must be greater than 3 but smaller than 8
so (0, 1, 1, 1, 0, 0) * (2, 7, 6, 5, 9 , 9) = (0, 7, 6, 5, 0, 0), which sums to 18 :)