Functions on filtered rows

[Sir Babydum GBE]

Hi

I have two filter-related questions.

1) Is there a function (or combination of) where I can return the most common value in a filtered column? (that is, I may filter by Column A, but want to see the most frequent value in Column B)

2) Can I have a rank() type function in each row that will adjust accordingly when a range is filtered? These formulae would be on the rows to being filtered.

Thanks in advance

BD

[rory]

Are you talking about number values, or could they be text too?

[Sir Babydum GBE]

Are you talking about number values, or could they be text too?

The common values would be text

the ranking would be looking at cells containg percentages

[rory]

You can do it with helper columns as in the attached?

[Sir Babydum GBE]

You can do it with helper columns as in the attached?

That's excellent Rory.

I searched on Google for an answer and I'm surprised there isn't more about the subject. I think Microsoft should look at putting a lot more functions into SUBTOTAL(). Who do I suggest this to? And will they pay me if they take it up???

[rory]

I'm pretty sure it's a common request. They won't pay you though!