Returns the key of a row (Ref
value) in the data set that contains the minimum value found in the named column from among the rows selected by an expression, or from among all rows if an expression was not provided.
Sample usage
MINROW("Products", "Discount")
: returns the key value for the row in the Products
data set that has the lowest value in the Discount
column (i.e., which product has the lowest discount?). Equivalent to MINROW("Products", "Discount", TRUE)
.
MINROW("MyTable", "_ROWNUMBER")
: returns the key value for the row in the MyTable
data set that has the lowest value in the _ROWNUMBER
column (i.e., which row was added first?).
MINROW("Students", "GPA", ([Class] = "2018"))
: returns the row for the (one) student with the lowest GPA of the class of 2018.
MINROW("Sales", "Close Date", ([City] = "Madrid"))
: returns the row for the oldest sale in Madrid.
MINROW("Events", "Date", ([Date] > [_THISROW].[Date]))
: returns the row for the next event that is scheduled after the selected event.
MINROW("Events", "Date", ([Date] >= DATE("1/1/" & YEAR(TODAY()))))
: returns the row for the earliest event this year. See also: DATE()
, TODAY()
, YEAR()
MINROW("MyTable", "SomeColumn", FALSE)
returns a blank value because the select-row?
expression will always return FALSE, excluding all rows from the search.
Common problems
MINROW(Events, Date)
produces the error, Expression [...] could not be parsed due to exception: #VALUE!
. In this example, the column name, Date
, has significance within the internals of AppSheet and causes confusion. Any data set name that matches an AppSheet or Excel function name may produce this problem. To fix, quote the problem name: MINROW(Events, "Date")
MINROW("Events", "Date", ([Venue] = [Wanted Venue]))
produces the error, Unable to find column [...]
. Column references within the select-row?
expression (e.g., [Venue]
) are to the row being considered as the data set is searched. To access columns outside the row being considered, such as when using MINROW()
from within a column constraint, app formula, initial value, or format rule, reference the external column using _THISROW
: MINROW("Events", "Date", ([Venue] = [_THISROW].[Wanted Venue]))
Syntax
MINROW(dataset, column, [select-row?])
dataset
- Name of the table or slice (the "data set") to search as a literalText
value (quoted or unquoted); may not be an expression.column
- Name of the column of the named data set in which to find the minimum value, as a literalText
value (quoted or unquoted); may not be an expression.select-row?
- An optionalYes/No
expression, evaluated for each row of the data set, that returnsTRUE
orFALSE
indicating whether the row should be included (TRUE
) or excluded (FALSE
) in the search for the minimum value. If no expression is provided, all rows of the data set will be searched (equivalent to the expression,TRUE
).
If the column minimum was found in more than one row, one of the minimum rows will be chosen at random.