Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
maxkofe
 
Posts: n/a
Default 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
  #2   Report Post  
Ken Wright
 
Posts: n/a
Default

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


  #3   Report Post  
CLR
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Seed numbers for random number generation, uniform distribution darebo Excel Discussion (Misc queries) 3 April 21st 23 09:02 PM
Highlight lowest number Amber M Excel Discussion (Misc queries) 2 January 12th 05 01:19 AM
Subtracting based on number of miles Ms Chewie Excel Discussion (Misc queries) 3 December 21st 04 06:35 AM
How to format a number in Indian style in Excel? Victor_alb Excel Discussion (Misc queries) 2 December 21st 04 05:21 AM
Count number of shaded cells Maddoktor Excel Discussion (Misc queries) 2 December 20th 04 09:35 PM


All times are GMT +1. The time now is 12:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"