Need Some Help with an Average 1

[Wray69]

Hello All,

Im trying to do an average with two conditions, I can get it to work fine with one but as soon as I add the second one I get a #DIV/0 error. My data is set up as follows;

A1 B1
.9 .06
.89 .224
1.05 .173
.9 .067
1.15 .117

If I do an array formula of =AVERAGE(IF(A1:A5=0.9,B1:B5)) I get and answer of 0.0635 like I should, but when I do =AVERAGE(IF(A1:A5=0.9,IF(A1:A5=0.89,B1:B5))) I get a #DIV/0... Can anyone point out my error? I appreciate any help you can offer....

Regards -

Wray

 

[SkipVought]

wray,

I am not sure what you are trying to accomplish.

you second formula returns TRUE or FALSE to AVERAGE ??? Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Wray69]

I am needing to get the average of the results for .9 and .89 I apologize for not explaining that in my original post.

Wray

 

[SkipVought]

I don't know what you are trying to average for .9 and .89 Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Wray69]

The value in column B if there is a .89 or .9 in the same row in column A...

Hope this makes it more clear....

Regards,

Wray

 

[GlennUK]

Try this array formula ( entered using CTRL-SHIFT-ENTER instead of ENTER ) ...

=AVERAGE(IF((A1:A5=0.9)+(A1:A5=0.89)>0,B1:B5,"&quot)

Glenn.

 

[SkipVought]

What does AVERAGE have to do with it? Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

You could try this UDF - looked into a coupla array formulae but they don't seem to like OR statements

Function DblAvg(testRng As Range, CalcRng As Range)
divBy = 0
TotAmt = 0
For Each c In testRng
If c.Value = 0.89 Or c.Value = 0.9 Then
divBy = divBy + 1
TotAmt = TotAmt + CalcRng.Cells(c.Row, 1).Value
Else
End If
Next
ArseyAvg = TotAmt / divBy
End Function
Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Wray69]

Thanks Glenn, that worked like a charm....

Regards -

Wray

 

[Zathras]

If you want to stick with an array formula, you can use this:

=(SUM(IF($A$1:$A$5=0.9,$B$1:$B$5))+SUM(IF($A$1:$A$5=0.89,$B$1:$B$5)))/(COUNT(IF($A$1:$A$5=0.9,$B$1:$B$5))+COUNT(IF($A$1:$A$5=0.89,$B$1:$B$5)))

I agree that it SHOULD work by using OR, but it doesn't (at least in Excel 97)

 

[SkipVought]

If you want to get .224 when you specify .89, then use DGET. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TonyJollans]

I have 97 and I have no trouble with the array formula

=AVERAGE(IF(OR(A1:A5=0.9,A1:A5=0.89),B1:B5))

which I think does what you want.

However, as you only specify what to do if the condition is TRUE, when the condition is FALSE the AVERAGE function tries to divide by zero (the number of values it is trying to average) and so, as in your original question when your formula was asking for values in column A which were equal BOTH to 0.9 AND to 0.89 (which must be false), you get #DIV/0.

Enjoy,
Tony

 

[xlbo]

Tony - unfortunately you are incorrect. Using this formula with these values:
0.9 2
0.89 4
0.5 3
0.9 6
0.6 6

gives an answer of 4.2 which is the overall average. This is the error I came up against when trying to use the OR statement.
Looks like Glen's answer is the shortest possible Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Wray69]

Yeah I was originally trying it with the OR statement also and it was giving me the average of the entire range. I would like to thak all of you though for the help and a thank you to Glen again for the answer...

Regards -

Wray

 

[TonyJollans]

Oops! You are correct Geoff. I should be a bit more careful.

However my point about the #DIV/0 stands - and applies to Glenn's formula as well. The AVERAGE function needs some input when the condition returns FALSE if that is ever going to happen.

Best,
Tony