AVERAGE IF (maybe OR?) (probably a very simple solution)

[cockneyjosh]

All I want to do is to calculate an average from a table that takes account of two pieces of criteria, but instead of needing both pieces of criteria I just need one or the other. In this case it is the average value of contracts from two out of the 5 companies in the study. I have tried AVERAGEIF(D4:15,"LUMS"or"WBS", F4:F15) which I know is wrong but I can't figure out how else to do it. I am sure the solution is simple but I can't find it myself

 

[Gavin T]

You basically got it:

=AVERAGEIF(D4:15,OR("LUMS","WBS"), F4:F15)

 

[joeu2004]

All I want to do is to calculate an average from a table that takes account of two pieces of criteria, but instead of needing both pieces of criteria I just need one or the other. [....] I have tried AVERAGEIF(D4:15,"LUMS"or"WBS", F4:F15) which I know is wrong

Array-enter the following formula (press ctrl+shift+Enter instead of just Enter):

=AVERAGE(IF(D4:D15={"LUMS","WBS"},F4:F15))

 

[cockneyjosh]

Thanks joeu2004! that seems to work. Out of interest what is the reason for having to use the Array bit?

 

[joeu2004]

Array-enter the following formula (press ctrl+shift+Enter instead of just Enter):
=AVERAGE(IF(D4:D15={"LUMS","WBS"},F4:F15))

Out of interest what is the reason for having to use the Array bit?

That's an astute question.

"Obviously", IF(D4:D15={"LUMS","WBS"},F4:F15) returns an array of values, and AVERAGE accepts an array of values. In fact, if we execute the following in VBA:

Evaluate("AVERAGE(IF(D4:D15={""LUMS"",""WBS""},F4:F15))")

Excel knows to treat that as an array formula with no special indication.

However, when formulas of that form are entered into a cell, I believe there is a potential semantic ambiguity, not so much with that particular formula, AFAIK, but with others. We rely on that ambiguity when we use a named range in a table, for example.

That is my rationalization. I cannot say with impunity it is the correct explanation.