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

1
votes

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

2
google-sheetsgoogle-sheets-formulaminarray-formulasgoogle-sheets-query
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
No, all three columns per row can have a value. I just need the lowest for each column after the conversion. - Kyle Monti

2 Answers

2
votes

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

1
votes

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