Finding a min() value on each row using an arrayformula() within Google Sheets

Question

I'm kind of in a pickle with a sheet I've been working on, I was looking for some clarification. For some reason my old account is gone that Ive for years :(, I apologize.

I'm trying get a min value for each row I've added data in. There is 2 columns where I need to convert the data first, and then find the lowest value for for all 3 columns for each row.

I've tried multiple things and only entering the formula I've created for each row works perfectly. For Instance:

=MIN( IF(E124 > 0, E124*$E$6), IF(F124 > 0, F124*$F$6), IF(G124 > 0, G124) )

I've tried to use other examples, however, I am not familiar with QUERY. Trying to do simple calculations (adding) within the formula is confusing. Example I've tried using:

=QUERY(TRANSPOSE(QUERY(TRANSPOSE(A1:C), "select "&REGEXREPLACE(JOIN( , ARRAYFORMULA(IF(LEN(A1:A&B1:B&C1:C), "min(Col"&ROW(A1:A)-ROW(A1)+1&"),", ""))), ".\z", "")&"")), "select Col2")

That has multiple problems when adding it. I want to ignore empty cells and text like headers. It will not write over text, and does not execute (gives me an error about overwriting values).

I've tried writing an =arrayformula but does not like calculating the min value. It does do the calculations for the rows.

=ArrayFormula(IF(ISBLANK({E8:E;F8:8;G8:8}), "", added my formula here))

Down below was something I've worked on for hours, I believe the problem is selecting the ranges inside the MIN function that is causing the problems

=arrayformula(IF(LEN(E8:G)<>0, MIN( IF(E8:E > 0, E8:E*$E$6), IF(F8:F > 0, F8:F*$F$6), IF(G8:G > 0, G124) ),)

If there's a way to do this, I'd really appreciate some help

LINK: a viewable version of a sample I made as my actual sheet is over 500 lines long. https://docs.google.com/spreadsheets/d/133LJHY3s45ZyxWq0PWew1KikbyNE4MTt-wOeWHBrZY0/edit?usp=sharing

In the sample you shared, there is only one value per row, either Euro, British or USD. Will that always be the case? If so, the answer is MUCH simpler. If not, could you change the sample data so that it contains some rows with multiple values? — MattKing
– MattKing, Commented Feb 17, 2020 at 20:25
No, all three columns per row can have a value. I just need the lowest for each column after the conversion. — Kyle Monti
– Kyle Monti, Commented Feb 17, 2020 at 20:34

player0 · Accepted Answer · 2020-02-17 11:59:57Z

2

try (which works for all rows till bottom):

=ARRAYFORMULA(TEXT(SUBSTITUTE(QUERY(TRANSPOSE(QUERY(TRANSPOSE( IF(E3:G<>"", {E3:E*M5, F3:F*N5, G3:G}, 999^99)), "select "&TEXTJOIN(",", 1, "min(Col"&ROW(A3:A)-ROW(A3)+1&")")&"")), "select Col2", 0), 999^99, ), "$#,###.00"))

edited Feb 17, 2020 at 11:59

answered Feb 17, 2020 at 11:52

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kyle Monti Over a year ago

It works nicely, however, when I have multiple boxes with headers at each box, it gives me an overwrite error. Also, my normal table starts at Row 8 instead of 3. So I changed those values, assuming that makes no difference.

=ARRAYFORMULA(TEXT(SUBSTITUTE(QUERY(TRANSPOSE(QUERY(TRANSPOSE( IF(E8:G<>"", {E8:E*$E$6, F8:F*$F$6, G8:G}, 999^99)), "select "&TEXTJOIN(",", 1, "min(Col"&ROW(A8:A)-ROW(A8)+1&")")&"")), "select Col2", 0), 999^99, ), "$#,###.00"))

And if I want the number to have no decimals or different currencies, just remove the .00 and change the symbol? or use CF?

Kyle Monti Over a year ago

I tested it for just a single box, and it works flawlessly. I just want it to skip empty rows, and rows with text in them. Basically, if I duplicated my example twice, I'd need to to continue and ignore the headers, etc.

Kyle Monti Over a year ago

Its actually the header in the formula column H3:H where I need to ignore that header

player0 Over a year ago

docs.google.com/spreadsheets/d/…

JPV · Accepted Answer · 2020-02-17 09:15:16Z

In cell O3 give this a try

=ArrayFormula(TO_DOLLARS(index(transpose(query(transpose(E3:G18*{M5, N5, 1}),"select "&join("),","max(Col"&row(indirect("A3:A18"))-2)&")")),,2)))

and see if that delivers the expected output?

In case, you'll have more then one value in the columns E:G you could try

=ArrayFormula(TO_DOLLARS(index(transpose(query(transpose(if(ISNUMBER(E3:G18), E3:G18, 99^99)*{M5, N5, 1}),"select "&join("),","min(Col"&row(indirect("A3:A18"))-2)&")")),,2)))

Collectives™ on Stack Overflow

Finding a min() value on each row using an arrayformula() within Google Sheets

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related