Formulas: Min() and Max()
Determines the minimum value from a list of numbers or dates/times
or stated otherwise...
Hey Coda, I have a list of numbers. Can you tell me which one is the smallest?”
Determines the maximum value from a list of numbers or dates/times
put a bit simpler...
Hey Coda, here I have a list of numbers. Can you tell me which one is the largest?
Min() and Max() each share the same, single parameter:
A number or list of numbers.
Below we have a list of numbers, dates, and currencies stored in the canvas. Watch out how Max() and Min() are able to pull out the smallest and largest values as you change the toggle below.
Its easy to see what these Min() and Max() formulas do when they are pulling from a Canvas based formula, but you will use them most often in workflows that involve tables.
Remember, tables are also treated as lists even though it may not always be as obvious. Let take this table for example of trees:
Trees and their Height
Difference from Tallest Tree
Lets look at how we are using Max() and to enrich our table and data
Above the table itself you will notice that the Tallest Tree’s height is identified using a Max() formula.
And the Shortest Tree’s height is identified using the Min() formula.
The Yellow column above uses the Max() formula to find the tallest tree in the table, then simply subtracts the current rows height from that value. This allows us to find the difference between the current rows tree height against the tallest tree.
The Tree Analysis column above is a more complex formula, but relies on Max() to identify the tallest tree and then output an analysis statement.
A (Long) Additional Note
There are two other formulas that are very similar to Max() and Min() that have are worth mentioning that have their own unique pros/cons and use cases.
Those formulas are: MinBy() and MaxBy()
These formulas can be used on a list of values (as opposed to a list of row-references) but when so it is functionally equivalent to Min() and Max() and is actually more complicated to write.
Take this list of numbers for example:
Where you will want to use MinBy() and MaxBy() is when attempting to return a row reference that contains a minimum or maximum value.
As an example, let look at the database with rows and the following columns:
Let’s say you wanted to find the individual with the highest salary. If you simply use Max() you will only return the salary number rather than the name.
MaxBy() would allow you to pull out the name and all other properties for the individual with the highest salary.
There are some disadvantage to using MaxBy() and MinBy(), though. Let’s look at this example:
You want to look at your database and find the oldest individuals.
Pros: Using Max() will return a full data set! Each of the individuals above is 80 years old and 80 is the oldest person in the data-set Cons: This is a little bit harder formula to write
Pros: Using MaxBy() is more user friendly in that its formula is easier to write Cons: MaxBy() and MinBy() only return the first match from the dataset even if multiple items have the same value.