Hello, I’m relatively new to LibreOffice, and I would like to display a string in a cell, based on whichever number is smallest/biggest.
I want to display the string highlighted in blue under the “Highest Accur.” Cell, because the number in red is the smallest.
If anyone could explain to me how to do that it would be much appreciated. Thank you for your patience.
Many questions of the kind induce a question in return:
Are you aware of the fact that “the lowest value” can occur more than once in (say) a column.
If so: Why don’t you tell what to do then?
Hello,
sadly you did not provide a sample file so I need to make some assumptions:
A1 (incl. header line)D1 (incl. header line)To get the name of lowest value use:
=OFFSET($A$1;MATCH(MIN(D2:D16);D2:D16;0);0;1;1)
Hope that helps.
Thank you! That was indeed the solution.
I’m baffled.
What about my question in return?
Why should it be obsolete in the given case?
If I have to look-up 16 valus once, I do it by eye. The question only makes sense if there are many more data rows, and/or the values change frequently.
Somebody may consider to check my example.
ask293293elementsAssociatedWithMinimumValue.ods
@Lupp Sorry, my friend, but I don’t understand from what mistakes this part of the formula ($B$2:$B$1001<>"")* should protect? The option without it seems to give the same result.
{=TEXTJOIN(";";1;IF(($B$2:$B$1001=MIN($B$2:$B$1001));ROW($A$2:$A$1001) & ":" & $A$2:$A$1001;""))}
The example cannot return 0 for the value to match. Otherwise the simplified formula would accept all empty cells if the MIN() is 0. (I chose a range of 1000 rows of which many have no number in column B.)
@Lupp Thanks I got it. But doesn’t zero match the question - “based on whichever number is smallest”?
0 (zero) may be the number to look for, but a blank cell should (imo) not be accepted as “equal”.
Its one of the original sins of spreadsheets that they answer TRUE if asked for A1=B1 and the content of A1 is 0 or a formula returning 0, but B1 is blank.
explainExemplifyComment.ods