PDA

View Full Version : Functions on filtered rows



Sir Babydum GBE
11-14-2007, 06:44 AM
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
11-14-2007, 07:29 AM
Are you talking about number values, or could they be text too?

Sir Babydum GBE
11-14-2007, 07:35 AM
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
11-14-2007, 08:31 AM
You can do it with helper columns as in the attached?

Sir Babydum GBE
11-14-2007, 08:57 AM
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
11-14-2007, 10:33 AM
I'm pretty sure it's a common request. They won't pay you though! :(