Using Average Formula

[NTBC]

I am trying to create a formula that will calculate a running average. The problem I am having is figure out how to exclude the cells containing #DIV/0!. I have cells that contain the above error message, but they are there because data has not yet been entered, but I want to calculate the average on the data I have available at the time. If anyone can offer any help I would greatly appreciate it.

 

[sfvb]

Change the formulas that do dividing to test for the divisor being zero. For example, if cell C1 contained the formula

=A1/B1

change it to

=IF(B1<>0,A1/B1,&quot;&quot;)

. Not only will this make the Average function work, but it will make your application look more professional.

Steve

 

[tahilg]

Steve,

Won't your solution give the wrong result? Won't the previously #DIV/0 cells be included as zero values in the calculation of the average? If that in fact is what NTBC wants then its fine.

It seems to me that to exclude the #DIV/0 cells from the calculation NTBC should instead divide the sum of the cells by a COUNTIF of non #DIV/0 cells.

Or am I wrong? I don't yet have MS Office 2003 yet so I could well be wrong. In fact, the reason why I am following this forum is to see others' experience with it before deciding to go to MS Office 2003.

Gunny

 

[sfvb]

Gunny,

Excel will only average the cells that actually have data in them. Here's an example:

 

[tahilg]

I stand corrected. And, wiser.

Gunny

 

[KenWright]

You can also use an array formula to ignore the error values, eg:-

=AVERAGE(IF(ISERROR(A1:A100),"",A1:A100))

array entered using CTRL+SHIFT+ENTER

but I'm with Steve in that I also prefer to trap the problem at source and not have the errors in the first place.

Regards
Ken...............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------