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

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?

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

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

"drb" wrote:

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?

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

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)

--
Biff
Microsoft Excel MVP


"drb" wrote in message
...
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?

Second largest - to all
 

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?

"drb" wrote in message
...
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?

Second largest - to all
 

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

"drb" wrote in message ...
| 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?
|
| "drb" wrote in message
| ...
| 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?
|
|
|

Second largest - to all
 

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(A1:DA1),26)=1)*(MOD(COLUMN(B 1:DB1),26)=2),A1:DA1-B1:DB1),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

Whe

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



--
Biff
Microsoft Excel MVP


"drb" wrote in message
...
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?

"drb" wrote in message
...
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?

Second largest - to all THANK YOU
 

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

"Niek Otten" wrote in message
...
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

"drb" wrote in message
...
| 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?
|
| "drb" wrote in message
| ...
| 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?
|
|
|