-1

In Google Sheets, I have 4 Names associated with 4 data points that update throughout the month. I'd like to auto pick the name associated with the lowest data point. If there are 2, 3 or all 4 that have the same lowest point, I'd then like to select a random name but only from the lowest data points.

Ex Scenario 1 - The formula should select adam from the list.

Adam - 2
Bill - 3
Charlie - 4
David - 4

Ex Scenario 2 - The formula should select a random name from the adam, charlie & david only.

Adam - 2
Bill - 3
Charlie - 2
David - 2

Ex Scenario 3 - The formula should select a random name from the adam & bill only.

Adam - 1
Bill - 1
Charlie - 2
David - 2

Any help in developing a formula would be greatly appreciated!

1
  • Hi, welcome to WebApps. Would you please summarise your research (as mentioned in How to Ask) as well as any work that you did to try and solve this yourself. Would you also describe EXACTLY what is the question - you've described a broad scenario but not a specific problem that you are having. Would you also please provide a sample of your data in table format (is the data in ne cell per row, or two cells per row, as well as an example of how you expect a successful outcome to be displayed.
    – Tedinoz
    Jul 20 at 1:52

1 Answer 1

0

For the solution you could use a long formula in a single cell or 2 much more understandable formulas in 2 different cells. The solution I propose works for any number of names, not just 4. Assuming the names are in column A2:A and the scores are in column B2:B, this is the long formula:

=if(
COUNTA(Filter(A2:A,B2:B=min(B2:B)))=1,Filter(A2:A,B2:B=min(B2:B)),
INDEX(Filter(A2:A,B2:B=min(B2:B)),RANDBETWEEN(1, COUNTA(Filter(A2:A,B2:B=min(B2:B)))))
)

As you can see the formula could be simplified if we used another cell with the formula:

=Filter(A2:A,B2:B=min(B2:B))

If the latter were put in cell F2, the solution would be given by the formula:

=if(COUNTA(F2:F)=1,F2:F,INDEX(F2:F,RANDBETWEEN(1, COUNTA(F2:F))))
1
  • Thank you Daniele! This is wonderful and so very much appreciated!
    – Anthony
    Jul 20 at 7:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.