subtract from higest in the group (excel 2000)

[cvbs]

To all the brilliant minds on this board,

If i have a column of numbers in column A such as 13,13,11,5,2 and I want to subtract 2 from the highest number in said column and then show the results in column C. What kind of formula could I put into column B that would check to make sure i do not subtract from both of the 13 numbers. In other words I am trying to find out which number in column A is the highest and subtract 2 from that. That much is easy, the problem comes in when two numbers are the same and I only want it to effect the first number in the column.

I am open to any formula suggestions as opposed to a macro. Any thoughts would be appreciated.
Thanks in advance.

[HansV]

Use
<code>
=MAX(A1:A100)-2
</code>
Replace A1:A100 with the appropriate range. If you have no other values in column A, you can use
<code>
=MAX(A:A)-2</code>

[cvbs]

Hans,
Thanks for the response. I obviously did not state clearly enough what I need to accomplish.

If you look at the numbers in column A you can see that 13 is the highest number. You can also see that it is repeated 2 times in that column. What I need to do is say ok 13 is the highest number in the column A and I need to subtract 2 from that and show the results in the next column over. I then want to ignore the fact that the number 13 shows up later in the column and just transfer that number to the next column over. I have included a sheet to show what I mean. ( i hope)

[HansV]

Take a look at the attached workbook.
Please note that the formula in B1 is different from the one in B2:B5; the formula in B2 can be filled down, but not the one in B1.

[cvbs]

Hans,

You are the MAN. This is perfect.

Thanks again

[cvbs]

Hans,
For education purpose, could you explain what the formula actually does in row 2 and beyond. I am not quite sure on how the AND comes into play here for one thing.

thanks for the education

[HansV]

Let's take the formula in B2:
<code>
=IF(AND(A2=MAX($A$1:$A$5),A2>MAX($A$1:A1)),A2-2,A2)
</code>
The first condition is
<code>
A2=MAX($A$1:$A$5)
</code>
This checks whether A2 is equal to the highest value in A2:A5. In this example, it is, so this condition evaluates to TRUE.

The second condition is
<code>
A2>MAX($A$1:A1)
</code>
This checks whether A2 is higher than all entries in previous rows. In this example, it isn't (A1 is just as large), so this condition evaluates to FALSE.

The use of absolute and relative references is important here: in $A$1:A1, the first part will remain constant when the formula is filled down, but the second part will be adjusted.
In B3, it'll become $A$1:A2, in B4 it'll be $A$1:A3, etc.

The AND function combines the two conditions. Since they are not both TRUE, AND evaluates to FALSE.
In plain words: A2 is equal to the highest value, but it is not the first occurrence of the highest value.

If the result of AND is TRUE, the formula returns A2-2, otherwise it returns A2 itself. In this example, AND returned FALSE, so the formula as a whole returns the value of A2.

HTH