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.

 

[eduzs]

Yes, thank you.
Do you have formula that only tell me if there's more than 1 with the same min value? return true when need to use random, false when there's only 1 position with the min value.

 

[pgc01]

Hi

If you prefer non-array, this one is compatible with older versions of excel:

=LARGE(INDEX((C4:F4=MIN(C4:F4))*{1,2,3,4},),RANDBETWEEN(1,COUNTIF(C4:F4,MIN(C4:F4))))

 

[Peter_SSs]

Yes, thank you.
Do you have formula that only tell me if there's more than 1 with the same min value? return true when need to use random, false when there's only 1 position with the min value.

I'm not certain what you are asking. What would be the expected results for the sample data in post #8 and why?

 

[eduzs]

Peter: the T2 formula in post # 8 did not work as expected, always returning 1. Pgc01 non-array formula always returning 4, I may be having a problem in translation of the formulas to pt-br version of excel (,;{} ...).

 

[eduzs]

I'll quit for now with these non-array formulas as I can't find where's the problem. Will use the pgc01 array formula that works ok (post #5).

 

[pgc01]

I'm glad it helped.

I'll quit for now with these non-array formulas as I can't find where's the problem.

IMO in would be worth spending some time making them work. It will help you in the future when you have other solutions to translate.

 

[eduzs]

Thanks Peter, Pgv01 and all.
After I racked my brains trying to find the problem in the translation , someone told me that just go to immediate window (CTRL+G in VBA editor) and enter this command:

activecell.formula="[COLOR=#333333]=LARGE(INDEX((C4:F4=MIN(C4:F4))*{1,2,3,4},),RANDBETWEEN(1,COUNTIF(C4:F4,MIN(C4:F4))))"[/COLOR]

and I got the formula in pt-br easily.

=MAIOR(ÍNDICE((C4:F4=MÍNIMO(C4:F4))*{1\2\3\4};);ALEATÓRIOENTRE(1;CONT.SE(C4:F4;MÍNIMO(C4:F4))))

The problem was that the translation of {1,2,3,4} is {1\2\3\4} and that's absolutely very difficult to imagine, why \ ? not just ; or , ....

All solutions worked as expected.

Thanks all
This topic is concluded.

 

[Peter_SSs]

... just go to immediate window (CTRL+G in VBA editor) and enter this command:

activecell.formula="[COLOR=#333333]=LARGE(INDEX((C4:F4=MIN(C4:F4))*{1,2,3,4},),RANDBETWEEN(1,COUNTIF(C4:F4,MIN(C4:F4))))"[/COLOR]

and I got the formula in pt-br easily.

That is a good trick to remember.
BTW, does pt-br mean Brazilian Portuguese?

The problem was that the translation of {1,2,3,4} is {1\2\3\4} and that's absolutely very difficult to imagine, why \ ? not just ; or , ....

I agree and I'm glad you tracked it down as I would never have come up with that.
Pleased you got it all sorted now.

 

[eduzs]

Yes, Brazilian Portuguese.
I suppose that this usefull translation method should works for any language.
Thanks

 

[eduzs]

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