Thread: average formal if cell contains number and text
View Single Post
 
Old 12-08-2016, 06:35 AM
xor xor is offline Windows 10 Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,100
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default
Can't get that SUMPRODUCT formula to work Pecoflyer.

If helper cells are acceptable I would put in B13 the formula:

LOOKUP(9^9,--LEFT(A13,ROW($1:$100))) and copy down to B66 and then use =AVERAGE(B13:B66).

If helper cells are not acceptable the following array formula seems to work:

=AVERAGE(IF(ISNUMBER(--MID($A$13:$A$66,1,FIND(" ",$A$13:$A$66)-1)),--MID($A$13:$A$66,1,FIND(" ",$A$13:$A$66)-1)))


Edit: My apologi to Pecoflyer. SUMPRODUCT formula does work if no empty cells, otherwise it doesn't.
Reply With Quote