Show figures and dates for "top ten"

  • Thread starter Thread starter rousseau
  • Start date Start date
R

rousseau

I apologize if this has been asked before, but I couldn't find anythin
with a search.

I have a table with monthly revenues. The headings across the top hav
the years, from 2000 to 2006. The headings at the side have the months
from January down to December.

I'm clear on how to show the top ten months for revenues using th
LARGE function. What I'd like to do is show the month and year, a
well. Could anyone point me to a solution?

Many thanks!
Roussea
 
Take a look at the feature, Data > Filter > AutoFilter......it will easily
filter out the Top Ten of any column......

Vaya con Dios,
Chuck, CABGx3
 
Thanks very much for the reply. I don't think I've explained what I nee
very well.

Further to my explanation as given above, I'd like to have a
additional column set up outside the table showing the top ten from th
table. I'd -also- like the month and the year to show up as well. Fo
example:

August, 2004 is a top month with $40,000, and is CellF9 of my table
Cell A9 is the item heading "August," and cell F1 is the item headin
"2004."

I know how to set up a ten-cell column -outside- my table showing th
top ten months. That's easy. What I'd like to have also are the date
for when these figures occurred, so that it says (somewhere) tha
$40,000 is for "August, 2004."

In this way, not only do I get a list of ten figures, but dates to g
with them. Surely someone before me must have wanted the same thing?

Thanks again,
Rousseau
 
I reconstructured your worksheet and add in some formula but it is a
little complicated and you have to understand how to use the following

- Vlookup
- Hlookup
- Array type of formulas

I haven't tested it yet, so you might want to test the worksheet out
before using my formulas. Please download attached.


+-------------------------------------------------------------------+
|Filename: SalesTop10.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4554 |
+-------------------------------------------------------------------+
 
Infinity said:
I reconstructured your worksheet and add in some formula but it is a
little complicated and you have to understand how to use the following

- Vlookup
- Hlookup
- Array type of formulas

I haven't tested it yet, so you might want to test the worksheet out
before using my formulas. Please download attached.
Click to expand...
Wow, this works! Thanks!

I'm not very good with formulas, but I'll tinker with it to get it how
I want it.

Thanks again!!

R
 
Hi!

Doesn't account for duplicates.

Duplicates that fall within the "top ten" cause some formulas to crash.

This isn't real easy to do and the Op didn't mention whether duplicates are
a possibility.

Biff
 
Actually that's really tough to do. what you may want to do is a counti
value and if there are 2 of the same value countif will display 2. No
just add one cent for that months sales and it should solve th
problem.
 
Back
Top