average multiple columns but skip a few columns

  • Thread starter Thread starter cpliu
  • Start date Start date
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
 
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,
 
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<>""
 
=SUMPRODUCT(B1:B5,A1:A5)/SUMPRODUCT((B1:B5<>"")*A1:A5)

HTH,
Bernie
MS Excel MVP
 
Back
Top