Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Excel =0/div
I have seven cells that I am trying to average the total in the eighth cell.
The worksheet is will not necessarely have an amount in each of the seven cells I am trying to average therefore I get a =0/div in the total cell unless there is at least one cell with content. How can I clean up the worksheet so the =0/div does not appear when there is no activity in any of the seven cells. This is the formula I have tried but it will not work. =IF(P3=0),"",(AVERAGE(D3,F3,H3,J3,K3,L3,N3) Thanks |
#2
|
|||
|
|||
=IF(ISERR(AVERAGE(D3,F3,H3,J3,K3,L3,N3),0,AVERAGE( D3,F3,H3,J3,K3,L3,N3))
-- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "cmeb4uby" wrote in message ... I have seven cells that I am trying to average the total in the eighth cell. The worksheet is will not necessarely have an amount in each of the seven cells I am trying to average therefore I get a =0/div in the total cell unless there is at least one cell with content. How can I clean up the worksheet so the =0/div does not appear when there is no activity in any of the seven cells. This is the formula I have tried but it will not work. =IF(P3=0),"",(AVERAGE(D3,F3,H3,J3,K3,L3,N3) Thanks |
#4
|
|||
|
|||
=IF(SUM(D3,F3,H3,J3,K3,L3,N3)=0,0,AVERAGE(D3,F3,H3 ,J3,K3,L3,N3))
"Nick Hodge" wrote in message ... Sorry typo =IF(ISERR(AVERAGE(D3,F3,H3,J3,K3,L3,N3)),0,AVERAGE (D3,F3,H3,J3,K3,L3,N3)) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Nick Hodge" wrote in message ... =IF(ISERR(AVERAGE(D3,F3,H3,J3,K3,L3,N3),0,AVERAGE( D3,F3,H3,J3,K3,L3,N3)) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "cmeb4uby" wrote in message ... I have seven cells that I am trying to average the total in the eighth cell. The worksheet is will not necessarely have an amount in each of the seven cells I am trying to average therefore I get a =0/div in the total cell unless there is at least one cell with content. How can I clean up the worksheet so the =0/div does not appear when there is no activity in any of the seven cells. This is the formula I have tried but it will not work. =IF(P3=0),"",(AVERAGE(D3,F3,H3,J3,K3,L3,N3) Thanks |
#5
|
|||
|
|||
Hi, Bob:
Your assumption is that if there is data in at least one cell, the sum will never be 0, i.e. you won't have just 2 entries, +10 and -10. I don't know whether that's a safe assumption or not. How about COUNT instead? =IF(COUNT(D3,F3,H3,J3,K3,L3,N3)=0,0,AVERAGE(D3,F3, H3,J3,K3,L3,N3)) Myrna Larson On Sat, 9 Apr 2005 22:08:22 -0400, "Bob Umlas" wrote: =IF(SUM(D3,F3,H3,J3,K3,L3,N3)=0,0,AVERAGE(D3,F3,H 3,J3,K3,L3,N3)) "Nick Hodge" wrote in message ... Sorry typo =IF(ISERR(AVERAGE(D3,F3,H3,J3,K3,L3,N3)),0,AVERAGE (D3,F3,H3,J3,K3,L3,N3)) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Nick Hodge" wrote in message ... =IF(ISERR(AVERAGE(D3,F3,H3,J3,K3,L3,N3),0,AVERAGE( D3,F3,H3,J3,K3,L3,N3)) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "cmeb4uby" wrote in message ... I have seven cells that I am trying to average the total in the eighth cell. The worksheet is will not necessarely have an amount in each of the seven cells I am trying to average therefore I get a =0/div in the total cell unless there is at least one cell with content. How can I clean up the worksheet so the =0/div does not appear when there is no activity in any of the seven cells. This is the formula I have tried but it will not work. =IF(P3=0),"",(AVERAGE(D3,F3,H3,J3,K3,L3,N3) Thanks |
#6
|
|||
|
|||
This is the formula I have tried but it will not work.
=IF(P3=0),"",(AVERAGE(D3,F3,H3,J3,K3,L3,N3) PS: I don't know what is in P3 (the sum?), but the problem with the above formula is the parentheses aren't correct. It should be =IF(P3=0,"",AVERAGE(D3,F3,H3,J3,K3,L3,N3)) |
#7
|
|||
|
|||
"Myrna Larson" wrote...
.... How about COUNT instead? =IF(COUNT(D3,F3,H3,J3,K3,L3,N3)=0,0,AVERAGE(D3,F3 ,H3,J3,K3,L3,N3)) .... Or use brute force. =SUM(D3,F3,H3,J3,K3,L3,N3)/MAX(1,COUNT(D3,F3,H3,J3,K3,L3,N3)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
html to excel | Excel Discussion (Misc queries) | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
Merge from Excel to Excel | Excel Discussion (Misc queries) | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) | |||
Shortcut file fails to open | Excel Discussion (Misc queries) |