Formula for SMALLEST VALUE not working

[hrayani]

Hello friends,


I am using this formula to

=SMALL(IF(orders_ref=$C$1,orders_po,""),1)

to get the smallest value in the range. Like 1st to 5th Smallest value.

Here is a sample data

[TABLE="width: 171"]
<tbody>[TR]
[TD]95822[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]95825[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]95828[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]95830[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]95831[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]40041541[/TD]
[TD]127[/TD]
[/TR]
[TR]
[TD]40043227[/TD]
[TD]127[/TD]
[/TR]
[TR]
[TD]40042045[/TD]
[TD]129[/TD]
[/TR]
[TR]
[TD]12943122 A[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]12943122 B[/TD]
[TD]130
[/TD]
[/TR]
</tbody>[/TABLE]

I am getting the result for Ref # 123 or 127... But the formula is not giving the result for
Ref # 130..... May be coz the Numbers in Column A against Ref # 130 contains some
alphabets with them...

Pls if anyone can let me know how to solve this issue???

Regards,

Humayun

 

[hrayani]

Thanks Sanjeev for the reply.

Your formula is working fine with the sample data.. But i dont know why i am not able to get it to work with my original data

Here is your formula

=IFERROR(INDEX($A$1:$A$10,SMALL(IF(C$1=$B$1:$B$10,ROW($A$1:$A$10),""),ROW())),"")

This is where i changed it.

=IFERROR(INDEX(orders_po,SMALL(IF(C$1=orders_ref,ROW(orders_po),""),ROW())),"")

Any Idea ???

Am i doing anything wrong.... let me know that the named ranges starts from Row 2 till Row 1000

 

[Sanjeev1976]

Try =IFERROR(INDEX(orders_po,SMALL(IF(C$1=orders_ref,ROW(orders_ref)-MIN(ROW(orders_ref))+1,""),ROW())),"") entered as an array formula

 

[hrayani]

No dear, still not working...

its not giving the first smallest value.... its starting from the 2nd Smallest value..

Any Idea ???

 

[XOR LX]

The unqualified ROW() is not a very rigorous choice for SMALL's k parameter. What's more, the part ROW($A$1:$A$10) will only work if your data begins in row 1.

Better is:

=IFERROR(INDEX(orders_po,SMALL(IF(C$1=orders_ref,ROW(orders_po)-MIN(ROW(orders_po))+1),ROWS(A$1:A1))),"")

though unless the number of expected returns is quite small then I would also replace the IFERROR here with a more efficient set-up.

https://superuser.com/questions/812...-and-return-all-multiple-corresponding-values

Regards

 

[Aladin Akyurek]

Will enter 130 in C1

Answer should be (12943122 A) in D1 & (12943122 B) in D2.... Rest D3:D5 should show blank as there are only two values against Ref # 130

Right.

Book1
	A	B	C	D	E
1	item	value	130	bottom
2	95822	123		5
3	95825	123		130
4	95828	123		3
5	95830	123		top items	top values
6	95831	123		12943122 A	130
7	40041541	127		12943122 B	130
8	40043227	127		Humayun	140
9	40042045	129
10	12943122 A	130
11	Humayun	140
12	12943122 B	130
13
Sheet1

In D3 control+shift+enter, not just enter:

=LARGE(IF(B2:B12>=C1,B2:B12),MIN(COUNTIFS(B2:B12,">="&C1),D2))

In D4 control+shift+enter, not just enter:

=SUM(IF(B2:B12>=LARGE(IF(B2:B12>=C1,B2:B12),MIN(COUNTIFS(B2:B12,">="&C1),D2)),1))

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

=IF($E6="","",INDEX($A$2:$A$12,SMALL(IF($B$2:$B$12=$E6,ROW($B$2:$B$12)-ROW($B$2)+1),COUNTIFS($E$6:E6,E6))))

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

=IF(ROWS($G$6:G6)<=$D$4,SMALL(IF($B$2:$B$12>=$D$3,$B$2:$B$12),ROWS($G$6:G6)),"")

 

[hrayani]

Thanks XOR LX

your formula is working just perfect

 

[XOR LX]

You're welcome, though take note of my point about IFERROR, which can be resource-heavy if the number of cells to which the formula is copied significantly exceeds the expected number of returns.

The link in my previous post demonstrates an alternative set-up with a single helper cell which avoids this potential issue.

Regards

 

[hrayani]

You're welcome, though take note of my point about IFERROR, which can be resource-heavy if the number of cells to which the formula is copied significantly exceeds the expected number of returns

Well, at this point of time number of cells to which the formula is copied are very few.
But, thanks for letting me know.

Regards,

Humayun

 

[hrayani]

Thanks Aladin for your Solution. I guess i will stick to the formula which XOR LX provided.
Its much simpler.....

And sanjeev - thanks to you too.

 

[Aladin Akyurek]

Thanks Aladin for your Solution. I guess i will stick to the formula which XOR LX provided.
Its much simpler.....

Really?