Results 1 to 7 of 7

20061013, 20:31 #1
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 170
 Thanks
 1
 Thanked 0 Times in 0 Posts
subtract from higest in the group (excel 2000)
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.

20061013, 20:35 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: subtract from higest in the group (excel 2000)
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>

20061013, 20:51 #3
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 170
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: subtract from higest in the group (excel 2000)
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)

20061013, 21:07 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: subtract from higest in the group (excel 2000)
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.

20061013, 21:17 #5
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 170
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: subtract from higest in the group (excel 2000)
Hans,
You are the MAN. This is perfect.
Thanks again

20061013, 21:48 #6
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 170
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: subtract from higest in the group (excel 2000)
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

20061013, 22:12 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: subtract from higest in the group (excel 2000)
Let's take the formula in B2:
<code>
=IF(AND(A2=MAX($A$1:$A$5),A2>MAX($A$1:A1)),A22,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 A22, otherwise it returns A2 itself. In this example, AND returned FALSE, so the formula as a whole returns the value of A2.
HTH