How can i highlight the cell that has the lowest occuring number (blanks not included), out of a range of cells
so in the example provided,
the first row the second ~(rightmost cell will be highlighted)
the second row, the first cell will be highlighted
the third row the first cell will be highlighted
the fourth row nothing will be highlighted
if it helps the lowest number possible is 1 and the highest number possible is 90
Yes, your task is easy to solve. But you need to slightly expand the set of functions of Calc with the help of this extension - ListOK
After installing this extension in the Add-In category of the Function Wizard, several additional functions will appear, the names of which begin with LST
All of them are designed to handle lists of natural numbers, exactly as shown in your screenshot.
The LSTARR() function will collect all values from the specified range into a single list and for your cells it will turn out 4, 10, 31, 44, 45, 56, 64, 72, 74, 90. This is a string, it is simply impossible to apply the MIN() function to it, as pointed out by a respected colleague of @Zizi64.
Therefore, use another function provided by this extension - LSTVAL(). Its result is an array of numbers that go into the string. The MIN() function can be applied to this array and for your example we will get the result 4
The LSTIN() function will check each cell - is 4 in the list of values for this cell? Apply conditional formatting to the range of your cells and hilight the cells with the minimum - the problem is solved
Update. After discussing the solution with distinguished colleague @mikekaganski , I came to the conclusion that I misunderstood your desire as stated in the question. To find the cell with the minimum value in a row, the formatting condition must refer not to the entire range, but only to the cells of the current row. That is, the formula should be like this
LSTIN(MIN(LSTVAL(LSTARR($B3:$C3)));B3)
or even like this
LSTIN(MIN(LSTVAL(LSTARR(3:3)));B3)The condition will need to be slightly modified to apply to current row only, since OP wants each row to have own minimum highlighted.
@mikekaganski Perhaps you are right - I could not understand the description of “the third row the first cell will be highlighted the fourth row nothing will be highlighted”
@mikekaganski Perhaps you are right - I could not understand the description of “the third row the first cell will be highlighted the fourth row nothing will be highlighted”
Yes, after your comment, I think that the required condition "Cell value contains TRIM(MIN(LSTVAL(LSTARR($B$3:$C$6))))"
But looking for a minimum 4 in 44 and 74? what is the point of this?
But looking for a minimum
4in44and74? what is the point of this?
I don’t think I understand your question. IIUC, OP wants that the condition finds that in row 2, the minimal value is 44 (not 4), and the cell containing it is the left one (it just happens that other cells in the row are empty, and they are not highlighted - supposedly OP was worrying to not consider empty as 0).
By the way, I’m interested why do you use TRIM in the condition? I don’t know much about LST*, so I guess that the result might be a string?
Also, won’t finding “contains 44” highlight cells with 7442?
TRIM()? Just to convert integer value 4 (result of MIN()) to string
When there are more than one “number” in a single cell, then that content is NOT numeric content but that is a string.
If there is a rarely occurring task of the kind, and the joint list of numbers isn’t excessive, the more specialized (as compared with the mentioned extension) and slightly playful solution as demonstrated in the attachment may do.
minimumFromRangeOfLists.ods
@JohnSUN: I didn’t yet study the extension you pointed to. You will know if it also contains a multi-purpose flexible TEXTSPLIT() function, probably includig additional functionality like the elimination of duplicates (e.g.)…
An adequate counterpart to TEXTJOIN() is missing in Calc since V4.4
.
@Lupp No, my friend, these functions are not there - their names do not start with LST, so they could not be inserted, sorry
Well, the functionality might be implemented by a function LSTTEXTSPLIT() in the next version 
Anyway: To be able to evaluate created lists by versatile means, a powerful tool for splitting should be available. Is LSTVAL() the only function of the family also performing a split?
LSTTEXTSPLIT?! No, no, no! No more 6 chars in name! It’s enough that we already have GETPIVOTDATA() and FORECAST.ETS.STAT.MULT()! And the name LSTSPT() is difficult to remember.
Yes, besides LSTVAL, there is also LSTHAL. Well, you understand Vertical/Horizontal Array Line
No, no, no! No more 6 chars in name!
That’s an undisprovable(13).irrefutable(11).irrebuttable(12).incontrovertible(16).irrepudiable(12) proof of IMPSTY(6).
