Checking for black cells

[shaun]

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

 

[Sandy Mann]

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.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Bob Phillips]

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)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sandy Mann]

How about this array formula

Yes, how about that! <g>

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk