Adding together numbers and text

[imgonna]

Hi


My file is here: http://dl.dropbox.com/u/13025982/level.xlsx


I have a sheet with the following in E3 to J3


5a, 5b, 5a, 5c, 5b (equally it could be any string from 4c to 7a)


I am looking to streamline selecting the best numerical representation of these numbers/letters


The best I can come up with is to convert each string to a decimal


a's could be represented with 0.75

b's could be represented with 0.50

c's could be represented with 0.25


And literally average them. Then convert back to a 5a, 5b representation.


So in my case, 5.75+5.5+5.75+5.25+5.5 = 5.55, then use VLOOKUP again to convert these numbers to the string representation.


In my case:


J3=AVERAGE(VLOOKUP(E3,$A$3:$B$14,2),VLOOKUP(F3,$A$3:$B$14,2),VLOOKUP(G3,$A$3:$B$14,2),

VLOOKUP(H3,$A$3:$B$14,2),VLOOKUP(I3,$A$3:$B$14,2))


It works fine except for, when there is blank in the data , VLOOKUP() returns #N/A


So I wrap each VLOOKUP() in a ISNA like this: =IF(ISNA(VLOOKUP(…),"",VLOOKUP(…)) - which forces a "" to be returned when the data is blank. Fair enough, but if I have to wrap each VLOOKUP with the ISNA and IF, my formula soon starts to look like War and Peace. Plus if I want to add extra columns into the table, it becomes a pain to update.


Question(s):


1) Is there a simpler way to "average" mixed numbers, like 5a, 5b, 4a, 5c, 6a etc

2) Is this an ideal case for me to make a UDF - and if so, how the heck do I go about it??


Thanks in advance.


Glen

 

[Haseeb A]

Hello Glen,


If you already have a table 'Level to Decimal' then could use SUMIF.


J9, then copy down.


=SUMPRODUCT(SUMIF(A$3:A$14,E3:I3,B$3:B$14))/COUNTA(E3:I3)


Or,


If you are on Excel 2007 or later, try this Array Formula to convert 'a, b, c' to it's decimal values.


=AVERAGE(IFERROR(SUBSTITUTE(E3:I3,{"a";"b";"c"},{".75";".5";".25"})+0,""))


SUBSTITUTE is 'case sensitive' so better to add UPPER or LOWER formula in range & use appropriate case substitution texts. Also, since your data is on columns, separator must be ";" NOT comma.


Hope this helps,

Haseeb

 

[imgonna]

Haseeb


This: =AVERAGE(IFERROR(SUBSTITUTE(E3:I3,{"a";"b";"c"},{".75";".5";".25"})+0,"")) as an array, works a treat.


Perfect & thanks


Glen

 

[imgonna]

OK, so my next question is:


How to I put that array formula into a UDF so I can create a BestFit() formula that passes the selection to the UDF.....


Using macro recorder I end up with:


Selection.FormulaArray = _

"=AVERAGE(IFERROR(SUBSTITUTE(RC[-10]:RC[-6],{""a"";""b"";""c""},{"".75"";"".5"";"".25""})+0,""""))"


Which is OK -- but I can't figure how to pass the current selection to RC[-10]:RC[-6]


mmmmMM more investigating.


Glen

 

[imgonna]

Sorted (I think)


Public Function BestFit(rng As Range) As Single


Dim formlrBestFit As String


formlrBestFit = "= AVERAGE(IFERROR(SUBSTITUTE(" & rng.Address & ",{""a"";""b"";""c""},{"".75"";"".5"";"".25""})+0,""""))"


BestFit = Application.Evaluate([formlrBestFit])


End Function


Thanks for the input

Glen