Average Array

[Native]

Hello,

Would like to calculate an average on an array based on a condition.

For example, if I do:

= SUM((Color="Red")*(Units))

I get the sum of all the units which are red....my arrays are Color
and Units

However, if I try:

= AVERAGE((Color="Red")*(Units))

I get a different answer than I would expect. It looks like this
formula sums up my units in red, but then counts the total number of
units to calculate the average.

Any suggestions?

 

[Bernie Deitrick]

Tex,

You're averaging a lot of Zero values.

Array enter - enter using Ctrl-Shift-Enter

=AVERAGE(IF(Color="Red",Units))

HTH,
Bernie
MS Excel MVP