AVERAGEIF Function out there?

[davidad]

Does anybody have an "AVERAGEIF" function they could pass
on to me? I'm new to programming and have failed miserably
trying to write it. Any help would be great. Thanks.

 

[Fredrik Wahlgren]

Does anybody have an "AVERAGEIF" function they could pass
on to me? I'm new to programming and have failed miserably
trying to write it. Any help would be great. Thanks.

I don't quite understand what AVERAGEIF means. Please explain.

/Fredrik

 

[Chip]

I am also confused, because it seems you could simply do a combination
of IF statements and Average statements

 

[Bob Phillips]

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

something like

=AVERAGE(IF(B2:B10>2,B2:B10))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Like Sumif or Countif

 

[David]

Bob Phillips wrote

which is an array formula, so commit with Ctrl-Shift-Enter

Could you give a brief explanation of criteria for array formulas?
I've never been able to grasp what makes one an array type.

 

[Tom Ogilvy]

=AVERAGE(IF(B2:B10>2,B2:B10))

B2:B10>2

this says for each cell in B2:B10, is the cell > 2. So you are working
with a range or array of data. where you want each row handled as a
separate entity returning an array of values. So if the cells were filled
sequentially from B2 to B10 as 2, 3, 4, . . . , 8, 9, 10

then evaluation of IF(B2:B10>2,B2:B10) would return
{false;3;4;5;6;7;8;9;10} (an array of values)

put the whole formula in a cell and Enter it with Ctrl+Shift+enter

now select the cell and go to the formula bar. Select this much of the
formula IF(B2:B10>2,B2:B10) and hit F9. This should return the array
above. hit escape to remove the evaluation and leave the original formula.

 

[Bob Phillips]

David,

If I understand the perspective of your question, Excel functions normally
work on an array or range of values, or a single value. For instance, SUM
works on an array of values, IF works on a single value. But, often, some
other criteria is wanted to be applied to the range before SUMming it (or
MAXing, MINing, etc.). If that criteria involves a function that would
normally apply to a single value, there is a problem. This can be overcome
by using it as an array formula, in which the single value function gets
evaluated for each element of the array, and the result of each is then
returned to the other function that happily handles arrays. If you check
Tom's post, you can see how that evaluates with this particular formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[David]

Tom & Bob,

Thanks. Much better understood now.