SMALL function

[Raymond1]

I'm looking to find the SMALLEST Value, 1st & 2nd runner ups.

SMALL function is used, but would like to target select cells & not a range in the function

How can this be done using SMALL (...., ROWS()) function?

Thanks,

 

[SpillerBD]

Re: SMALL function - Question

A range can be any series of cells being contiguous or non-contiguous. We do get accustomed to seeing contiguous ranges "A1:D6" that we forget we could have "A1,D1,A6,D6" for a range. Then name it to call it in formulas easier.

 

[Scott Huish]

Re: SMALL function - Question

The SMALL function can accommodate non-contiguous ranges if you enclose them in parentheses:

=SMALL((A1,C6,D2),1)

but what do you want to do with the ROWS function exactly?

 

[Raymond1]

Re: SMALL function - Question

Small or Large, same principle....but yes, I tried that & it cites error saying: "You have entered too many arguments for this function"

=LARGE($C$45,$E$45,$G$45,$I$45,$K$45,$M$45,$O$45,$Q$45,ROWS($C$50:C50))


what am I doing wrong?

 

[Raymond1]

Re: SMALL function - Question

YES, That did the job....I was missing the brackets!
The ROW function calls number 1, 2 & 3 for the SMALLEST or largest 3 values.

THanks a mil!

 

[Aladin Akyurek]

Re: SMALL function - Question

Small or Large, same principle....but yes, I tried that & it cites error saying: "You have entered too many arguments for this function"

=LARGE($C$45,$E$45,$G$45,$I$45,$K$45,$M$45,$O$45,$Q$45,ROWS($C$50:C50))


what am I doing wrong?

In C50 enter and copy down:

=IFERROR(LARGE(($C$45,$E$45,$G$45,$I$45,$K$45,$M$45,$O$45,$Q$45),ROWS($C$50:C50)),"")

 

[Raymond1]

Re: SMALL function - Question

Thanks, it is working . . . I now want to return the "Column header name," based in this LARGE value, how can I use the INDEX & MATCH function to do this in the next column next to the value?

 

[Aladin Akyurek]

Re: SMALL function - Question

Thanks, it is working . . . I now want to return the "Column header name," based in this LARGE value, how can I use the INDEX & MATCH function to do this in the next column next to the value?

You did not tell where column header names are located...

Row\Col	C​	D​	E​	F​	G​	H​	I​	J​	K​
44​	fred		alyosha		carol		dave		nate
45​	5​		3​		5​		2​		3​
46​
47​
48​
49​
50​	5​	fred
51​	5​	carol
52​	3​	alyosha
53​	3​	nate
54​	2​	dave
55​

In C50 control+shift+enter, not just enter, and copy down:

=IFERROR(LARGE(($C$45,$E$45,$G$45,$I$45,$K$45,$M$45,$O$45,$Q$45),ROWS($C$50:C50)),"")

In D50 control+shift+enter, not just enter, and copy down:

=IF($C50="","",INDEX($C$44:$K$44,SMALL(IF(MOD(COLUMN($C$44:$K$44)-COLUMN($C$44),2)=0,IF($C$45:$K$45=$C50,COLUMN($C$44:$K$44)-COLUMN($C$44)+1)),COUNTIFS($C$50:C50,C50))))