In my User Profile sheet I have 9 columns each of which holds a score e.g. 7,3,8,2,1,5,9,3,5. I would just like to have another column that specifies which of these columns holds the lowest value. Simple right? I wish …
A query + MIN formula would work well here I bet!! Hang on. I can probably find something on Google.
So you want to identify the name of the column that holds the lowest value, as opposed to just grabbing the lowest?
Ahh didn’t catch that last part. Hmm.
I think the issue here is that MIN does not play nicely inside an ARRAYFORMULA
Yeah it doesn’t. A QUERY formula would maybe work okay but it would be a complex one.
For context, these are scores in various assessment categories and I want to offer advice based on the lowest score(s)
Okay here’s an example thread I found— just ignore the average and max!
Ouch! Bit early in the morning for the query statement! But I will grab and coffee and try it - thanks.
LOL! Query is a bit of a beast for sure. If you can share an example sheet or just tell me which columns you’re needing to group by, I can try to help!
Well as an example, assume you have a single row with 9 columns. The column values are 3,6,2,7,8,9,4,3,2 for example. Can QUERY be used to calc the value of the lowest column value and put this in the 10th column?
At first glance it seems that QUERY wants to do its work across multiple rows rather than within a row, but I have never used it before so am probably wrong.
If this worked then I guess I’d use an array formula to replicate down as new rows are added?
Query is kind of like an arrayformula itself. It should compute new data from whatever columns you name. And yes, you’re right in saying that QUERY works across multiple rows, but it also works across multiple columns, if you write it like that. Hang on & I’ll share a sheet w/ an example.
Here’s an open edit sheet, so feel free to poke around— but this uses several diff formulas w/a query. It expands when new rows are added. Only downside, I didn’t identify which column contains lowest value. Try playing around with it (maybe using “Label” and “Group by” language) to solve that part!
Much appreciated. Will take a look now that coffee has hit.
I will jump in and help if the solution has not been found.
I knew you would show up sooner or later
I will politely decline any further response to that comment sir …
Why not do it inside Glide Date Editor itself? You could use a Math column with the formula Min(Col A, Col B, …, Col I)
Lets call this column “Min Val”
Now from that use another If Then Else Column with conditions if Min Value equals value in Col A, Return Col A
If Min Value equals value in Col B, Return Col B and so on.
That’s the simplest way I could think of.
Btw what @MegannLock did with the Query blew my mind