how to subtract the very next LOWEST number

[Guest]

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.

 

[Ken Wright]

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.

 

[CLR]

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 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