 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2007Checking for black cells
Hi, I have the following formula on my worksheet: =ROUNDUP((C3/D3) + (F3/G3) + (I3/J3) + (L3/M3) + (O3/P3) + (R3/S3) + (U3/V3) + (X3/Y3) + (AA3/AB3) + (AD3/AE3) + (AG3/AH3) + (AJ3/AK3) + (AM3/AN3) + (AP3/AQ3) + (AS3/AT3) + (AV3/AW3) + (AY3/AZ3) + (BB3/BC3), 0) The formula doesn't work if any of the cells are empty, how can I alter it so that it checks for empty cells and only calculates populated cells? Thanks for your advice Well, one way would be: =ROUNDUP(SUM((IF(D3=0,0,C3/D3)+IF(G3=0,0,F3/G3)+IF(J3=0,0,I3/J3)+IF(M3=0,0,L3/M3)+IF(P3=0,0,O3/P3)+IF(S3=0,0,R3/S3)+IF(V3=0,0,U3/V3)),(IF(Y3=0,0,X3/Y3)+IF(AB3=0,0,AA3/AB3)+IF(AE3=0,0,AD3/AE3)+IF(AH3=0,0,AG3/AH3)+IF(AK3=0,0,AJ3/AK3)+IF(AN3=0,0,AM3/AN3)+IF(AQ3=0,0,AP3/AQ3)),(IF(AT3=0,0,AS3/AT3)+IF(AW3=0,0,AV3/AW3)+IF(AZ3=0,0,AY3/AZ3)+IF(BC3=0,0,BB3/BC3))),0) but I can't help but think that there must be a more elegant way of doing it.  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > Hi, > [quoted text clipped - 10 lines] > > Thanks for your advice How about this array formula =ROUNDUP(SUM(IF((MOD(COLUMN(C3:BB3),3)=0)*(D3:BC3<>""),C3:BB3,0)/ IF((MOD(COLUMN(C3:BB3),3)=0)*(D3:BC3<>""),D3:BC3,1)),0) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Well, one way would be: > [quoted text clipped - 17 lines] >> >> Thanks for your advice > How about this array formula Yes, how about that! <g> SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > How about this array formula > [quoted text clipped - 22 lines] >>> >>> Thanks for your advice
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.