Using average if with alphanumeric cells

Hi all,

I’ve been searching this forum as well as others for some time trying to figure out how to use the average if function with a set of data that contains alphanumeric cells. I’m having no luck and was wondering if someone would be so kind to help me. My worksheet has two columns; one with quantities the other with prices in alphanumeric form. E.g.

QTY PRICE
12 GBP 12.00
14 GBP 18.00
2 GBP 2.00
4 GBP 4.00
10 GBP 10.00

What I need is a formula which would average the numeric elements of the price data above a specified quantity. E.g. the average price for quantities above say 5 is (12+18+10)/3 = 10.

Below is what I’ve come up with so far and is an array formula (ctrl+shift+enter) which averages all the prices.

=AVERAGE(IF(ISERROR(FIND(" ",B2:B6)),"",RIGHT(B2:B5002,LEN(B2:B6)-FIND(" ",B2:B6))*1))

I hope this all makes sense. Please let me know if otherwise.

Any help will be greatly appreciated.

Many thanks

Try:

=AVERAGE(IF(A2:A6>5,MID(B2:B6,FIND(" ",B2:B6),10)+0))

confirmed with CTRL+SHIFT+ENTER not just ENTER

the average of those should be 13.3333333 not 10

Try:

=AVERAGE(IF(A2:A6>5,IF(B2:B6<>"",MID(B2:B6,FIND(" ",B2:B6),10)+0)))