Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The data
changes constantly.

TIA

--
Jim

Hi in E12 enter the following formula
=INDEX($A$1:$A$100,MATCH(LARGE($B$1:$B$100,ROW(1:1 )),$B$1:$B$100,0))
and copy this down

Note: Does not work if you have ties in your list


--
Regards
Frank Kabel
Frankfurt, Germany


JBoulton wrote:
Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The
data changes constantly.

TIA

Assuming codes in A2:A100, Value1 in B2:B100, the first formula in D2

=INDEX($A$2:$A$100,MATCH(LARGE($B$2:$B$100,ROW()-ROW($D$2)+1),$B$2:$B$100,0))

and copy down for 25 rows.

If you put the first formula in a cell other than D2, you must change the $D$2
to refer to that cell with the 1st formula.


On Fri, 29 Oct 2004 13:51:01 -0700, "JBoulton"
wrote:

Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The data
changes constantly.

TIA

Myrna and Frank,

FANTASTIC! Thanks for the lesson.

"Myrna Larson" wrote:


Assuming codes in A2:A100, Value1 in B2:B100, the first formula in D2

=INDEX($A$2:$A$100,MATCH(LARGE($B$2:$B$100,ROW()-ROW($D$2)+1),$B$2:$B$100,0))

and copy down for 25 rows.

If you put the first formula in a cell other than D2, you must change the $D$2
to refer to that cell with the 1st formula.


On Fri, 29 Oct 2004 13:51:01 -0700, "JBoulton"
wrote:

Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The data
changes constantly.

TIA

On Fri, 29 Oct 2004 13:51:01 -0700, "JBoulton"
wrote:

Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The data
changes constantly.

TIA

You may use the LARGE worksheet function.

Assuming your table is in A1:Cn and you want the results in E2:F25,

Name the first two columns Code and Value1.

In E2:E26 enter the numbers 1-25

In F2 place the formula:

=INDEX(Code,MATCH(LARGE(Value1,E2),Value1,0))

Copy/Drag this down as far as needed.


--ron

But instead of ROW()-ROW($D$2)+1, which is dependent on the address of the
cell with the formula, use Frank's formula where he just writes ROW(1:!)

I don't know why, but I always forget about that!



On Fri, 29 Oct 2004 14:30:03 -0700, "JBoulton"
wrote:

Myrna and Frank,

FANTASTIC! Thanks for the lesson.

"Myrna Larson" wrote:


Assuming codes in A2:A100, Value1 in B2:B100, the first formula in D2


=INDEX($A$2:$A$100,MATCH(LARGE($B$2:$B$100,ROW()-ROW($D$2)+1),$B$2:$B$100,0))

and copy down for 25 rows.

If you put the first formula in a cell other than D2, you must change the
$D$2
to refer to that cell with the 1st formula.


On Fri, 29 Oct 2004 13:51:01 -0700, "JBoulton"
wrote:

Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The data
changes constantly.

TIA

Right! That combined with Ron's suggestion to use dynamic names created an
elegant solution.

"Myrna Larson" wrote:

But instead of ROW()-ROW($D$2)+1, which is dependent on the address of the
cell with the formula, use Frank's formula where he just writes ROW(1:!)

I don't know why, but I always forget about that!



On Fri, 29 Oct 2004 14:30:03 -0700, "JBoulton"
wrote:

Myrna and Frank,

FANTASTIC! Thanks for the lesson.

"Myrna Larson" wrote:


Assuming codes in A2:A100, Value1 in B2:B100, the first formula in D2


=INDEX($A$2:$A$100,MATCH(LARGE($B$2:$B$100,ROW()-ROW($D$2)+1),$B$2:$B$100,0))

and copy down for 25 rows.

If you put the first formula in a cell other than D2, you must change the
$D$2
to refer to that cell with the 1st formula.


On Fri, 29 Oct 2004 13:51:01 -0700, "JBoulton"
wrote:

Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The data
changes constantly.

TIA

Ron,

Following your suggestion, I set up dynamic names. Now I have a great
solution.

Thanks for the idea.

"Ron Rosenfeld" wrote:

On Fri, 29 Oct 2004 13:51:01 -0700, "JBoulton"
wrote:

Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The data
changes constantly.

TIA

You may use the LARGE worksheet function.

Assuming your table is in A1:Cn and you want the results in E2:F25,

Name the first two columns Code and Value1.

In E2:E26 enter the numbers 1-25

In F2 place the formula:

=INDEX(Code,MATCH(LARGE(Value1,E2),Value1,0))

Copy/Drag this down as far as needed.


--ron

I can tell you the reason you are using it,
it's because it is independent of row insertions above the formula,
so it is more stable

Frank's formula will return wrong result if you insert a row above the
formula
--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Myrna Larson" wrote in message
...
But instead of ROW()-ROW($D$2)+1, which is dependent on the address of the
cell with the formula, use Frank's formula where he just writes ROW(1:!)

I don't know why, but I always forget about that!



On Fri, 29 Oct 2004 14:30:03 -0700, "JBoulton"
wrote:

Myrna and Frank,

FANTASTIC! Thanks for the lesson.

"Myrna Larson" wrote:


Assuming codes in A2:A100, Value1 in B2:B100, the first formula in D2


=INDEX($A$2:$A$100,MATCH(LARGE($B$2:$B$100,ROW()-ROW($D$2)+1),$B$2:$B$100,0))

and copy down for 25 rows.

If you put the first formula in a cell other than D2, you must change
the
$D$2
to refer to that cell with the 1st formula.


On Fri, 29 Oct 2004 13:51:01 -0700, "JBoulton"
wrote:

Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The
data
changes constantly.

TIA

Here's an approach that will take ties into consideration...

D2, copied down:

=RANK(B2,$B$2:$B$100)+COUNTIF($B$2:B2,B2)-1

E1:

=MAX(IF(B2:B100=INDEX(B2:B100,MATCH(F1,D2:D100,0)) ,D2:D100))-F1

...entered using CONTROL+SHIFT+ENTER.

F1: contains your Top N parameter, in this case 25

G2, copied down:

=IF(ROW()-ROW(G$2)+1<=$E$1+$F$1,INDEX(A$2:A$100,MATCH(ROW()-ROW(G$2)+1,$D$2:$D$100,0)),"")

If you want to display the corresponding information, copy this formula
across and down.

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273740

What follows constructs a Top N list.

Let A3:C11 house the following sample:

{"Code","Value1","Value2";1,100,1000;2,125,999;3,1 10,25;5,140,8;6,140,3;8,120,9;9,110,5;20,110,8}

In D3 enter: Rank

In D4 enter & copy down:

=RANK(B4,$B$4:$B$11)+COUNTIF($B$4:B4,B4)-1

Enter the Top N parameter value in F1: 5 (in this example).

In F2 enter:

=COUNTIF(B4:B11,LARGE(B4:B11,F1))-1

This calculates the ties of Nth highest value.

In F3 enter: Top N

In F4 enter & copy down:

=IF(G4<"",INDEX($A$4:$A$11,MATCH(ROW()-ROW(F$4)+1,$D$4:$D$11,0)),"")

The ROW(F$4) anchors the formula to the first cell it's entered: Here
F4.

In G3 enter: Value1

In G4 enter & copy down:

=IF(ROW()-ROW($G$4)+1<=$F$1+$F$2,INDEX($B$4:$B$11,MATCH(ROW( )-ROW(G$4)+1,$D$4:$D$11,0)),"")

The results area will look like this:

{5,140;6,140;2,125;8,120;3,110;9,9;20,20}


If you are on Excel 2003, do the following:

Change the formula in D4 from:

=RANK(B4,$B$4:$B$11)+COUNTIF($B$4:B4,B4)-1

to:

=RANK(B4,$B$4:$B$11)+COUNTIF($B$4:OFFSET(B4,0,0),B 4)-1

Select A3:D11.
Activate Data|List|Create List.
Check the "My list has headers" option.
Click OK.

Repeat the foregoing steps for F3:G10.

Whenever you add records to A:C, everything will be calculatad
automatically without adjusting any formulas or copying them down
manually. This List feature is just great: It solves the formula
copying problem of the formula systems.

A side note. It's surprising that the List functionality cannot cope
with the original formula in D4, a fact that forces us to introduce an
additional function call with the volatile OFFSET(). I'd urge Microsoft
to lift up this shortcoming of the otherwise very promising feature.


JBoulton Wrote:
Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The
data
changes constantly.

TIA

--
Jim


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273740

Myrna Larson Wrote:
But instead of ROW()-ROW($D$2)+1, which is dependent on the address of
the
cell with the formula, use Frank's formula where he just writes
ROW(1:!)

I don't know why, but I always forget about that!

[...]



ROW(1:1) and similar idioms should never be used for it is one of the
sources of spreadsheet errors by giving way to non-robust formulas.

If ROW()-ROW($D$2)+1 looks too baroque, you could use

ROWS($1:1)

instead (which I somewhat dislike because of how it looks when the
cursor is in the formula).


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273740

Domenic,

You're right. It handles ties. I don't understand the array formula in E1.
It seems to always evaluate to zero.

"Domenic" wrote:


Here's an approach that will take ties into consideration...

D2, copied down:

=RANK(B2,$B$2:$B$100)+COUNTIF($B$2:B2,B2)-1

E1:

=MAX(IF(B2:B100=INDEX(B2:B100,MATCH(F1,D2:D100,0)) ,D2:D100))-F1

...entered using CONTROL+SHIFT+ENTER.

F1: contains your Top N parameter, in this case 25

G2, copied down:

=IF(ROW()-ROW(G$2)+1<=$E$1+$F$1,INDEX(A$2:A$100,MATCH(ROW()-ROW(G$2)+1,$D$2:$D$100,0)),"")

If you want to display the corresponding information, copy this formula
across and down.

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273740

Will that work when the formula is copied down? In the 2nd row, it becomes an
array: $1:2


On Fri, 29 Oct 2004 18:03:21 -0500, Aladin Akyurek
wrote:


Myrna Larson Wrote:
But instead of ROW()-ROW($D$2)+1, which is dependent on the address of
the
cell with the formula, use Frank's formula where he just writes
ROW(1:!)

I don't know why, but I always forget about that!

[...]



ROW(1:1) and similar idioms should never be used for it is one of the
sources of spreadsheet errors by giving way to non-robust formulas.

If ROW()-ROW($D$2)+1 looks too baroque, you could use

ROWS($1:1)

instead (which I somewhat dislike because of how it looks when the
cursor is in the formula).

Myrna Larson Wrote:
Will that work when the formula is copied down? In the 2nd row, it
becomes an
array: $1:2

[...]


Yes, it will. It's result is not array.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273740

JBoulton Wrote:
Domenic,

You're right. It handles ties. I don't understand the array formula
in E1.
It seems to always evaluate to zero.
...

If correctly set up, it will calculate the ties of the Nth value. I
recently replaced it with an ordinary formula as shown in my reply.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273740

JBoulton Wrote:
Domenic,

You're right. It handles ties. I don't understand the array formula
in E1.
It seems to always evaluate to zero.

If there is more than one value ranked 25, then all of those values
will be displayed in addition to the top 24. The formula in E1 helps
effect such a situation.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273740

Aladin Akyurek Wrote:
If correctly set up, it will calculate the ties of the Nth value. I
recently replaced it with an ordinary formula as shown in my reply.

Hi Aladin!

Yes, I noticed your new formula. Interesting! :)


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273740

I'm betting you read it as: Row($1:1) and not RowS($1:1)?


Myrna Larson wrote:

Will that work when the formula is copied down? In the 2nd row, it becomes an
array: $1:2

On Fri, 29 Oct 2004 18:03:21 -0500, Aladin Akyurek
wrote:


Myrna Larson Wrote:
But instead of ROW()-ROW($D$2)+1, which is dependent on the address of
the
cell with the formula, use Frank's formula where he just writes
ROW(1:!)

I don't know why, but I always forget about that!

[...]



ROW(1:1) and similar idioms should never be used for it is one of the
sources of spreadsheet errors by giving way to non-robust formulas.

If ROW()-ROW($D$2)+1 looks too baroque, you could use

ROWS($1:1)

instead (which I somewhat dislike because of how it looks when the
cursor is in the formula).

--

Dave Peterson

Exactly. I missed that "S".

On Fri, 29 Oct 2004 20:04:10 -0500, Dave Peterson wrote:

I'm betting you read it as: Row($1:1) and not RowS($1:1)?


Myrna Larson wrote:

Will that work when the formula is copied down? In the 2nd row, it becomes
an
array: $1:2

On Fri, 29 Oct 2004 18:03:21 -0500, Aladin Akyurek
wrote:


Myrna Larson Wrote:
But instead of ROW()-ROW($D$2)+1, which is dependent on the address of
the
cell with the formula, use Frank's formula where he just writes
ROW(1:!)

I don't know why, but I always forget about that!

[...]



ROW(1:1) and similar idioms should never be used for it is one of the
sources of spreadsheet errors by giving way to non-robust formulas.

If ROW()-ROW($D$2)+1 looks too baroque, you could use

ROWS($1:1)

instead (which I somewhat dislike because of how it looks when the
cursor is in the formula).

I find that the results differ when using your old and new formulas for
the Top N list.

For example, if I enter 6 as the Top N value I get the following
results:

Old formula [MAX(IF(.....]

{5,140;6,140;2,125;8,120;3,110;9,110;20,110}

...which seems correct. The last value is included since it's tied
with the Top N value.

New formula [=COUNTIF(......]

{5,140;6,140;2,125;8,120;3,110;9,110;20,110;1,100}

...which doesn't seem correct. I don't understand why that last value
of 100 is included.

What am I missing?

Aladin Akyurek Wrote:
What follows constructs a Top N list.

Let A3:C11 house the following sample:

{"Code","Value1","Value2";1,100,1000;2,125,999;3,1 10,25;5,140,8;6,140,3;8,120,9;9,110,5;20,110,8}

In D3 enter: Rank

In D4 enter & copy down:

=RANK(B4,$B$4:$B$11)+COUNTIF($B$4:B4,B4)-1

Enter the Top N parameter value in F1: 5 (in this example).

In F2 enter:

=COUNTIF(B4:B11,LARGE(B4:B11,F1))-1

This calculates the ties of Nth highest value.

In F3 enter: Top N

In F4 enter & copy down:

=IF(G4<"",INDEX($A$4:$A$11,MATCH(ROW()-ROW(F$4)+1,$D$4:$D$11,0)),"")

The ROW(F$4) anchors the formula to the first cell it's entered: Here
F4.

In G3 enter: Value1

In G4 enter & copy down:

=IF(ROW()-ROW($G$4)+1<=$F$1+$F$2,INDEX($B$4:$B$11,MATCH(ROW( )-ROW(G$4)+1,$D$4:$D$11,0)),"")

The results area will look like this:

{5,140;6,140;2,125;8,120;3,110;9,9;20,20}


If you are on Excel 2003, do the following:

Change the formula in D4 from:

=RANK(B4,$B$4:$B$11)+COUNTIF($B$4:B4,B4)-1

to:

=RANK(B4,$B$4:$B$11)+COUNTIF($B$4:OFFSET(B4,0,0),B 4)-1

Select A3:D11.
Activate Data|List|Create List.
Check the "My list has headers" option.
Click OK.

Repeat the foregoing steps for F3:G10.

Whenever you add records to A:C, everything will be calculatad
automatically without adjusting any formulas or copying them down
manually. This List feature is just great: It solves the formula
copying problem of the formula systems.

A side note. It's surprising that the List functionality cannot cope
with the original formula in D4, a fact that forces us to introduce an
additional function call with the volatile OFFSET(). I'd urge Microsoft
to lift up this shortcoming of the otherwise very promising feature.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273740

You're right. I should have thought more about it before deciding to
switch. Thanks for looking at it.

The OP should use in F2 the original:

=MAX(IF(INDEX(B4:B11,MATCH(F1,D4:D11,0))=B4:B11,D4 :D11))-F1

which must be confirmed with control+shift+enter instead of just
enter.

instead of the flawed:

=COUNTIF(B4:B11,LARGE(B4:B11,F1))-1

Domenic Wrote:
I find that the results differ when using your old and new formulas for
the Top N list.

For example, if I enter 6 as the Top N value I get the following
results:

Old formula [MAX(IF(.....]

{5,140;6,140;2,125;8,120;3,110;9,110;20,110}

...which seems correct. The last value is included since it's tied
with the Top N value.

New formula [=COUNTIF(......]

{5,140;6,140;2,125;8,120;3,110;9,110;20,110;1,100}

...which doesn't seem correct. I don't understand why that last value
of 100 is included.

What am I missing?


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273740

Please replace the formula in F2, which is flawed, with:

=MAX(IF(INDEX(B4:B11,MATCH(F1,D4:D11,0))=B4:B11,D4 :D11))-F1

which you need to confirm with control+shift+enter instead of just
enter.

Thanks to Domenic for keeping me to my original formula system.

Aladin Akyurek Wrote:
What follows constructs a Top N list.

Let A3:C11 house the following sample:

{"Code","Value1","Value2";1,100,1000;2,125,999;3,1 10,25;5,140,8;6,140,3;8,120,9;9,110,5;20,110,8}

In D3 enter: Rank

In D4 enter & copy down:

=RANK(B4,$B$4:$B$11)+COUNTIF($B$4:B4,B4)-1

Enter the Top N parameter value in F1: 5 (in this example).

In F2 enter:

=COUNTIF(B4:B11,LARGE(B4:B11,F1))-1

This calculates the ties of Nth highest value.

In F3 enter: Top N

In F4 enter & copy down:

=IF(G4<"",INDEX($A$4:$A$11,MATCH(ROW()-ROW(F$4)+1,$D$4:$D$11,0)),"")

The ROW(F$4) anchors the formula to the first cell it's entered: Here
F4.

In G3 enter: Value1

In G4 enter & copy down:

=IF(ROW()-ROW($G$4)+1<=$F$1+$F$2,INDEX($B$4:$B$11,MATCH(ROW( )-ROW(G$4)+1,$D$4:$D$11,0)),"")

The results area will look like this:

{5,140;6,140;2,125;8,120;3,110;9,9;20,20}


If you are on Excel 2003, do the following:

Change the formula in D4 from:

=RANK(B4,$B$4:$B$11)+COUNTIF($B$4:B4,B4)-1

to:

=RANK(B4,$B$4:$B$11)+COUNTIF($B$4:OFFSET(B4,0,0),B 4)-1

Select A3:D11.
Activate Data|List|Create List.
Check the "My list has headers" option.
Click OK.

Repeat the foregoing steps for F3:G10.

Whenever you add records to A:C, everything will be calculatad
automatically without adjusting any formulas or copying them down
manually. This List feature is just great: It solves the formula
copying problem of the formula systems.

A side note. It's surprising that the List functionality cannot cope
with the original formula in D4, a fact that forces us to introduce an
additional function call with the volatile OFFSET(). I'd urge Microsoft
to lift up this shortcoming of the otherwise very promising feature.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273740

Aladin,

Thanks for a very robust solution!

"Aladin Akyurek" wrote:


Please replace the formula in F2, which is flawed, with:

=MAX(IF(INDEX(B4:B11,MATCH(F1,D4:D11,0))=B4:B11,D4 :D11))-F1

which you need to confirm with control+shift+enter instead of just
enter.

Thanks to Domenic for keeping me to my original formula system.

Aladin Akyurek Wrote:
What follows constructs a Top N list.

Let A3:C11 house the following sample:

{"Code","Value1","Value2";1,100,1000;2,125,999;3,1 10,25;5,140,8;6,140,3;8,120,9;9,110,5;20,110,8}

In D3 enter: Rank

In D4 enter & copy down:

=RANK(B4,$B$4:$B$11)+COUNTIF($B$4:B4,B4)-1

Enter the Top N parameter value in F1: 5 (in this example).

In F2 enter:

=COUNTIF(B4:B11,LARGE(B4:B11,F1))-1

This calculates the ties of Nth highest value.

In F3 enter: Top N

In F4 enter & copy down:

=IF(G4<"",INDEX($A$4:$A$11,MATCH(ROW()-ROW(F$4)+1,$D$4:$D$11,0)),"")

The ROW(F$4) anchors the formula to the first cell it's entered: Here
F4.

In G3 enter: Value1

In G4 enter & copy down:

=IF(ROW()-ROW($G$4)+1<=$F$1+$F$2,INDEX($B$4:$B$11,MATCH(ROW( )-ROW(G$4)+1,$D$4:$D$11,0)),"")

The results area will look like this:

{5,140;6,140;2,125;8,120;3,110;9,9;20,20}


If you are on Excel 2003, do the following:

Change the formula in D4 from:

=RANK(B4,$B$4:$B$11)+COUNTIF($B$4:B4,B4)-1

to:

=RANK(B4,$B$4:$B$11)+COUNTIF($B$4:OFFSET(B4,0,0),B 4)-1

Select A3:D11.
Activate Data|List|Create List.
Check the "My list has headers" option.
Click OK.

Repeat the foregoing steps for F3:G10.

Whenever you add records to A:C, everything will be calculatad
automatically without adjusting any formulas or copying them down
manually. This List feature is just great: It solves the formula
copying problem of the formula systems.

A side note. It's surprising that the List functionality cannot cope
with the original formula in D4, a fact that forces us to introduce an
additional function call with the volatile OFFSET(). I'd urge Microsoft
to lift up this shortcoming of the otherwise very promising feature.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273740