Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to do a vlookup (or similar) against a column which is a list of values. This works fine for looking up a value from a single row, but I want to be able to look up multiple rows, sum the results, and divide by the number of rows referenced.

For example:

   A     B            C       D        E             F               G
   [----given values----------------]  [Work/Auth]   [sum(vlookup(each(G),table,5))
                                                      /count(G)]     [given vals]

1  Item  Authorized   OnHand  Working  Operational%  DependencyOR%   Dependencies 
2  A     1            1       1        1              .55            B 
3  B     10           5       5         .50           .55            C,D
4  C     100          75      50        .50           .60            D
5  D     10           10      6         .60          1              

I want to be able to show an Operational Rate, and an operational rate of the systems each system depends on (F). In order to get a value for F, I want to sum over each value in column-E that was referenced by a dependency in column-G then divide by the number of dependencies in G. Column-G can have varying lengths, and will be a comma separated list of values from column-A.

Is there any way to do this in excel?

share|improve this question
up vote 1 down vote accepted

Are you looking for a formula for each row? So where you have C, D in G3 (?) I assume you want to lookup C and D in column A and average the corresponding values in column E? If so try this array formula for row 2 copied down

=AVERAGE(IF(COUNTIF(G2,"*"&A$2:A$10&"*")*(A$2:A$10<>""),E$2:E$10))

To enter the array formula you must use CTRL-SHIFT-ENTER. Excel will place {} braces around the function to indicate it is an array function.

share|improve this answer
    
Col-G will have varying numbers of comma-separated values. I will have to read up on how you are doing this with array formulas, and wildcards? But I'm working in a table, so my actual formula looks more like =AVERAGE(IF(COUNTIF(Table1[[#This Row],[Dependencies]],"*"&[Asset]&"*")*([Asset]<>""),[EquipReadiness])) It shouldn't make a difference between cell-references and table-references, but ... this formula is reporting all zeros. – glallen Jun 20 '12 at 20:22
    
You'd have to use the table name with all the columns so I think that should be this: =AVERAGE(IF(COUNTIF(Table1[[#This Row],[Dependencies]],"*"&Table1[Asset]&"*")*(Table1[Asset]<>""),Table1[EquipRead‌​iness])). That works for me. It's an array formula so you need to confirm with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar – barry houdini Jun 20 '12 at 21:15
    
CTRL+SHIFT+ENTER does work much better. So, this mostly works, although I can't seem to use an if or iferror to get rid of the #DIV/0. Also: this only seems to work if I put N, in the G column instead of N where N is the reference. Otherwise it does work. – glallen Jun 21 '12 at 16:38
    
Do you mean a literal "N" or is the reference a number? For avoiding errors you can just wrap the whole formula in IFERROR like =IFERROR(original_formula,0) the zero there is what you get in place of error, if you want blank use "" – barry houdini Jun 21 '12 at 17:27

as you have a variable amount of values, the easiest way is to use a UDF (User defined function)

This should get you your answer:

Function CSVAverage(CSVList As String, DataRange As Range, NumberColumn As Long) 
Dim FindList() As String
Dim NumEntries As Long
Dim I As Long
Dim Tot As Double

FindList = Split(CSVList, ",")
Tot = 0
NumEntries = UBound(FindList) - LBound(FindList) + 1

On Error Resume Next
For I = LBound(FindList) To UBound(FindList)
    Tot = Tot + Application.WorksheetFunction.VLookup(FindList(I), DataRange, NumberColumn, False)
    If Err.Number <> 0 Then
        CSVAverage = CVErr(xlErrNum)
        Exit Function
    End If
Next I
CSVAverage = Tot / NumEntries
End Function

If you have a value that is not found, then the function will return #NUM!
using your example spreadsheet, F1 would have =CSVAverage(G1,A2:E5,5)

share|improve this answer
    
I haven't used vbscript before (though I've played with C and perl some), but setting this up in my spreadsheet, adding a breakpoint to the function, and then stepping through it - everything looks good until Application.WorksheetFunction.VLookup(FindList(I), DataRange, NumberColumn, False) When I set a watch this never returns a value. – glallen Jun 20 '12 at 20:08
    
this is the VBA way of using the vlookup function. For each of the values you have separated by commas, it will look for the value in column 1 of your range, and return the matching value in column NumberColumn of that range. if you want to try the function manually, then you can use VLookup("A", Range("A1:B5"), 2, False) to find A in area A1:B5, and return the value in the 2nd column – SeanC Jun 20 '12 at 20:14
    
Also, if the value is not found in your table, it will return 0, and the err.number will be set to indicate this. – SeanC Jun 20 '12 at 20:33
    
Yes, I started off using VLookup directly in Excel and was trying to figure out the glue to make it look-up multiple times based on multiple references. Hence the question. It works when I call it directly in Excel, but not in VB. I set a watch on it, and also tried calling it manually, ie foo= Application.WorksheetFunction.VLookup("3",Range("Table1"),8,False) It may be that I'm trying to pass a table to it instead of a cell range? In my actual file, I've tried it both ways: =CSVAverage(Table1[[#This Row],[Dependencies]],Table1,9) and, =CSVAverage(L3,$A$3:$I$55,8) no luck either way. – glallen Jun 20 '12 at 20:38
    
It will return 0 if there is no value in G, but otherwise it yields either #NUM! – glallen Jun 20 '12 at 20:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .