CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaminarray-formulasgoogle-sheets-query
Kyle Monti
Kyle Monti

Reputation: 704

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

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

Upvotes: 1

Views: 2070

Answers (2)

player0
player0

Reputation: 1

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"))

0

Upvotes: 2

JPV
JPV

Reputation: 27282

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)))

Upvotes: 1

Related Questions