average multiple columns but skip a few columns

[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

 

[Bernie Deitrick]

=AVERAGE(A2:B2,D2:E2,G2,I2:Z2)

HTH,
Bernie
MS Excel MVP

 

[cpliu]

=AVERAGE(A2:B2,D2:E2,G2,I2:Z2)

Thanks for the help!

 

[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,

 

[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<>""

 

[Bernie Deitrick]

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

HTH,
Bernie
MS Excel MVP

 

[cpliu]

Thanks again for the help. The problem is solved with your help.