average multiple columns but skip a few columns

C

cpliu

How can I average muliple columns that are not continuous? For
example, I'd like to average A2 to Z2 but ignore the numbers in C2, F2
to H2?

Thanks for the help
 
C

cpliu

I'm doing weighted avreage but I need it to skip any cells without an
average.

Total number average
------------------------
10 4.3
5
90 4.1
15 4.4
30

I'd like it to ignore the row with total number of 5 and 30 as they
don't have data for average. If I use =SUMPRODUCT(B1:B5,A1:A5)/SUM
(A1:A5) it will include 5 and 30 into the division. How can I ask it
to not sum up if B has no data?

Thanks for the help,
 
S

Shane Devenshire

Hi,

Here is the idea behind a conditional weighted average:

=SUMPRODUCT(--(C2:C9=K2:K9),A2:A9*B2:B9)/SUMPRODUCT(B2:B9*(C2:C9=K2:K9))

Change your ranges. Here C2 is being compared to K2 but you can adjust this
to test for blank cells B1:B5<>""
 
B

Bernie Deitrick

=SUMPRODUCT(B1:B5,A1:A5)/SUMPRODUCT((B1:B5<>"")*A1:A5)

HTH,
Bernie
MS Excel MVP
 
Top