![]() |
#1
|
|||
|
|||
![]()
A large quantity of data has been captured with units of measurement included in same cell as values. e.g. 65 kg or 1.2 metres
I have tried =AVERAGE(VALUE(LEFT(A13:A66,))) But get a #VALUE error. Please can someone explain how to property use the VALUE and LEFT functions or provide the correct formula. |
#2
|
||||
|
||||
![]()
Something like =(SUMPRODUCT(--LEFT(A1:A2,FIND(" ",A1:A2))))/COUNTA(A1:A2)
( adapt range as needed)
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
![]()
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. |
![]() |
Thread Tools | |
Display Modes | |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
ibs | Word Tables | 1 | 11-22-2016 06:31 PM |
![]() |
Offrddrver | Excel | 4 | 03-23-2016 11:32 PM |
Data validation,force cell to be filed with number if respective cell is not empty | nicholes | Excel Programming | 0 | 08-01-2015 09:08 AM |
![]() |
Bill Martz | Excel | 1 | 04-23-2015 07:57 PM |
![]() |
jyfuller | Excel | 10 | 06-19-2013 05:31 PM |