CODEWITHSUNDEEP
excelarray-formulasvba
Manish
Manish

Reputation: 213

Excel small formula not returing small value due to duplicate value

I got struck in Array formula. Assume, my Range has Value like 

A1:F1 = {1,0,2,1,0,3}.

I want to use one array formula dynamically to find value as 1st small as 0, 2nd Small as 1,3rd as 2 and 4th as 3.

Formula =Small(A1:F1,k) gives second small as 0. Whereas I wanted to have 1 as second by excluding duplicate small value 0.

Any formula to remove duplicate would be helpful. I tried this one, SMALL(IF(A1:F1>SMALL(A1:F1,k),A1:F1,""),k) , here k represent n'th small value, It gives me wrong output.

Upvotes: 2

Views: 490

Answers (1)

Limak
Limak

Reputation: 1521

I think I found solution, however I don't know how to clearly describe this composition of formulas, so I have uploaded it as an example. Please download and check if it is what you need. The column A is column with data, column B is list of min values, column C is number of this min values in column with data.

Upvotes: 1

Related Questions