#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

 

[rallcorn]

For your specific example try:

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

 

[VaraK]

Hi,

Perhaps, try this:

=SUM(INDIRECT(A342):INDIRECT(A343),IF(A344="",0,INDIRECT(A344)),IF(A345="",0,INDIRECT(A345)))/COUNTA($A$342:$A$345)

 

[tdg]

Thanks for looking at my question!

But I really want the formula to look for multiple subranges, not just a342:a343. I want it to check and see if there are ranges specified in a343:344 and/or in a345:a346 and/or a347:a348, etc. If there are any cell references specified in those cells, I want the formula to look up the values in those references and include them in the average. If there aren't cell references in some/any of them, I want the average to ignore those.

 

[tdg]

This almost works! The only problem is that this part of your eqn

IF(A344="",0,INDIRECT(A344)),IF(A345="",0,INDIRECT(A345))</pre>tells Excel to add individual numbers to the list of things to average. I want these two cells to give a range of cells. And you can't just swap the comma between the two IF statements with a colon.

 

[Aladin Akyurek]

Care to be specific - just state the ranges of interest and what claculation you want to do?

 

[schielrn]

Welcome to the Board!

This should work, but requires you to have Excel 2007 or newer, it can be modified for older versions, but will be much longer, cause you will need to use if(iserror()) statements to check instead of just iferror:

Excel Workbook
	A	B	C
16	4.222222
17		c23
18		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
Sheet15

Cell Formulas
Range	Formula
A16	=(IFERROR(SUM(INDIRECT(B17):INDIRECT(B18)),0)+IFERROR(SUM(INDIRECT(B19):INDIRECT(B20)),0)+IFERROR(SUM(INDIRECT(B21):INDIRECT(B22)),0)+IFERROR(SUM(INDIRECT(B23):INDIRECT(B24)),0))/COUNT(INDIRECT(B17):INDIRECT(B18),INDIRECT(B19):INDIRECT(B20),INDIRECT(B21):INDIRECT(B22),INDIRECT(B23):INDIRECT(B24))

Hope that helps.

 

[tdg]

Thanks! This seems to work perfectly. You're awesome, schielrn!

 

[tdg]

A follow up: how do I calculate the stdev for this range?

 

[schielrn]

I'm not sure what can easily be done, as here is the equation for calculating the standard deviation in simplest form:

http://standard-deviation.appspot.com/

But the sticking point would be to take the mean away from each individual number. I will try to look into it tomorrow, but do not have the time today.