Functions to ignore hidden rows
 

I want the functions MATCH, INDEX, and AVERAGE to ignore hidden rows, for the
data range specified.

In addition, worksheet functions containing MATCH will be named, and names
inserted in INDEX. (I have found that, when specifying data ranges for
charts, using names often negates the hiding of rows).

Can it be done?

Functions to ignore hidden rows
 

Here are average and max in A2:A100 if you have Excel version 2003 and
possibly 2002 (I don't remember when they introduced subtotal for hidden
rows) Earlier versions would get name errors

=SUMPRODUCT(--($A$2:$A$100),SUBTOTAL(103,OFFSET($A$2,ROW($A$2:$A $100)-MIN(ROW($A$2:$A$100)),,)))/SUMPRODUCT(SUBTOTAL(103,OFFSET($A$2,ROW($A$2:$A$10 0)-MIN(ROW($A$2:$A$100)),,)))


=MAX(($A$2:$A$100)*(SUBTOTAL(103,OFFSET($A$2,ROW($ A$2:$A$100)-MIN(ROW($A$2:$A$100)),,))))


the latter needs to be entered with ctrl + shift & enter


If this is a workbook that is supposed to be shared with multiple people
with maybe earlier versions then it is not a good solution, then it is
better to make add-ins of UDF

IMHO it is not a good design to hide rows in ranges that need to be
calculated



--
Regards,

Peo Sjoblom



"hmm" wrote in message
...
I want the functions MATCH, INDEX, and AVERAGE to ignore hidden rows, for
the
data range specified.

In addition, worksheet functions containing MATCH will be named, and names
inserted in INDEX. (I have found that, when specifying data ranges for
charts, using names often negates the hiding of rows).

Can it be done?

Functions to ignore hidden rows
 

Note that the second formula does not work if all the values are negative


--
Regards,

Peo Sjoblom



"Peo Sjoblom" wrote in message
...
Here are average and max in A2:A100 if you have Excel version 2003 and
possibly 2002 (I don't remember when they introduced subtotal for hidden
rows) Earlier versions would get name errors

=SUMPRODUCT(--($A$2:$A$100),SUBTOTAL(103,OFFSET($A$2,ROW($A$2:$A $100)-MIN(ROW($A$2:$A$100)),,)))/SUMPRODUCT(SUBTOTAL(103,OFFSET($A$2,ROW($A$2:$A$10 0)-MIN(ROW($A$2:$A$100)),,)))


=MAX(($A$2:$A$100)*(SUBTOTAL(103,OFFSET($A$2,ROW($ A$2:$A$100)-MIN(ROW($A$2:$A$100)),,))))


the latter needs to be entered with ctrl + shift & enter


If this is a workbook that is supposed to be shared with multiple people
with maybe earlier versions then it is not a good solution, then it is
better to make add-ins of UDF

IMHO it is not a good design to hide rows in ranges that need to be
calculated



--
Regards,

Peo Sjoblom



"hmm" wrote in message
...
I want the functions MATCH, INDEX, and AVERAGE to ignore hidden rows, for
the
data range specified.

In addition, worksheet functions containing MATCH will be named, and
names
inserted in INDEX. (I have found that, when specifying data ranges for
charts, using names often negates the hiding of rows).

Can it be done?