|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 29th, 2010, 04:01 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Need "Div/0" Average Relief ...
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 -- Kha 
|
|
#2
April 29th, 2010, 04:15 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need "Div/0" Average Relief ...
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),"") -- Best Regards, Luke M "Ken" wrote in message ... 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 -- Kha
|
|
#3
April 29th, 2010, 04:34 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need "Div/0" Average Relief ...
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 -- Kha "Ken" wrote: 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 -- Kha
|
|
#4
April 29th, 2010, 04:39 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need "Div/0" Average Relief ...
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" wrote: 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 -- Kha
|
|
#5
April 30th, 2010, 01:18 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need "Div/0" Average Relief ...
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 -- Kha "Luke M" wrote: 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),"") -- Best Regards, Luke M "Ken" wrote in message ... 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 -- Kha .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
