Monthly Archives: February 2017
Using the Excel TEXTJOIN function to Extract an Internal Numeric String From a String by David Hager
Before I had access to Excel 2016 (365), I made a comment on Chris Webb’s web site about a possible use for the new TEXTJOIN function.
Here is the comment:
… the following array formula will return a numeric string from a string (i.e. – 125 from AMDHF125KOI) by using this formula. =TEXTJOIN(“”,TRUE,IFERROR(VALUE(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)),””))
Chris confirmed that it worked!
Afterwards, I forgot about it, until I read the article on XOR LX’s web site about extracting numbers from a string.
https://excelxor.com/category/extracting-numbers-from-a-string/
In looking at the comparisons, I saw that this TEXTJOIN formula construction simplified the extraction of an internal numeric string from a string as compared to previous solutions.
I am looking at ways to extend this methodology to other string-to array-to string solutions.
Averaging of Scientific Results in Excel
Each scientific measurement process has its own criteria for what is acceptable data. However, there are some general rules. If a data point is suspect, another is generated. If these are within the statistical variation for that process, then they are averaged. However, if a 3rd measurement is needed,
An Excel formula that correctly handles all of these requirements is needed. If 2 of the measurements are within statistical variation, they are averaged. This would be the two closest values.
But if two measurements are equidistant from the 3rd, (13.2,13.5,13.8) then all 3 values
must be averaged.
So, given those requirements, the following formula will return the correct result.
=IF(SUM(MATCH($A$1:$C$1,$A$1:$C$1,0))<6,QUARTILE($A$1:$C$1,1+2*(MEDIAN($A$1:$C$1)>SUM($A$1:$C$1)/3)),AVERAGE(IF(LARGE(ROUND(ABS($A$1:$C$1-TRANSPOSE($A$1:$C$1)),3),5)=ROUND(ABS($A$1:$C$1-TRANSPOSE($A$1:$C$1)),3),$A$1:$C$1,””)))
First, if the 3 values are all different, then
=SUM(MATCH($A$1:$C$1,$A$1:$C$1,0))=6 is True.
So, if SUM(MATCH($A$1:$C$1,$A$1:$C$1,0))<6,then the formula uses
=QUARTILE($A$1:$C$1,1+2*(MEDIAN($A$1:$C$1)>SUM($A$1:$C$1)/3))
In the comments at this site:
http://chandoo.org/wp/2011/01/19/average-of-closest-2-numbers/
a poster in the comments named Ihm came up with this formula.
In the FALSE condition of the formula is the following:
=AVERAGE(IF(LARGE(ROUND(ABS($A$1:$C$1-TRANSPOSE($A$1:$C$1)),3),5)=ROUND(ABS($A$1:$C$1-TRANSPOSE($A$1:$C$1)),3),$A$1:$C$1,””))
It is based on a 3×3 matrix obtained from
=ROUND(ABS($A$1:$C$1-TRANSPOSE($A$1:$C$1)),3)
For a data set of
13.4 | 13.55 | 13.8 |
The following matrix is
{0,0.25,0.4;0.25,0,0.15;0.4,0.15,0}
The ROUND function is necessary since the ABS function sometimes introduces differences (i.e. – 0.25 vs 0.2499999999).
The 5th largest value in the matrix corresponds to the smallest difference. The values for those differences in A1:C1 are averaged to afford the desired result.