Highest figures

**spoter** · 07-07-2009, 07:10 AM

hey guys, not too flash with excel and so on, so thought i might ask for a bit of help.

I have about 14 different sheets, and i want the top 20 figures from the areas i select in a list on a 15th sheet, is this possible? and if so how?

cheers

**sweep** · 07-07-2009, 07:15 AM

Hi,

Something along the lines of

=LARGE(Sheet1:Sheet14!A1:A10,1)
=LARGE(Sheet1:Sheet14!A1:A10,2)
=LARGE(Sheet1:Sheet14!A1:A10,3)

will return the 1st, 2nd and 3rd largest figure in the range A1:A10 on sheet1 to sheet 14

**spoter** · 07-07-2009, 07:31 AM

Thanks for the reply, it says i have too many arguments. So does that mean i can't do what i were intending on doing?

**sweep** · 07-07-2009, 07:39 AM

Works for me.

Can you post an example workbook?

**spoter** · 07-07-2009, 07:46 AM

Its on my other comp at work at the moment.

Its like D9:D19, D36:D46, D70:D80, D97:D107, D131:D141, D158:D168, D192:D202, D19:D29

Had it written down.

So its those cells on the 14 sheets

**sweep** · 07-07-2009, 07:50 AM

oh, so it's differenct ranges of cells on different sheets?

There's (to my knowledge) no easy way of dealing with that.

Is there any way to get them all on the same sheet, or at least in the same range on different sheets?

**spoter** · 07-07-2009, 07:55 AM

Yea might have to put them all onto one sheet then,

Alright cheers for your help

**DonkeyOte** · 07-07-2009, 08:05 AM

If you only want the top 20 in total then just pull the top 20 from each sheet, eg:

Please Login or Register  to view this content.

The LOOKUP is used to return 0 where there are less than sufficient numbers of values to populate the list of 20 -- you could use an IF with COUNT test but the formula would become even longer...

C1:C20 could be copied to D,E etc (1 column per sheet) -- running an Edit -> Replace on each column separately to update the Sheet name in the formula (ie from Sheet1 to Sheet2) -- avoid using INDIRECT here.

Once you have your Matrix of values you should be able to continue with your analysis.