#value error in average formula with indirect function

[tdg]

Hi,

New to the forum. I'm trying to set up a formula that will average some numbers. The numbers I want it to average will be specified via the indirect function. The range of numbers to average may not be contiguous, so there may be numbers in a1:a5,a8:a10, for example. I want the formula to have the capability to average as many as 5 different subranges (where a subrange in the previous example is a1:a5). So I've set up this formula

=AVERAGE(INDIRECT(A342):INDIRECT(A343),IF(OR(A344="",A345=""),"",INDIRECT(A344):INDIRECT(A345)))

And this sort of works, but it only has 2 different subranges and it doesn't like it if a344 or a345 is blank. I know that the range to average will have at least two values - specified by INDIRECT(A342):INDIRECT(A343). I had hoped that I'd just be able to repeat the if statement 4 times to give the 5 subrange capability, but something isn't right.

I wondered if it needed a ctrl-shift-enter, but that didn't do it.

Thanks!
Tim

 

[tdg]

Any progress on a solution for the STDEV problem?

 

[schielrn]

I couldn't figure out a way to do it with native excel formulas, but this UDF will work and you could also build one for the average side, so you don't have to use all those indirects and iferror statements:

Excel Workbook
	A	B	C
16
17		c23
18	2.223610677	c25
19		c29
20		c31
21		c33
22		c36
23			3
24			4
25			5
26
27
28
29			2
30			3
31			4
32
33
34			2
35			6
36			9
37
38
39
40
41
42
Sheet1

Cell Formulas
Range	Formula
A18	=std(B17,B18,B19,B20,B21,B22)

Here is the udf that I have in a standard module:

Function std(r1 As Range, r2 As Range, Optional r3 As Range, Optional r4 As Range, _
    Optional r5 As Range, Optional r6 As Range, Optional r7 As Range, Optional r8 As Range, _
    Optional r9 As Range, Optional r10 As Range, Optional r11 As Range, Optional r12 As Range)
    If r1 Is Nothing Then Set r1 = r1
    If r2 Is Nothing Then Set r2 = r1
    If r3 Is Nothing Then Set r3 = r1
    If r4 Is Nothing Then Set r4 = r1
    If r5 Is Nothing Then Set r5 = r1
    If r6 Is Nothing Then Set r6 = r1
    If r7 Is Nothing Then Set r7 = r1
    If r8 Is Nothing Then Set r8 = r1
    If r9 Is Nothing Then Set r9 = r1
    If r10 Is Nothing Then Set r10 = r1
    If r11 Is Nothing Then Set r11 = r1
    If r12 Is Nothing Then Set r12 = r1
    avg = WorksheetFunction.Average(Union(Range(r1.Value, r2.Value), Range(r3.Value, r4.Value), Range(r5.Value, r6.Value), _
        Range(r7.Value, r8.Value), Range(r9.Value, r10.Value), Range(r11.Value, r12.Value)))
    cnt = WorksheetFunction.Count(Union(Range(r1.Value, r2.Value), Range(r3.Value, r4.Value), Range(r5.Value, r6.Value), _
        Range(r7.Value, r8.Value), Range(r9.Value, r10.Value), Range(r11.Value, r12.Value)))
    For Each a In Union(Range(r1.Value, r2.Value), Range(r3.Value, r4.Value), Range(r5.Value, r6.Value), _
        Range(r7.Value, r8.Value), Range(r9.Value, r10.Value), Range(r11.Value, r12.Value))
        If a.Value<> "" Then Total = Total + (a - avg) ^ 2
    Next a
    std = (Total / (cnt - 1)) ^ 0.5
End Function

Hope that helps.

 

[tdg]

Thanks a bunch. I'm new to UDFs, but I'll look them up and see how to implement. Do you have any quick advice?

 

[schielrn]

Basically udf's are user defined functions that will only work in the workbook that the function is in. Here is some information on them:

http://msdn.microsoft.com/en-us/library/ms499792.aspx

 

[tdg]

I couldn't figure out a way to do it with native excel formulas, but this UDF will work and you could also build one for the average side, so you don't have to use all those indirects and iferror statements:

Excel Workbook
	A	B	C
16
17		c23
18	2.223610677	c25
19		c29
20		c31
21		c33
22		c36
23			3
24			4
25			5
26
27
28
29			2
30			3
31			4
32
33
34			2
35			6
36			9
37
38
39
40
41
42
Sheet1

Cell Formulas
Range	Formula
A18	=std(B17,B18,B19,B20,B21,B22)

Here is the udf that I have in a standard module:

Function std(r1 As Range, r2 As Range, Optional r3 As Range, Optional r4 As Range, _
    Optional r5 As Range, Optional r6 As Range, Optional r7 As Range, Optional r8 As Range, _
    Optional r9 As Range, Optional r10 As Range, Optional r11 As Range, Optional r12 As Range)
    If r1 Is Nothing Then Set r1 = r1
    If r2 Is Nothing Then Set r2 = r1
    If r3 Is Nothing Then Set r3 = r1
    If r4 Is Nothing Then Set r4 = r1
    If r5 Is Nothing Then Set r5 = r1
    If r6 Is Nothing Then Set r6 = r1
    If r7 Is Nothing Then Set r7 = r1
    If r8 Is Nothing Then Set r8 = r1
    If r9 Is Nothing Then Set r9 = r1
    If r10 Is Nothing Then Set r10 = r1
    If r11 Is Nothing Then Set r11 = r1
    If r12 Is Nothing Then Set r12 = r1
    avg = WorksheetFunction.Average(Union(Range(r1.Value, r2.Value), Range(r3.Value, r4.Value), Range(r5.Value, r6.Value), _
        Range(r7.Value, r8.Value), Range(r9.Value, r10.Value), Range(r11.Value, r12.Value)))
    cnt = WorksheetFunction.Count(Union(Range(r1.Value, r2.Value), Range(r3.Value, r4.Value), Range(r5.Value, r6.Value), _
        Range(r7.Value, r8.Value), Range(r9.Value, r10.Value), Range(r11.Value, r12.Value)))
    For Each a In Union(Range(r1.Value, r2.Value), Range(r3.Value, r4.Value), Range(r5.Value, r6.Value), _
        Range(r7.Value, r8.Value), Range(r9.Value, r10.Value), Range(r11.Value, r12.Value))
        If a.Value<> "" Then Total = Total + (a - avg) ^ 2
    Next a
    std = (Total / (cnt - 1)) ^ 0.5
End Function

Hope that helps.

Sorry to bug you again, but there seems to be a problem with the UDF that I can figure out. I thought it was working ok, but now it isn't. Or maybe there's some sort of problem getting it in the VBA module properly? I'm not sure.

If I enter =std(B17,B18,B19,B20,B21,B22) into a cell, but I only have values in b17,b18,b19,b20 and not b21,b22 then I get a #VALUE error. This is a major problem because it means that I have to manually change the arguments to the std function so that it has the same number of terms as I have data for. I wanted the flexibility of the function just ignoring cells b21 and b22 if there were no data in them.

Can you help? Thanks in advance.

Tim

 

[schielrn]

I think this should handle what you were saying:

Function std(r1 As Range, r2 As Range, Optional r3 As Range, Optional r4 As Range, _
    Optional r5 As Range, Optional r6 As Range, Optional r7 As Range, Optional r8 As Range, _
    Optional r9 As Range, Optional r10 As Range, Optional r11 As Range, Optional r12 As Range)
    If r3 Is Nothing Then
        Set r3 = r1
    Else
        If r3.Value = "" Then Set r3 = r1
    End If
    
    If r4 Is Nothing Then
        Set r4 = r1
    Else
        If r4.Value = "" Then Set r4 = r1
    End If
    
    If r5 Is Nothing Then
        Set r5 = r1
    Else
        If r5.Value = "" Then Set r5 = r1
    End If
    
    If r6 Is Nothing Then
        Set r6 = r1
    Else
        If r6.Value = "" Then Set r6 = r1
    End If
    
    If r7 Is Nothing Then
        Set r7 = r1
    Else
        If r7.Value = "" Then Set r7 = r1
    End If
    
    If r8 Is Nothing Then
        Set r8 = r1
    Else
        If r8.Value = "" Then Set r8 = r1
    End If
    
    If r9 Is Nothing Then
        Set r9 = r1
    Else
        If r9.Value = "" Then Set r9 = r1
    End If
    
    If r10 Is Nothing Then
        Set r10 = r1
    Else
        If r10.Value = "" Then Set r10 = r1
    End If
    
    If r11 Is Nothing Then
        Set r11 = r1
    Else
        If r11.Value = "" Then Set r11 = r1
    End If
    
    If r12 Is Nothing Then
        Set r12 = r1
    Else
        If r12.Value = "" Then Set r12 = r1
    End If
    
    avg = WorksheetFunction.Average(Union(Range(r1.Value, r2.Value), Range(r3.Value, r4.Value), Range(r5.Value, r6.Value), _
        Range(r7.Value, r8.Value), Range(r9.Value, r10.Value), Range(r11.Value, r12.Value)))
    cnt = WorksheetFunction.Count(Union(Range(r1.Value, r2.Value), Range(r3.Value, r4.Value), Range(r5.Value, r6.Value), _
        Range(r7.Value, r8.Value), Range(r9.Value, r10.Value), Range(r11.Value, r12.Value)))
    For Each a In Union(Range(r1.Value, r2.Value), Range(r3.Value, r4.Value), Range(r5.Value, r6.Value), _
        Range(r7.Value, r8.Value), Range(r9.Value, r10.Value), Range(r11.Value, r12.Value))
        If a.Value <> "" Then Total = Total + (a - avg) ^ 2
    Next a
    std = (Total / (cnt - 1)) ^ 0.5
End Function