Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 5
Please note that i can't change the original sheets, i don't want toc opy the
whole thing again, so filters etc won't work, i need a formulae. Thanks Hi all, I have Sheet1 which contains large amounts of data such as A B Fred £10 Joe £10 Tom £20 Sarah £30 etc Total £XYZ on sheet2 i would like to list the top 5 people (colA) based on their performance in (colB). I cannot change sheet1 in anyway, the only way i can find to do it, is to copy the entire thing to sheet to, do a rank() formula and then vlookup positions 2, 3, 4, 5, 6 (i don't use 1 as there is a total at the bottom). This gets a bit upset when there are blanks and when two or more people have the same value There must be a better way of doing this, so it can take into account when people have the same figure, ignoring the total at the bottom and without copying the entire thing! The number of rows of data is changeable and so i can't use i.e. A1:B50 when the totals are in B51, i need it either ignore the highest value or look at positions 2 to 6 whichever is easier for the formula Any help would be gratefully received |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 5
One further thought ..
In Sheet2, Put in A1: =IF(Sheet1!B1="","",IF(ISNUMBER(Sheet1!B1),Sheet1! B1-ROW()/10^10,"")) If you actually have a number of source cells in Sheet1's col A with "Total" (as indicated in your post) where these are of course to be excluded from the criteria, just replace the formula above (in Sheet2's A1) with: =IF(Sheet1!B1="","",IF(ISNUMBER(SEARCH("Total",She et1!A1)),"",IF(ISNUMBER(Sheet1!B1),Sheet1!B1-ROW()/10^10,""))) Then copy down to cover the max expected extent of data in Sheet1's cols A and B. No change is required to the other formulas. The above will then return the required results which omits all cells with "Total" in Sheet1. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 5
Assume name and sales (numbers) are
in Sheet1, cols A and B, from row1 down In Sheet2, Put in A1: =IF(Sheet1!B1="","",IF(ISNUMBER(Sheet1!B1),Sheet1! B1-ROW()/10^10,"")) Copy down to cover the max expected extent of data in Sheet1's col B Put in B1: =IF(ROW(A1)COUNT($A:$A),"",INDEX(Sheet1!A:A,MATCH (LARGE($A:$A,ROW(A1)),$A:$A,0))) Copy B1 to C1, then fill down by say, 10 rows? to C10, to cover the possibility of ties/multiple ties within the top 5. Hide away col A. Cols B and C will return the required descending sort of names and sales by sales. Read off the top 5 names & sales as desired. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Emma Hope" wrote: Please note that i can't change the original sheets, i don't want toc opy the whole thing again, so filters etc won't work, i need a formulae. Thanks Hi all, I have Sheet1 which contains large amounts of data such as A B Fred £10 Joe £10 Tom £20 Sarah £30 etc Total £XYZ on sheet2 i would like to list the top 5 people (colA) based on their performance in (colB). I cannot change sheet1 in anyway, the only way i can find to do it, is to copy the entire thing to sheet to, do a rank() formula and then vlookup positions 2, 3, 4, 5, 6 (i don't use 1 as there is a total at the bottom). This gets a bit upset when there are blanks and when two or more people have the same value There must be a better way of doing this, so it can take into account when people have the same figure, ignoring the total at the bottom and without copying the entire thing! The number of rows of data is changeable and so i can't use i.e. A1:B50 when the totals are in B51, i need it either ignore the highest value or look at positions 2 to 6 whichever is easier for the formula Any help would be gratefully received |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|