how to subtract the very next LOWEST number
 

I have a column of real numbers entered in C8 to C25. In the next column I
want to display (in the corresponding cell), the result of that particular
cell minus the very next lowest number.

e.g. say I have C8 = 2000
C9 = 1000
C10 = 1900
C11 = 5000
now what I want in D8 is to take C8 and subtract the very next lowest number
(i.e. 1900) regardless of where it is in the column.
When I use the "LARGE" command with "2", it returns the result of the
highest number in the column (5000) minus the second largest number (2000)
and it totally ignores the process I need which is C8 - C10.

Your help would be most appreciated.
--
funafuti

In D8 use the following formula array entered

=C8-MAX(IF($C$8:$C$25<C8,$C$8:$C$25,MIN($C$8:$C$25)))

Array enter using CTRL+SHIFT+ENTER

Then just copy down

You didn't specify what happens if there is no lower number, so I've had it
return the same value (The smallest number in the list), and therefore one minus
the other give you 0. If that's not right then just post back with what you
want to happen.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"maxkofe" wrote in message
...
I have a column of real numbers entered in C8 to C25. In the next column I
want to display (in the corresponding cell), the result of that particular
cell minus the very next lowest number.

e.g. say I have C8 = 2000
C9 = 1000
C10 = 1900
C11 = 5000
now what I want in D8 is to take C8 and subtract the very next lowest number
(i.e. 1900) regardless of where it is in the column.
When I use the "LARGE" command with "2", it returns the result of the
highest number in the column (5000) minus the second largest number (2000)
and it totally ignores the process I need which is C8 - C10.

Your help would be most appreciated.
--
funafuti


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.804 / Virus Database: 546 - Release Date: 30/11/2004

It's kinda round-about, but you can put sequencial numbers 1,2,3, etc in
column B for your present order,...............then sort both columns on
column C DECENDING, then in D1 put =C1-C2 and copy down..............then do
Copy PasteSpecial on column D and then re-sort columns B:D using column B
ASCENDING.............

If you have to do this frequently, you can just record a macro while you do
it once then you will have it, just run the macro each time.........


Vaya con Dios,
Chuck, CABGx3


"maxkofe" wrote in message
...
I have a column of real numbers entered in C8 to C25. In the next column I
want to display (in the corresponding cell), the result of that particular
cell minus the very next lowest number.

e.g. say I have C8 = 2000
C9 = 1000
C10 = 1900
C11 = 5000
now what I want in D8 is to take C8 and subtract the very next lowest
number
(i.e. 1900) regardless of where it is in the column.
When I use the "LARGE" command with "2", it returns the result of the
highest number in the column (5000) minus the second largest number (2000)
and it totally ignores the process I need which is C8 - C10.

Your help would be most appreciated.
--
funafuti