Remember Me?

#1
October 26th 05, 10:45 PM
 Tonto Posts: n/a
SMALL and LARGE

Hello everyone.

I have a column of numbers in an array called TEST

1
1
3
4
5
6
7
7

Why when I run the formula =small(test,2) I get 1 and with
=large(test,2) I get 7.

My logic suggests I should get 3 and 6?

Thanks in anticipation.

John

#2
October 26th 05, 11:00 PM
 Posts: n/a
SMALL and LARGE

=SMALL (test,N) returns the Nth value when the elements
of test are arranged in ascending order, so

=SMALL(test,1) = 1
=SMALL(test,2) = 1
=SMALL(test,3) = 3
etc. Similarly for LARGE. You have to be a bit cleverer to
get the Nth smallest number ignoring duplicates.

Andrew Taylor

Tonto wrote:
Hello everyone.

I have a column of numbers in an array called TEST

1
1
3
4
5
6
7
7

Why when I run the formula =small(test,2) I get 1 and with
=large(test,2) I get 7.

My logic suggests I should get 3 and 6?

Thanks in anticipation.

John

#3
October 26th 05, 11:04 PM
 Tonto Posts: n/a
SMALL and LARGE

Thanks. I think I pointed that out but still need a solution.

John

#4
October 26th 05, 11:26 PM
 Bob Phillips Posts: n/a
SMALL and LARGE

=LARGE(IF(MATCH(S1:S10,S1:S10,0)=ROW(Data)-CELL("Row",S1:S10)+1,S1:S10),2)

=SMALL(IF(MATCH(S1:S10,S1:S10,0)=ROW(Data)-CELL("Row",S1:S10)+1,S1:S10),2)

array formulae, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Tonto" wrote in message
oups.com...

Thanks. I think I pointed that out but still need a solution.

John

#5
October 26th 05, 11:37 PM
 Tonto Posts: n/a
SMALL and LARGE

Bob

My test array is 12 values between F156 to F167

Thanks

John

#6
October 27th 05, 12:01 AM
 Bob Phillips Posts: n/a
SMALL and LARGE

Essentially, it builds an array of the data, replacing any duplications with
FALSE, and then does a LARGE or SMALL on that enhanced array, so that the
second largest is then truly the second largest if not the second ranked.

The MATCH(S1:S10,S1:S10,0) builds an array of indexes of the data within the
formula (as against a range S1:S10)

The ROW(S1:S10)-CELL("Row",S1:S10)+1 builds an array of possible indexes,
1-10 in this example. In my example, I could have used just ROW(S1:S10) as I
started in row 1, but the rest is proofing regardless of row start.

Comparing one against the other gives an array of TRUE/FALSE which drives
which items in S1:S10 are out-selected by the IF statement, i.e. giving an
array of unique numbers, which is then passed to the LARGE or SMALL
function.

Your formulae would then look like

=LARGE(IF(MATCH(F156:F167,F156:F167,0)=ROW(F156:F1 67)-CELL("Row",F156:F167)+
1,F156:F167),2)

and

=SMALL(IF(MATCH(F156:F167,F156:F167,0)=ROW(F156:F1 67)-CELL("Row",F156:F167)+
1,F156:F167),2)

Don't forget ... array formulae

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Tonto" wrote in message
oups.com...
Bob

My test array is 12 values between F156 to F167

Thanks

John

#7
October 27th 05, 07:02 AM
 Tonto Posts: n/a
SMALL and LARGE

Thanks Bob,

Clever, as usual.

Cheers

John

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Thorsten Pollmeier Excel Discussion (Misc queries) 3 January 15th 08 01:15 PM Deadly Excel Discussion (Misc queries) 2 August 15th 05 03:21 PM WPA Excel Discussion (Misc queries) 2 June 13th 05 07:41 PM Jayne Excel Discussion (Misc queries) 0 March 8th 05 03:43 PM BobT Excel Worksheet Functions 1 February 18th 05 11:03 PM

All times are GMT +1. The time now is 05:39 AM.

The comments are property of their posters.