Second largest in array of difference in high-low values in cells

[drb]

Using Excel 2002 to try to find second largest number from 10 numbers, which
are calculated from the high and low values of the day. Used Large Function
but comes up with error even when I cut down the quantity of numbers.

Example:
10-8=2.0
10.2-8=2.2
9.5-8.5=1.0
9.5-9.3=0.2
10.2-10.1=0.1
etc
Correct answer would be 2.0 for the second largest number, but how do I get
Excel to calculate this?

 

[Mike H]

Hi,

What error does Large give?

=Large(a1:a100,2) would return the second largest even if there were blanks
or text in the range

Mike

 

[T. Valko]

Array entered** :

=LARGE(A1:A5-B1:B5,2)

=2

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[drb]

Unfortunately the rows of numbers do not follow each other. There are other
values in between then so I can not us A1:Z1 array indications. This is
probably why it comes up with error since I want something like:
Large((A1-B1)-(AA1-AB1), (BA1-BB1),(CA1-CB1),(DA1-DB1),(EA1-DB1), 2)

Any possibilities of coming up with solution?

 

[Niek Otten]

Make a new (consecutive) range in which you pull the values from the original ranges and use that in your formula

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Unfortunately the rows of numbers do not follow each other. There are other
| values in between then so I can not us A1:Z1 array indications. This is
| probably why it comes up with error since I want something like:
| Large((A1-B1)-(AA1-AB1), (BA1-BB1),(CA1-CB1),(DA1-DB1),(EA1-DB1), 2)
|
| Any possibilities of coming up with solution?
|
| | > Using Excel 2002 to try to find second largest number from 10 numbers,
| > which are calculated from the high and low values of the day. Used Large
| > Function but comes up with error even when I cut down the quantity of
| > numbers.
| >
| > Example:
| > 10-8=2.0
| > 10.2-8=2.2
| > 9.5-8.5=1.0
| > 9.5-9.3=0.2
| > 10.2-10.1=0.1
| > etc
| > Correct answer would be 2.0 for the second largest number, but how do I
| > get Excel to calculate this?
| >
|
|

 

[T. Valko]

Large((A1-B1)-(AA1-AB1), (BA1-BB1),(CA1-CB1),(DA1-DB1),(EA1-DB1), 2)

Your references follow a pattern. Try this array formula** :

=LARGE(IF((MOD(COLUMN(A1A1),26)=1)*(MOD(COLUMN(B1B1),26)=2),A1A1-B1B1),2)

Note how the references are offset!

Based on you example:

Example:
10-8=2.0
10.2-8=2.2
9.5-8.5=1.0
9.5-9.3=0.2
10.2-10.1=0.1

Where:

A1...B1...AA1...AB1...BA1...BB1...CA1...CB1...DA1...DB1
10....8.....10.2......8......9.5......8.5.....9.5......9.3....10.2....10.1
.........2................2.2.................1..................0.2.................0.1

 

[drb]

Yes, that works and is simpler than other suggestions. Thank you.