Surprisingly simple formulas with a surprisingly large number of use cases.

Surprisingly simple formulas with a surprisingly large number of use cases.

Formulas: Min() and Max()

Category: Math

Min()

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?”

Max()

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:

Value

Min(Value)

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.

Min() values

Max() values

Min Value:

2

List of numbers:

5

8

2

5

Min Value:

3/2/2024

List of dates:

3/2/2024

3/3/2024

3/4/2024

3/5/2024

Min Value:

List of currencies:

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

Tallest Tree

380

feet

Shortest Tree

100

feet

Tree

Height in Feet

Height in Meters

Difference from Tallest Tree

Tree Analysis

Tree

Height in Feet

Height in Meters

Difference from Tallest Tree

Tree Analysis

1

Coast Redwood

380

115.82

0

The Coast Redwood is the tallest tree!

2

Sugar Maple

115

35.05

265

The Sugar Maple tree is 265 feet shorter than the Coast Redwood

3

Sycamore

100

30.48

280

The Sycamore tree is 280 feet shorter than the Coast Redwood

4

Douglas Fir

327

99.67

53

The Douglas Fir tree is 53 feet shorter than the Coast Redwood

5

Swamp Gum

326

99.36

54

The Swamp Gum tree is 54 feet shorter than the Coast Redwood

No results from filter

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:

12345

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

Broken link

database with

rows and the following columns:

Using Max()

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.

Heres an example:

Using MaxBy()

MaxBy() would allow you to pull out the name and all other properties for the individual with the highest salary.

Heres an example:

A Warning

There are some disadvantage to using MaxBy() and MinBy(), though. Let’s look at this example:

Using Max()

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

Using MaxBy()

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.

Practice: Use a canvas based formula to find the minimum value of a list

Done

Use a canvas based formula to find the maximum value in a list

Done

Cause a checkbox field to be checked if that row is the minimum value in the table

Done

Cause a button to open the most recently created row in a table