Display the top five numbers of column Y

Hi Leonard & Welcome to the Forum,

It sounds like this might work in A20 copied down...

=INDEX(ROW($Y$1:$Y$15),MATCH(LARGE($Y$1:$Y$15,ROW(A1)),$Y$1:$Y$15,0))

Or I guess you don't even need the Index part...

=MATCH(LARGE($Y$1:$Y$15,ROW(A1)),$Y$1:$Y$15,0)

Thank you for your reply,

That formula did not do the trick but wow ... A + for effort.

Still trying to work it out ... any other suggestions for this
mess an amateur made for you pros?

Thank you,
Leonard

try this array formula, but all the numbers should be unique
=LARGE(IF(LARGE($Y$1:$Y$15,ROW(A1))=$Y$1:$Y$15,ROW($Y$1:$Y$15),""),1)

Originally Posted by Leonard99321

That formula did not do the trick but wow ... A + for effort

Most often what we suggest is sight unseen and we have only your description to go by, but thanks for the endorsement.

So we might offer you the correct solution...

• Why did the formula not do the trick?
• What results do you require?
• Care to share some data?

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Originally Posted by AZ-XL

try this array formula, but all the numbers should be unique
=LARGE(IF(LARGE($Y$1:$Y$15,ROW(A1))=$Y$1:$Y$15,ROW($Y$1:$Y$15),""),1)

This returns the same exact results as that in post #2, but without array entered.

Originally Posted by Leonard99321

What I get is a pop up saying that the function is not a proper works function.

Okay, sorry about that. I see know your profile does say "Microsoft Works". I don't have that version so let me make a call to some that might.

well here is a list of works functions,no large or rank in there or countif or sumproduct, i think works isnt up to the job
The following list contains the functions and parameters available in Works Spreadsheet:

ABS(x)
ACOS(x)
AND(Logical0,Logical1,...)
ASIN(x)
ATAN(x)
ATAN2(x-coordinate,y-coordinate)
AVG(RangeReference0,RangeReference1,...)
CHOOSE(Choice,Option0,Option1,...)
COLS(RangeReference)
COS(x)
COUNT(RangeReference0,RangeReference1,...)
CTERM(Rate,FutureValue,PresentValue)
DATE(Year,Month,Day)
DAY(DateSerialNumber)
DDB(Cost,Salvage,Life,Period)
ERR()
EXACT(TextValue0,TextValue1)
EXP(x)
FALSE()
FIND(FindText,SearchText,Offset)
FV(Payment,Rate,Term)
HLOOKUP(LookupValue,RangeReference,RowNumber)
HOUR(TimeSerialNumber)
IF(Condition,ValueIfTrue,ValueIfFalse)
INDEX(RangeReference,Column,Row)
INT(x)
IRR(Guess,RangeReference)
ISERR(x)
ISNA(x)
LEFT(TextValue,Length)
LENGTH(TextValue)
LN(x)
LOG(x)
LOWER(TextValue)
MAX(RangeReference0,RangeReference1,...)
MID(TextValue,Offset,Length)
MIN(RangeReference0,RangeReference1,...)
MINUTE(TimeSerialNumber)
MOD(Numerator,Denominator)
MONTH(DateSerialNumber)
N(RangeReference)
NA()
NOT(Logical)
NOW()
NPV(Rate,RangeReference)
OR(Logical0,Logical1,...)
PI()
PMT(Principal,Rate,Term)
PROPER(TextValue)
PV(Payment,Rate,Term)
RAND()
RATE(FutureValue,PresentValue,Term)
REPEAT(TextValue,Count)
REPLACE(OldText,Offset,Length,NewText)
RIGHT(TextValue,Length)
ROUND(x,NumberOfPlaces)
ROWS(RangeReference)
S(RangeReference)
SECOND(TimeSerialNumber)
SIN(x)
SLN(Cost,Salvage,Life)
SQRT(x)
STD(RangeReference0,RangeReference1,...)
STRING(x,DecimalPlaces)
SUM(RangeReference0,RangeReference1,...)
SYD(Cost,Salvage,Life,Period)
TAN(x)
TERM(Payment,Rate,FutureValue)
TIME(Hour,Minute,Second)
TRIM(TextValue)
TRUE()
UPPER(TextValue)
VALUE(TextValue)
VAR(RangeReference0,RangeReference1,...)
VLOOKUP(LookupValue,RangeReference,ColumnNumber)
YEAR(DateSerialNumber)