Anyone have a formula that will do this please...



In Column A1:A500 I have numbers such as.

100

85

38

47

95

101

In B1:B500 I would like to have a formula that will return 20 for the
Highest number in A1:500 such as.

101=20

100=19

95=14

85=4

47=0

38=0



Thank you,



Eamon

Hi

Try this in B1
=IF(A1=MAX($A$1:$A$500),20,0)
and fill down to B500

I don't know where your other numbers come from (19, 14, 4 etc) by the way.

Andy.

"Eamon" wrote in message
...
Anyone have a formula that will do this please...



In Column A1:A500 I have numbers such as.

100

85

38

47

95

101

In B1:B500 I would like to have a formula that will return 20 for the
Highest number in A1:500 such as.

101=20

100=19

95=14

85=4

47=0

38=0



Thank you,



Eamon

Andy,

Thanks for your reply, sorry if I was not clear with my original question,
so I will try again...
In Column A lets say I have
A1 96
A2 100
A3 79
In Column B I would want to return 20 for A2 (100) as it is the highest
number.
I would want to return 16 for A1 (96) as it is 4 less than the maximum
number in Column A.
And any number in Column A that is 20 or more lower than the Maximum number
in Column A to return 0.

Hope this explains it a bit better.

Thank you,

Eamon
<Andy wrote in message ...
Hi

Try this in B1
=IF(A1=MAX($A$1:$A$500),20,0)
and fill down to B500

I don't know where your other numbers come from (19, 14, 4 etc) by the
way.

Andy.

"Eamon" wrote in message
...
Anyone have a formula that will do this please...



In Column A1:A500 I have numbers such as.

100

85

38

47

95

101

In B1:B500 I would like to have a formula that will return 20 for the
Highest number in A1:500 such as.

101=20

100=19

95=14

85=4

47=0

38=0



Thank you,



Eamon

Try this, then:
=IF(A1=MAX($A$1:$A$500),20,IF(MAX($A$1:$A$500)-A1=20,0,MAX($A$1:$A$500)-A1))

Andy.

"Eamon" wrote in message
...
Andy,

Thanks for your reply, sorry if I was not clear with my original question,
so I will try again...
In Column A lets say I have
A1 96
A2 100
A3 79
In Column B I would want to return 20 for A2 (100) as it is the highest
number.
I would want to return 16 for A1 (96) as it is 4 less than the maximum
number in Column A.
And any number in Column A that is 20 or more lower than the Maximum
number in Column A to return 0.

Hope this explains it a bit better.

Thank you,

Eamon
<Andy wrote in message ...
Hi

Try this in B1
=IF(A1=MAX($A$1:$A$500),20,0)
and fill down to B500

I don't know where your other numbers come from (19, 14, 4 etc) by the
way.

Andy.

"Eamon" wrote in message
...
Anyone have a formula that will do this please...



In Column A1:A500 I have numbers such as.

100

85

38

47

95

101

In B1:B500 I would like to have a formula that will return 20 for the
Highest number in A1:500 such as.

101=20

100=19

95=14

85=4

47=0

38=0



Thank you,



Eamon

In B1:
=MAX(20-MAX(A$1:A$500)+A1;0)

then copy down

HTH
--
AP

"Eamon" a écrit dans le message de
...
Anyone have a formula that will do this please...



In Column A1:A500 I have numbers such as.

100

85

38

47

95

101

In B1:B500 I would like to have a formula that will return 20 for the
Highest number in A1:500 such as.

101=20

100=19

95=14

85=4

47=0

38=0



Thank you,



Eamon

Ardus,

With your formula changed to =MAX(20-MAX(A$1:A$500)+A1,0)

This appears to work thank you, also thanks to Andy for his help.

Best regards,

Eamon
"Ardus Petus" wrote in message
...
In B1:
=MAX(20-MAX(A$1:A$500)+A1;0)

then copy down

HTH
--
AP

"Eamon" a écrit dans le message de
...
Anyone have a formula that will do this please...



In Column A1:A500 I have numbers such as.

100

85

38

47

95

101

In B1:B500 I would like to have a formula that will return 20 for the
Highest number in A1:500 such as.

101=20

100=19

95=14

85=4

47=0

38=0



Thank you,



Eamon

I always forget to delocalize my french formulas!!!

--
AP

"Eamon" a écrit dans le message de
...
Ardus,

With your formula changed to =MAX(20-MAX(A$1:A$500)+A1,0)

This appears to work thank you, also thanks to Andy for his help.

Best regards,

Eamon
"Ardus Petus" wrote in message
...
In B1:
=MAX(20-MAX(A$1:A$500)+A1;0)

then copy down

HTH
--
AP

"Eamon" a écrit dans le message de
...
Anyone have a formula that will do this please...



In Column A1:A500 I have numbers such as.

100

85

38

47

95

101

In B1:B500 I would like to have a formula that will return 20 for the
Highest number in A1:500 such as.

101=20

100=19

95=14

85=4

47=0

38=0



Thank you,



Eamon

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

Joseph,

Thanks for all the work you put in it is very much appreciated.
This works best from your suggestions, for what I wanted to do.
B2 =MAX(A$4:A$160)
B3 to B25 {=MAX(IF(A$2:A$160<B2,A$2:A$160)) }

Regards,

Eamon

"Joseph in Atlanta" wrote in
message ...
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