Need "Div/0" Average Relief ...

[Ken]

Excel2003 ... I do not usually work between WorkBooks ... I simply do not
like to ...

WB1 (Cell D3) ... AVERAGE Formula pulling from <= 10 different WB's (Cell
D3) ... (Works great as long as there is a "value" found)

Issue ... When other 10 WBs (Cell D3) is null ("") (hope I am using the
correct term?) ... My AVERAGE Formula WB1 (Cell D3) is returning the "Div\0"
error.

My fix ... =if(iserror(Average etc etc),"",(Average etc etc)) ... issue here
became length of my Formula because of Drives, Folder Trees & FileNames etc
.... Fix ok until Formula to looooooooooong.

Help from any intimate with Excel would be welcome ... Thanks ... Kha

 

[Luke M]

I would suggest breaking those links out into 10 seperate cells. Much easier
to diagnose problems. You could put them somewhere off to the side (say
Z1:Z10), maybe hide them so they don't distract user. Then your averaging
formula becomes:
=IF(SUM(Z1:Z10),AVERAGE(Z1:Z10),"")

 

[Ken]

Ok ... I weaseled ... "Div/0" error still happens, but now not visible & does
not print.

1: I removed error checking piece from my Formula
2: I set Conditional Format of my Range =iserror(Cell)
3: I set Font color to same color as cell Pattern
4: I set "Print Errors" as "Displayed" to "<Blank>"

Now if one of you could kindly tell me how to do this the correct way ...
That would be "awesome" ... I have learned many lessons from this board ...
Thanks ... Kha

 

[dlw]

the only way to do it is to make the check in the formula
you could dedicate cells in the main workbook that would just contain the
links to D3 in the other WB's. Use those cells instead of the long reference
in the formula.

 

[Ken]

Great suggestion ... Only I neglected to state that I have to do this for
every cell in the Range (D3:O14) ... I guess this just means more Helper
Cells ... Thanks ... Kha