7

I'm looking for a way to average the numerical numbers in one column, say B, only if the string in column A contains a certain text.

For example:

(In column A) (In column B)
Hello table . . . 1
Hello desk  . . . 2 
Bye table   . . . 3
Bye desk  . . . . 4

So if the criteria to average the numbers is "desk" the answer would be (2+4)/2=3.

What I came up with so far is:

=AVERAGEIF(A1:A5, SEARCH("desk",B1:B5),B1:B5) 

but it returns an error because the SEARCH only takes one input.

Any suggestions?

10

Add the asterisk * wildcard character before and/or after the word “desk”, as:

=AVERAGEIF(A1:A5,"*desk",B1:B5)

or

=AVERAGEIF(A1:A5,"*desk*",B1:B5)

This tells Excel to ignore any characters before/after the word “desk”.

3

Well as a somewhat generic answer that will work for you, another way to do a condition within a function (in your case an IF inside an AVERAGE) you can always break it apart. The IF statement can also return an array and does not need to be a single value.

If you type

=AVERAGE(IF(ISERROR(SEARCH("desk",A1:A5))=FALSE,A1:A5))

and press Ctrl + Shift + Enter after you type it, then it will treat the input as an array rather than a value, giving your results.

Hope this helps!

  • This doesn't appear to do what was asked at all. This will average A1:A5 (the range with text in it) if any of the items in A1:A5 contain the word 'desk'. Even if you correct the range returned by the IF portion to B1:B5 it's still not work the way it needs to because it'll return all items and then only when no rows contains a match. – krowe2 Jul 27 '15 at 13:53
1

AVERAGEIF is the best answer for Excel 2013+. For older versions, you'll need to use a third column, C.

All cells in column C (with a value to the left) have this formula: =IF(ISNUMBER(SEARCH(C$7,A1)), B1, "")

Cell, C7 just has the word desk typed out so that those formula don't need to be fixed every time you change the search.

After this, you simply do an average of that in C5, =AVERAGE(C1:C4). That's all you need. Not a lot of wasted space really and you can get rid of the search box too if you'd rather just adjust the functions when needed.

0

I'm not sure if using column C is a possibility for you, but here is one option that I have tested.

In Cell C1, input the following formula: =IF(ISNUMBER(SEARCH("desk",A1)),B1,"")

Copy/paste this formula down for each row in column C

Average the amounts that appear in column C.

If column C is already used, just insert another column in its place

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.