skipping #DIV/0 when averaging range

Originally Posted by redneck joe

Probably simple, but I have had no luck searching. I do recall seeing a post about this awhile back.

I have a column of formulas pulling info from multiple worksheets..

I am then averaging (or sum, or countif, etc) that column.

Some of the cells contain #DIV/0 of a zero due to no data in the cell being pulled from so the average formula does not work correctly.

I can do =IF(PCHI!$R$3="0","",PCHI!$R$3), but the problem is the quantity of these I would have to do as the column is long and each cell pulls from a differently named sheet AND I pull info to the final destination from multiple other cells within each sheet that would require the same type formula to be typed, and in the case of AVERAGE I do not want a zero returned if nothing is entered.



So I just need to know how to not read the #DIV/0 (or #VALUE) errors that are contained within the range to be averaged.



thanks

joe

Try: =AVERAGE(IF(ISERROR(A1:A30),"",$A$1:$A$30))

Enter it with CTRL+SHIFT+ENTER.

Scott

Originally Posted by redneck joe

perfect.

I see the by entering the ctl shift enter adds the {}. which I have seen before, but what exactly does that do?

Why does it not work just typing those in?

I'm not one to be giving a technical explanation, but basically, the {} indicate that the formula is an array formula. It's just an indicator for when you are looking at the cell so that you know that it is an array formula, as otherwise there isn't really any indicator.

Someone else can probably do a better job of explaining this.

Scott