Extracting Data for a Formula from a List

[Zaraf]

I have a large list which I want to use two different criteria to find
one unique row. I was able to achieve this using the Autofilter
command (perfect with the drop down menu, just what I wanted), but the
problem that I'm having is that the data that I get, I want to use the
value for a formula.

Here is an example:

Size OD ___Weight ____m³/m
101.600 ___8.40813 __0.0070573
101.600 __13.78040 __0.0063858
101.600 __14.13756 __0.0063786
101.600 __15.56620 __0.0061223
114.300 __10.77431 __0.0088873
114.300 __11.83090 __0.0087520
114.300 __12.85774 __0.0086178
120.650 __10.58085 __0.0100792
120.650 __10.78919 __0.0100795
120.650 __14.13756 __0.0096500

(the __ is used to seperate the columns)

I want to be able to pick a Size OD, and a weight, and come up with a
unique value for m³/m. This I can do with autofilter, but the cell
that contains the m³/m data keeps changing, since autofilter only hides
cells and doesn't move the data. Is there a way that I'd be able to
automatically take the data from the filtered m³/m column to go into a
formula?

 

[Roger Govier]

Hi

Take a look at the Subtotal() function.
=SUBTOTAL(1,C2:C1000)
would calculate the Average of filtered data with column C, between C2
and C1000
Dependent upon which version of XL you are using, XL2003 has the
facility for doing the same thing with rows that are hidden as opposed
to filtered, but in that case you add 100 to the parameter.
The full list is as below.

1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP


I tend to insert a row above my header and put any subtotal function
there.

 

[Zaraf]

Thank you VERY much! It works! :