DAverage Help

L

Lintz

I've got the below which finds how many "Up" and Low" there are in a se
range which works fine. What i am now trying to do is get the averag
of all values in Column K where the Up and Low criteria is met.

Can someone help me out.

Thanks.

=SUMPRODUCT((Details!G$2:G$18000="Up")*(Details!F$2:F$180003="Low")
 
J

Jerry W. Lewis

=AVERAGE(IF((Details!G$2:G$18000="Up")*(Details!F$2:F$180003="Low"),Details!K$2:K$180003))

array entered (Ctrl-Shift-Enter), or

=SUMPRODUCT((Details!G$2:G$18000="Up")*(Details!F$2:F$180003="Low"),Details!K$2:K$180003)/SUMPRODUCT((Details!G$2:G$18000="Up")*(Details!F$2:F$180003="Low")

which does not have to be array entered.

Jerry
 
J

Jerry W. Lewis

Whose code averages everything? Quoting relevant portions, or
specifically adressing your respondant helps to clarify what you are
talking about.

I have no clue what else you are seeing in ExcelForum, but I only see my
reply in the Excel newsgroups. I gave you two different formulas.
Neither works because your two array sizes (F & G) do not match, and the
reference to column F exceeds the maximum number of rows in a worksheet.
If you change :F$180003 to :F$18000 (and :K$180003 to :K$18000 that I
copied into my reply), then both formulas average only the matching values.

However, if you used the first formula and ignored the directions to
array enter it, then you would get the average of everything if
Details!G2"Up" and Details!F2"Low". To array enter a formula you use
Ctrl-Shift-Enter. If you do, the formula bar will show the formula you
entered surrounded by curly brackets
{=AVERAGE(...)}
even though you didn't type curly brackets.

The second formula does not have to be array entered.

Jerry
 
Top