Excel 2003 formula help

[cojast08]

Any help if even possible would be great.

In one cell i have a formula that averages an entire column...this formula has been tested and works.

In another cell i have a formula that pulls numbers from another spreadsheet and adds them together for a total...This formula has been test and works.

What i am currently tring to do is drag my total cell all the way down my column that is being averaged.

It works until i get a cell that says div/0 then my avg does the same...

Is there anyway to write the formula so that it will ignore the div/0 for my avg until the cell gets populated with a number.

 

[SkipVought]

Hi,
[tt]
=if(MyRef=0,0,A_Ref/MyRef)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cojast08]

Can yo explain what each value stands for...

 

[SkipVought]

A cell reference. Excel 101.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Not sure what info you are pulling from the other workbook but Totalling or Averaging averages is not usually good maths.

Skip's solution would, if I have understood you and him correctly, need to be in the spreadsheet that is causing the problem, eliminating the div/0 error at source.

If that is not possible then Sumif(.....)/Countif(....) using the iserror(...) function will calculate your average

or pull the data from the other sheet without the errors:
if(iserror(x),"",x). Where x is your formula pulling from the other workbook.

Thinking about it, you have not been clear if you want to totally ignore those error values or treat them as zeros when calculating the average. If the latter then change "" to 0 in the formula I posted. [If using Skip's solution then the same consideration will apply].


Gavin

 

[cojast08]

Thanks for your answers.

I ended up just doing a sumif(a:a,">0")/countif(a:a,"<0")
and that was able to ignore the div/o errors until there was actually a number in that field.