macro to compute average

[Eric Kelcher]

I need a macro to compute the average for cells that have an unknown range. I am having an issue as the cells to compute the average of have a formula in them and if there is no data for that formula it is dividing by zero thus #DIV/0! So how do I make a macro figure the cells that have real data in them? or better how do I get the formula to appear if there is values in the relevant cells. (fairly uniform (b2-b3+1)/b3, (c2-c3+1)/c3...) then I want allt he cells that have formula in them to be averaged.

I thought I had it figured out to compute how many cells had info in them but the #DIV/0 threw me for a loop, and miscalculated.

 

[Dufus]

Eric Kelcher,

I couldn't quite decipher all the question but I'll offer this in hopes it helps. A common way to handle divide by zero errors is:

=IF(C3=0,0,(c2-c3+1)/c3)

Dufus
"Go Cowboys!"

 

[barry houdini]

Perhaps change the formula to

=if(b3,(b2-b3+1)/b3,"")

then if b3 is blank or zero the formula will generate a blank which won't be included in your average

 

[GorD]

Try

=AVERAGE(IF(ISNUMBER(L3:L12),L3:L12))

which is an array formula, so enter with ctrl shift enter rather than just enter.

Obviously change the range to suit your data

 

[eliW]

Hi

You may also use a formula that ignors the cells with any error:

=AVERAGE(IF(NOT(ISERROR(B1:E1)),B1:E1))

Asuming your cells are B1:E1

This is an array formula so enter with ctrl+ shift+ enter

Eli

 

[Eric Kelcher]

Hi

You may also use a formula that ignors the cells with any error:

=AVERAGE(IF(NOT(ISERROR(B1:E1)),B1:E1))

Asuming your cells are B1:E1

This is an array formula so enter with ctrl+ shift+ enter

Eli

That did it thanks