Return poisition of the smallest with criteria.

[eduzs]

I have four values in P Q R and S columns (int numbers).
I need a formula that returns the position (match) of the smallest value, BUT when there's two or more values with this same smallest value??? In this case I want it to return a aleatory position between those smallest equal values.
Without macro will be better.
Examples:
10,20,15,9 returns 4 (position of the smallest).
40,2,30,8 returns 2 (position of the smallest).
3,3,7,5 returns 2 (aleatory between 1 and 2).
10,4,4,4 returns 3 (aleatory between 2,3,4).
Thanks.

 

[Peter_SSs]

Just one last question. Are there any advantages beyond the simplicity in using non-array formulas? As performance, smaller file size, etc.

In some cases array formulas may consume more resources and have slower calculation times than non-array formulas, but that is not a blanket rule.

In this case there really isn't that distinction anyway. The AGGREGATE formulas and PGC's non-CSE formula still use an array in their calculation so they are really still array formulas - hey just doesn't require the CSE entry.

I timed (twice) the calculation of each column of 5 formulas below with the results in seconds in rows 8 & 9. Those speed results would not cause me to choose one formula over the other.

Excel Workbook
	P	Q	R	S	T	U	V	W	X	Y	Z
1							PS4 cols only	PSExpandable	PGCExpandable	PGC4 cols only	PGCnon-array
2	10	20	15	9			4	4	4	4	4
3	40	2	30	8			2	2	2	2	2
4	3	3	7	5			2	2	2	1	1
5	10	4	4	4			3	2	3	4	2
6	3	3	7	3			2	1	4	2	4
7
8							0.00183	0.00183	0.00187	0.00187	0.00183
9							0.00182	0.00184	0.00185	0.00185	0.00184
Smallest

 

[eduzs]

Nice! Can you tell me the code to calculate the execution time?
I did some tests here it's a 459 lines and found that your AGREGATE or pgc01 X2 small were the fastest.

 

[Peter_SSs]

Can you tell me the code to calculate the execution time?

Refer here.