The original question WAS pretty vague, as to what you wanted to calculate:
I've put together several formulas that MAY apply to your situation, or MAY
answer more detailed questions than you had originally...
I have re-created a spreadsheet, that has a column of numbers A2:A160
I have made the assumption that you prefer NOT to sort these numbers/rows
It seems that you are wanting to RANK the vlaues in these rows, finding the
top 20
I don't know if you had allowed for the possibility of DUPLICATES or not (I
did).
For instance, if 480 was the MAX value, (given a rank of 20) but there were
THREE
.... people with 480, then is the person with 479 ranked at 19, or 17 (20-3)
It was not clear what the return value was (ie: where do you get 4 from 85 ??)
It seems that you don't want to return negative values, but ZERO if not top
20.
You MAY want to find the top 20 UNIQUE values in a column...
You MAY want to find the top 20 values in a column, but count all
dup-occurances..
HERE is what I've come up with...
(DO-Send a response, so I'll know what you used, and if it was worth my time
to reply)
Headings in row A1:H1 (so you will know where I'm going with this)
A1='BASE TABLE # Your Data
B1='HI-NEXT # High value, and each next highest
C1='COUNT # Count of duplicates for each
D1='RANK # From 20, decrementing by ONE
E1='HIGHER # How many table cells are higher
F1='RANK-N # RANK counting duplicates
G1='RANK-N3 # Like F1, with different formula
H1='SUB-HI # How far below MAX is THIS value
I1='SUB=20 # 20 minus SUB-HI for each row
FORMULAS: (What you really wanted anyway)
B2 =MAX(A$4:A$160) # Find the first HI value
B3 to B25 {=MAX(IF(A$2:A$160<B2,A$2:A$160)) } # each HI-NEXT
... NOTE: This is an ARRAY formula, denoted by the { } braces.
... The curlies are not typed as part of the formula, but when you ENTER
... the formula into the cell, type Control-Shift-Enter to activate.
... when this formula is calculated, the MAX() function will only be able
... to "see" numbers in your table that are greater than the last max.
DUP: C2 to C25 =COUNTIF(A$2:A$160,B2) # Count Duplicates
... See How many values in your table match the HI for this row
RANK: You wanted the highest value in TABLE to return a value of 20, so the
...TOP RANK (in D2) is automatically 20 & others count down from D2
D2 = 20 # set highest rank by hand
D3 to D25 =MAX(D2-1,0) # decrement each row's RANK, MIN=0
HIGHER: For each row, this formula finds how many TABLE values were higher
E2 to E25 =COUNTIF(A$4:A$160,">"&B2)
... COUNTIF's 3rd param is a conditional, but must be a $String type,
... so I use '&' to concat "Greater" and convert B2 from NUM to STR
RANK-N: This column RANKs the high values in TABLE, but counts duplicates,
... so that if there are 3 identical values for 10th place, that the
... NEXT highest velue would be given 7th place ranking.
F2 = 20 # the highest rank you wanted was 20
F3 to F25 =MAX(F2-C2,20) # C2 has count of duplicates for row 2
RANK-N2:Column $G gets the same result as column $F, with diffeerent formula
that combines calculations from $F and $C above.
G2 to G25 =MAX(20-COUNTIF(A$2:A$160,">"&B4),0)
SUB-HI: These cells calculate the difference between the MAX value in TABLE
and the Nth highest value on this row.
H2 to H25 =B$2-B5 # B$2 is MAX(TABLE), Bx is current row
SUB-20: How far below the MAX(TABLE) value is the current row's HI-NEXT value?
This SEEMS to be one of the values you were looking for.
I2 to I25 =MAX(B2-B$2+20,0) # MIN 20, else zero.
I hope this helps...
Joseph
