Problem with AVERAGE function

[H28Sailor]

Hi

I have column A - may contain "Surgery" or "*FSA"
Column B - contains Scores e.g. 24, 30 etc

I wish to find the mean of column B where Column A contains either "Surgery" or "*FSA"

My formula:-

=AVERAGE(IF(ISNUMBER(MATCH(A2:A10, {"*FSA", "Surgery"}, 0)), B2:B10))

I enter the formula with Ctrl+Shift+Enter

but I get zero as a result {I know there are scores that fit the criteria

what am I misunderstanding?

Thank you

[joeu2004]

My formula:-
=AVERAGE(IF(ISNUMBER(MATCH(A2:A10, {"*FSA", "Surgery"}, 0)), B2:B10))
I enter the formula with Ctrl+Shift+Enter
but I get zero as a result

It works for me. The quickest way to get a dispositive answer is to attach an example Excel file that demonstrates the problem.

If all the qualifying "numbers" in column B were text, or if none of the numbers qualified (despite your insistence to the contrary), the formula would result in a #DIV/0 error.

So my guess is: the result is not truly zero, but a value that appears to be zero due to the cell format.

Try formatting the cell as Scientific, just to be sure. Only 0.00E+00 is truly zero.

But of course, we cannot say why that would be the case without seeing the data.

[EDIT] PS.... Another guess is: the formula that you posted was not copy-and-pasted from the Formula Bar, and you unconsciously corrected the original error when you retyped the formula manually. Again, attaching an example Excel file will avoid these wild speculations.

[kvsrinivasamurthy]

Try this

Please Login or Register  to view this content.

[Tony Valko]

Is the asterisk in *FSA being used as a wildcard or is it the literal character?

Are the numbers in column B true Excel numbers or might they be TEXT numbers?

Any leading/trailing whitespace characters within the data of column A?