Show figures and dates for "top ten"

[rousseau]

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

I have a table with monthly revenues. The headings across the top have 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 the LARGE function. What I'd like to do is show the month and year, as well. Could anyone point me to a solution?

Many thanks!
Rousseau

[CLR]

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


"rousseau" <rousseau.25igcm_1143769203.3315@excelforum-nospam.com> wrote in
message news:rousseau.25igcm_1143769203.3315@excelforum-nospam.com...
>
> I apologize if this has been asked before, but I couldn't find anything
> with a search.
>
> I have a table with monthly revenues. The headings across the top have
> 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 the
> LARGE function. What I'd like to do is show the month and year, as
> well. Could anyone point me to a solution?
>
> Many thanks!
> Rousseau
>
>
> --
> rousseau
> ------------------------------------------------------------------------
> rousseau's Profile:
http://www.excelforum.com/member.php...o&userid=33013
> View this thread: http://www.excelforum.com/showthread...hreadid=528374
>

[rousseau]

Thanks very much for the reply. I don't think I've explained what I need very well.

Further to my explanation as given above, I'd like to have an additional column set up outside the table showing the top ten from the table. I'd also like the month and the year to show up as well. For 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 heading "2004."

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

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

Thanks again,
Rousseau

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

[Infinity]

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.

[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.

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

[Biff]

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

"Infinity" <Infinity.25iwjz_1143790207.7896@excelforum-nospam.com> wrote in
message news:Infinity.25iwjz_1143790207.7896@excelforum-nospam.com...
>
> 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
> ------------------------------------------------------------------------
> Infinity's Profile:
> http://www.excelforum.com/member.php...o&userid=32725
> View this thread: http://www.excelforum.com/showthread...hreadid=528374
>

[Infinity]

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

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

"Infinity" <Infinity.25iwjz_1143790207.7896@excelforum-nospam.com> wrote in
message news:Infinity.25iwjz_1143790207.7896@excelforum-nospam.com...
>
> 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
> ------------------------------------------------------------------------
> Infinity's Profile:
> http://www.excelforum.com/member.php...o&userid=32725
> View this thread: http://www.excelforum.com/showthread...hreadid=528374
>