Formatter Features: RAND/RANDBETWEEN and MIN/MAX in spreadsheet-style formulas

  • 20 July 2021
  • 0 replies
  • 42 views
Formatter Features: RAND/RANDBETWEEN and MIN/MAX in spreadsheet-style formulas
Userlevel 7
Badge +10

What’s up Zappers? Welcome to the 3rd installment of the Formatter Features series. If you haven’t done so already, please check out the first two:

What We’ll be Covering

RAND / RANDBETWEEN

RAND: Returns a random number between 0 and 1
RANDBETWEEN: Returns a random number between two integers, respectively.

MIN / MAX

MIN: Returns the smallest value from a list of numbers
MAX: Returns the largest value from a list of numbers

When Might You Use Each One?

Let’s take a look at some use cases for each of the above-mentioned functions. That might help you to understand the relevance of these particular spreadsheet-style formulas.

RAND

When might you want to return a number that’s between 0 and 1, you ask? Though I suspect RANDBETWEEN is used more frequently here’s one way you could use RAND.

RAND()

 

Generating a Number in Order to Randomize a List of Items

Though there are likely more efficient ways to do this, you could use RAND in order to produce random numbers that then get added to a spreadsheet to be sorted by, thus randomizing the list.

NOTE: Of course, you can also use RAND in the spreadsheet directly. But maybe these people are being added to an email list or CRM first, then exported into a CSV and it’s just as easy to have the column pre-populated. If the action step is a spreadsheet you can also add the formula directly into the field (see below).

99214f53c6b84cd301cfcc9e099f14b5.gif

RANDBETWEEN

I can think of more reasons to use RANDBETWEEN than the aforementioned RAND function. 

RANDBETWEEN(number 1, number 2)

 

Generating a Random Number for a Delay Step
Let’s say you want to add a Delay step to your Zap, but you don’t always want it to be for the same amount of time. Sometimes when automating actions we want to give the impression that it’s random and not being automated.

EXAMPLE:

 

Generating a Random String for Something Like an Order ID
There are times when you may need to provide a random number to the action app, for an invoice or order number (for example). Using RANDBETWEEN is one way to generate such a number :)

EXAMPLE:

MIN / MAX

These are two sides of the same coin. One will return the smallest number from a comma-separated list of numbers and the other returns the largest.

MIN(number, [number, …])

 

Reasons why you might want to return the smallest number:

  • Identifying the least costly item among multiple products
  • Outputting the smallest number of errors amongst a list
  • Indicating the shortest or lightest item from a group of items/people
  • Showing the smallest number of employees amongst a list of startups

 

MAX(number, [number, …]):

 

Reasons why you might want to return the largest number:

  • Identifying the highest amount of weight lost among a group of people
  • Outputting the highest test score, from a list of scores
  • Indicating the tallest or heaviest item from a group of items/people
  • Showing the highest population from a list of countries or cities

NOTE: These functions will simply return the number. If you wanted to match the number to the item it belongs to, that would require some additional steps — maybe some IF / AND / OR functions as shown here: 

 

 

Other Resources

If you’re looking for more information, here are a few places you can look:

  1. Community articles
  2. Zapier’s help docs (here + here)
  3. The other spreadsheet-style Community posts (first, second).
  4. This video from the first version of Zapier University 

Wrapping Up 

Well that’s it for this installment of Formatter Features. Next week, we’ve got the last spreadsheet-style formulas post and after that we’ll be moving on to date formatting :)


0 replies

Be the first to reply!

Reply