IFS Function in Google Sheets

The IFS function in Google Sheets is used to test multiple conditions and outputs a value specified by the first test that evaluates to true.

It’s akin to a nested IF formula, although it’s not exactly the same. However, if you find yourself creating a nested IF formula then it’s probably easier to use this IFS function.

Example IFS Function in Google Sheets

Suppose you have a set of student exam scores and you want to assign different grades to the students:

Simple IFS Function In Google Sheets

In this scenario, you want to put students into three groups: i) those who score below 50 failed the exam, ii) those with scores between 50 and 79 passed the exam, and iii) students who scored 80 or above passed with distinction.

The IFS Function to do this is:

=IFS(B2<50,"Fail",B2<80,"Pass",B2>=80,"Pass with distinction")

The IFS function consists of pairs of arguments: a condition to test and a value.

If the conditional test is true, the value is displayed and the function stops. If not, and the test is false, the function tries the next test/value pair.

Consider row 2 of the example above, where Bob has a score of 70 in cell B2.

The first logical test and value pair is:

B2<50,"Fail"

The function takes the value of 70 in cell B2 and compares it to the value of 50 to see if it’s less. This is false, so the “Fail” output is not displayed.

Instead, the IFS function in Google Sheets moves to the second logical test and value pair:

B2<80,"Pass"

This time the logical test – is 70 less than 80? – evaluates to true, so the function displays “Pass”.

The IFS formula never reaches the third test/value pair.

The result of the IFS function in Google Sheets is an output that classifies the students:

IFS Function In Google Sheets Exam Grade

You could modify this IFS formula to assign grades “A”, “B”, “C” etc. instead, based on bands.

Note, it can also be done with a nested IF function. This gives the same result as the IFS function, but is more complex to understand:

=IF(B2<50,"Fail",IF(B2<80,"Pass","Pass with distinction"))

IFS Function in Google Sheets: Syntax

=IFS(condition1, value1, [condition2, value2, …])

It takes a minimum of two arguments:

condition1

This is a logical test that evaluates to a TRUE or FALSE value.

For example A1 > 10 or ISBLANK(A1)

value1

If condition1 is TRUE, then the IFS function will output this value.

condition2, value2

These are optional pairs of logical tests and values. If condition 1 is FALSE, the IFS moves on to test condition 2, then 3, then 4 etc.

IFS Function Notes

  • The conditions and values always come in a pair, so the IFS always has an even number of arugments.
  • The function reads the test/value pairs from left to right. It always starts with the leftmost argument as the first logical test.
  • There is no default value to display should all the conditions fail. The IFS function will then output an #N/A! error.
  • However, you can create a default fallback value by adding a penultimate argument TRUE (the logical test) and some value (the fallback value). This is illustrated in the bank account example below.

IFS function template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

You can also read about it in the Google documentation.

IFS Function Account Balance Example

Suppose you have a list of corporate bank balances and a table of interest rates, and you want to add the correct rate against the account balance so you can calculate the interest.

Account IFS Function Example

One way to do this is to use this IFS function in cell B2:

=IFS(A2<50001,0.5%,A2<100001,0.75%,A2<250001,1.25%,A2>=250000,1.75%)

The first test compares the value in cell A2 to see if it’s less than $50,001, which means the account balance is in the $0 – $50,000 tier, so the formula returns a value of 0.5%.

If this first test fails, it tries the second logical test. If that fails, it tries the third logical test, etc.

As it’s written above, the interest rates are hardcoded in the formula, which is generally bad practice (because it’s hard to make changes and easy to make mistakes).

A better solution is to link the output values to the interest rate table, as shown in this version of the formula:

=IFS(A2<50001,$E$2,A2<100001,$E$3,A2<250001,$E$4,A2>=250000,$E$5)

(Exercise for readers: take this a step further and link the bounds (50001, 100001 etc.) to the interest rate table instead of hard coding them.)

The output looks like this:

Account IFS Function Solution

Note: The final logical test can be replaced with the word TRUE, as the catch-all when none of the other conditions are met:

=IFS(A2<50001,0.5%,A2<100001,0.75%,A2<250001,1.25%,TRUE,1.75%)

Alternative Solution

Note that this account example can also be solved with the VLOOKUP function using a TRUE value as the final argument.

This account example is covered in Day 6 of my free Advanced Formulas 30 Day Challenge course.

Advanced IFS Function in Google Sheets

This is somewhat contrived but will give you an idea of what’s possible with the IFS function.

In this scenario, imagine that two parties of buyers have viewed some properties.

The agent wants to record each party’s preferences. He remembers:

“Party 1 liked apartments under $200k”

“Party 2 liked houses or townhouses”

There are various ways you could solve this, including simply recording the preferences manually in a Sheet, but let’s see an IFS formula that does it automatically.

Here’s the data table:

Advanced IFS Formula Data

And here’s the formula in cell D2 that can be dragged down the column and across the row to fill in the peferences:

=IFS(ISBLANK($C3),"",AND(D$1="Party 1",$B3="Apartment",$C3<200000),"Yes", AND(D$1="Party 2", OR($B3="House",$B3="Townhouse")), "Yes",TRUE,"Not interested")

The test/value pairs are grouped as follows:

Test # Test In Formula Output
1 Is cell C3 blank? ISBLANK($C3) ""
2 Is it party 1 and Apartment and less than $200k? AND(D$1 = "Party 1",$B3 = "Apartment",$C3 < 200000) Yes
3 Is it party 2 and house or townhouse? AND(D$1 = "Party 2",OR($B3 = "House",$B3 = "Townhouse")) Yes
4 Catch all when conditions 1 – 3 not true TRUE Not interested

You’ll notice that test/value pairs 2 and 3 show how to use a nested AND function and a nested OR function to combine conditions.

The output of this formula is:

Advanced IFS Formula

SPLIT Function in Google Sheets

The SPLIT function in Google Sheets is used to divide a text string (or value) around a given delimiter, and output the separate pieces into their own cells.

SPLIT Function Examples

Let’s see a simple example using SPLIT to separate a list of names in cell A1:

Split Function Name Example

This simple SPLIT formula will separate these names, using the comma as the separator:

=SPLIT(A1,",")

The result is 5 cells, each containing a name. Note that one cell looks blank because the text string in cell A1 has two adjacent commas with a space between them. The “space” is interpreted in the same way as the names and contained in the output:

Split Function Name Example

Now watch what happens if we include a space in the delimiter, i.e. ", "

=SPLIT(A1,", ")

The function splits on the comma "," and on the space " ", so the name “Mary Jo” split in two:

Undesirable behavior with SPLIT formula

This is probably not the desired behavior.

The third argument is an optional TRUE or FALSE that determines whether SPLIT considers each individual character of the delimiter (TRUE) or only the full combination as the separator to use (FALSE).

In our example, adding FALSE ensures that it only considers the combined comma/space string as the delimiter:

=SPLIT(A1,", ", FALSE)

And the output looks like this:

Split names in Google Sheets

There is a fourth argument too, which is optional and takes a TRUE/FALS value. It determines whether to remove blank cells or not in the output.

To illustrate this, consider this arrangement of data separated by semi-colons. Note the presence of two adjacent semi-colons with no data between them:

Split Formula Fourth Argument

The fourth argument determines whether to show or hide the blank cell caused by the two adjacent semi-colons.

To keep the blank cells, add FALSE as the fourth argument:

=SPLIT(A2,",", TRUE, FALSE)

SPLIT Function in Google Sheets: Syntax

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

It takes 4 arguments:

text

This is the text string or value in the cell that you want to split. It can also be a reference to a cell with a value in, or even the output of a nested formula, provided that output is a string or value and not an array.

delimiter

The character or characters used to split the text. Note that by default, all characters are used in the division. So a delimiter of “the” will split a text string on “the”, “he”,”t”,”h”,”e” etc.

This behavior can be controlled by the next argument:

split_by_each

This argument is optional and takes a TRUE or FALSE value only. If omitted, it’s assumed to be TRUE.

The TRUE behavior splits by individual characters in the delimiter and any combination of them. The FALSE behavior does not consider the characters separately, and only divides on the entire delimiter.

remove_empty_text

The fourth and final argument is optional and takes a TRUE or FALSE value only. If omitted, it’s assumed to be TRUE.

It specifies what to do with empty results in the SPLIT output. For example, suppose you’re splitting a text string with a "," and your string looks like this: “Ben,Bob,,Jenny,Anna”

Between the names Bob and Jenny are two commas with no value between them.

Setting this final argument of the SPLIT function to FALSE results in a blank cell in the output. If this fourth argument is omitted or set to TRUE, then the blank cell is removed and “Bob” and “Jenny” appear in adjacent cells.

SPLIT Function Notes

  • Delimiters in SPLIT are case sensitive. So “t” only splits on lower-case t’s in the text
  • The SPLIT function requires enough “space” for its output. If it splits a text string into 4 elements then it requires 4 cells (including the one the formula is in) on that row to expand into. If there is already data in any of these cells, it does NOT overwrite it but instead shows a #REF! error message
  • You can input a range as the first argument to the SPLIT function, but it requires an Array Formula wrapper to work
  • The output from the SPLIT function is an array of values that can be passed as the input into another formula, which may require the use of the Array Formula

Alternative Split Method

There’s an alternative way to split values in a Google Sheet.

Under the Data menu, there’s a feature called “Split text to columns” which will separate single columns into multiple columns, based on the delimiter you specify.

It’s a quick and easy way to split text.

Note that it overwrites existing data in your Sheet if the split columns overlap with any existing data.

Split function alternative

SPLIT Function Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

You can also read about it in the Google documentation.


Advanced Examples of the SPLIT Formula in Google Sheets

Extract The N-th Word In A Sentence

You can wrap the SPLIT function output with an INDEX function to extract the word at a given position in a sentence. E.g. to extract the 4th word, use this formula:

=INDEX(SPLIT(A1," "),4)

Extract Nth Word in Google Sheets

If you combine this with a drop down menu using data validation, you can create a word extractor:

extract Nth Word Data Validation

Alphabetize Comma-Separated Strings With The SPLIT Function in Google Sheets

Suppose you have a list of words in a single cell that you want to sort alphabetically:

Formula Challenge 3

This formula will rearrange that list alphabetically:

=JOIN(",",SORT(TRANSPOSE(SPLIT(A1,","))))

It splits the string of words, applies the TRANSPOSE function to convert into a column so it can be sorted using the SORT function, and then recombines it with the JOIN function.

Formula Challenge 3 Solution

Read more in Formula Challenge #3: Alphabetize Comma-Separated Strings.

Splitting and Concatenating Strings

The SPLIT is useful in more advanced formulas as a way to divide an array into separate elements, do some work on those elements (e.g. sort them) before recombining them with another function, like the JOIN function.

For example, this formula will add surnames to a list of first names in a cell:

=ArrayFormula(TRIM(JOIN(", ",SPLIT(A2,", ")&" Smith")))

which looks like this in your Google Sheet:

Split Function In Google Sheets To Add Surnames

Using the onion framework to analyze this formula, starting from the innermost function and working out, it splits the text string, joins on the surname “Smith”, trims the excess trailing space and finally outputs an array by using the Array Formula.

Advanced SPLIT Array Techniques

The SPLIT function in Google Sheets is used in a number of the complex IMPORT formulas for retrieving social media statistics into your Google Sheet.

The SPLIT function was also used in this exceedingly wacky unpivot formula in Google Sheets (see Solution 4):

=ArrayFormula({"Customer","Product","Value";
QUERY(IFERROR(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )), , 500000)), , 500000)),"🌶"))),"🐠"),""),"SELECT Col2, Col1, Col3 ORDER BY Col2 OFFSET 1",0)})

All in all, SPLIT is a useful function!

REPT Function in Google Sheets

The REPT function in Google Sheets is used to repeat an expression a set number of times.

The REPT formula to repeat “Go! ” three times is:

=REPT("Go! ", 3)

Notice the additional space added after the exclamation point, so that there is a space between the repeated values in the output.

More REPT Function Examples

Rept formula examples in Google Sheets

Column A contains the values you want to repeat.

Column B contains the number of repetitions.

The formula is then very straightforward:

=REPT(A2,B2)

As you can see, for each row the value in A is repeated per the number in column B and joined into one long string.

REPT Function in Google Sheets: Syntax

The REPT function takes two arguments and both are required.

=REPT(text_to_repeat, number_of_repetitions)

text_to_repeat

This is a text string or cell reference that you want to repeat.

number_of_repetitions

This is a positive integer indicating how many times you want to repeat the input text.

Notes on using the REPT function

  • The return value (the output of the REPT function) is a string value in a single cell
  • Setting the number of repetitions to 0 results in a cell containing a blank string (not a true blank cell though. ISBLANK will still give FALSE)
  • Setting the number of repetitions to -1 results in a #VALUE! error
  • If you want spaces between the repeated text strings, you must add that as the final character of the input string (as shown in the "Go! " example above). This results in a final trailing space, which can be removed with the TRIM function if required
  • The number_of_repetitions can’t exceed the character limit of a cell: 32,000 characters. If it does, you’ll see a #VALUE! error
  • Ranges can be repeated by wrapping the REPT function in an Array Formula

REPT function template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

You can also read about it in the Google documentation.

Using The REPT Function For Repeated Values In Separate Cells

What if you don’t want to create a long string of repeated values in a single cell, but instead want to output the repeated value across a range of cells, such that each value is in its own cell?

That’s possible with this formula, assuming the value is in cell A1 and the number of repetitions is in cell B1:

=TRANSPOSE(SPLIT(REPT(A1&"♕",B1),"♕"))

This particular formula outputs 10 values “text” in a column. (You can remove the transpose if you want the output across a row.)

The Queen symbol “♕” is added to the end of the repeated value to act as a unique value used by the SPLIT function to divide the repeated string.

REPT formula to output values in separate cell

Note: you can also achieve this affect with the SEQUENCE function:

=ArrayFormula(TEXT(SEQUENCE(B1),"")&A1)

This particular variation uses the SEQUENCE function to output an array 1,2,3,…10. The TEXT function then “tricks” all of these to become empty strings. We then concatenate the repeated value from cell A1 onto this string. Finally, the Array Formula ensures that the output is an array.

Repeated Images with REPT Formula in Google Sheets

This REPT formula will repeat the specified image across a row. It uses the same Queen symbol “♕” trick as the previous formula.

=ArrayFormula(IMAGE(SPLIT(REPT("https://www.google.com/favicon.ico"&"♕",5),"♕")))

The output looks like this:

Repeated image using the REPT function in Google Sheets

Using REPT As Logical Formulas

The REPT function can be used instead of IF formulas in specific situations.

It’s quite a clever trick that works because TRUE is equivalent to the number 1, and FALSE to the number 0, in formulas.

So, by putting a logical test, which evaluates to TRUE or FALSE, equivalent 1 or 0, in the number of repetitions argument, we can either show 1 value or 0 values using REPT.

For example, this formula identifies values over $1,000:

=REPT("Over $1,000",A1 > 1000)

And the output looks like this:

Rept Function Logical Formula

This formula works because the logical test checks if the value in cell A1 is greater than $1,000. If the result is TRUE, it’s interpreted as a value of 1, which then repeats the text in the REPT formula once. So the output is “Over $1,000”.

If the value is less than or equal to $1,000, then the test evaluates to FALSE, equivalent to 0 input for the REPT formula, so the output is a blank string in the cell.

This technique can be taken a step further to check multiple conditions are TRUE.

For example, the following formula checks whether the value is over $1,000 AND comes from Client A:

=REPT("Over $1,000 and Client A",(B1>1000)*(A1="Client A"))

The two logical tests evaluate to TRUE or FALSE, which are interpreted as 1 or 0 by the formula.

When the value is greater than $1,000 and from Client A, it’s equivalent to:

TRUE * TRUE = 1 * 1 = 1

In which case, the formula outputs the string “Over $1,000 and Client A”

Multiplication using “*” as shown above is for the AND case, where both conditions are true.

To do the OR case, you use addition “+”. If either value is TRUE and the other FALSE, it’s equivalent to 1 + 0 = 1. When both are true it’s equivalent to 1 + 1 = 2, so the REPT would repeat the value twice which we don’t want.

To fix this, wrap it with a MIN formula to set to 1 if the value is above 1.

=REPT("Over $1,000 and Client A",MIN((B1>1000)+(A1="Client A"),1)

In Cell Charts with REPT Function in Google Sheets

Bar chart with REPT Function

In cell bar chart With Rept Function

The formula to do this is:

=REPT(CHAR(10074),A1)

You can use any character as the repeated symbol, for example you could use tacos with CHAR(127790):

tacos in Google Sheets

Vertical Bar Chart with REPT function

You can easily extend the technique above to display the bar chart in a single cell, with a vertical orientation, using this formula:

=ArrayFormula(JOIN(CHAR(10),REPT(CHAR(9679),A1:A6)))

which looks like this (using dots as the repeated symbol):

Rept Column Chart

Dynamic Width Bar Charts Using REPT function

This is an interesting, although not particularly useful, formula which uses the CELL function to access the width of a cell and then sets the REPT to repeat the value to match the width of the cell:

Dynamic Width Bar Chart With Rept

The formula to do this is:

=REPT(CHAR(10074),ROUND((A2/MAX($A$2:$A$4))*(CELL("width",$B$1)*1.25),0))

It uses the random number generator and the spreadsheet calculation setting to be “On change and every minute” to force the CELL function to re-calculate the width value each time there is a change.

The 1.25 factor at the end is simply to adjust the largest bar to fit the full width. It depends on the character you use to repeat and how wide it is. Feel free to experiment.

Padding Strings With The REPT Function

REPT can also be used to pad text strings.

With a value in A1, this formula adds a variable number of underscores to the end of the string:

=A1&REPT("_",20-LEN(A1))

If you want to have the text strings to have equal widths you’ll also need to use a font where the letters are equally spaced, like SOURCE CODE PRO.

This approach can also be achieved with numbers, although it’s much better to use custom number formatting, because that preserves the value as type number instead of converting to a string.

5 Insights From Taking A Live Cohort-Based Course (And How I’m Applying Them To My Own)

Cohort-based course transformation
Sketching Billy Broas’ Bridge of Transformation model for my cohort-based course!

I recently joined 40 other entrepreneurs for the first cohort of Billy Broas’ new live online cohort-based course, the Keystone Accelerator.

It’s an 8-week program covering how to market and launch cohort-based courses (CBCs).

Cohort-based courses are online courses where a group of students join at the same time and progress through the course together. Typically, students in a CBC meet online via Zoom for interactive sessions and work on course materials between sessions.

They’re fundamentally different to self-paced video courses because the emphasis shifts from being content-centric to being community-centric.

Students get the benefit of accountability, a peer group and expert teacher(s) for questions, and a community in which to celebrate wins and share challenges. CBC’s provide structure and guidance for students. Consequently, they have much higher completion rates than self-paced courses and better outcomes for students.

I joined this Keystone cohort course because I’m creating my own live training course and I wanted to learn from the experts on how to build, market and sell these types of courses.

We met twice a week for 90 minutes over Zoom – with participants calling in from all over the world – to learn an education-based marketing framework. In addition, I joined weekly peer coaching groups to brainstorm ideas and critique each other’s work in a safe environment.

Here are my top five takeaways from taking a cohort-based course that I want apply to my own course, the Pro Sheets Accelerator:

1. It Is Possible To Have Transformative Experiences Online

I’d never done a live cohort-based course before Keystone. I’d experienced plenty of webinars, in-person courses and self-paced video courses.

But this was different.

Keystone was unlike any other learning experience I’ve had.

It combined the content of an on-demand video course with the accountability, rapid feedback and community of an in-person event.

Accountability pushes you to show up and do the work.

Rapid feedback means you don’t get stuck for long periods and learn quickly from your mistakes.

Community provides a safe space to share wins and challenges and make new friends.

Combining these three factors with world-class content is the best way to facilitate the student transformation.

For first cohort of the Pro Sheets Accelerator, I want to foster a really strong community to maximize these benefits.

If you join, you’ll be in a group of 30 – 40 students, with a private course forum, peer groups and office hours, in addition to the main teaching sessions.

2. The Connections You Make Are As Valuable As The Course Content

I joined a group of 40 super smart, motivated entrepreneurs, all building their own live training courses in wildly different industries.

Some were earlier in their journeys than me, some were further along.

Together, the breadth of experiences, ideas and insights far surpassed what I, as an individual, could have achieved.

Truly a case of the whole being greater than the sum of its parts. 

I learned a lot from the peer coaching sessions, both from giving and receiving feedback. Seeing how other people solve problems, how they think and how they approach opportunities and challenges, was hugely valuable.

Like a wifi network that gets stronger as more nodes are added and interlinked, the new connections you make through a CBC improve your chances of success with your own business.

3. Learning With Others Is More Fun Than Learning Alone

I’m a firm believer that education should be enjoyable and that you learn best when you’re having fun. We should take inspiration from young children who learn through play.

If it’s enjoyable, you’re more likely to succeed.

Instead of watching the clock and counting down until the end of a boring lecture, you’re emotionally present and absorbing information. You’re in the zone of proximal development.

In the past year, we’ve all been starved of human connection and friendship.

Although CBCs are online, you’re still going through an intense shared experience with other folks.

Naturally friendships form as people get to know each other and become more comfortable. You begin to see beneath the surface and really learn from each other.

The Pro Sheets Accelerator will be a fun experience. My goal is for everyone to enjoy the course first and foremost.

4. The Tangential Ideas Are Just As Valuable As The Core Content

The Keystone course I participated in is a marketing course. Its promise is that it’ll teach you how to market and sell cohort-based courses.

But along the way, I picked up tons of other ideas. Ideas that weren’t on the syllabus but arose through digressions, chats with peers or from surprise bonus sessions with guest experts.

Simply from being part of this cohort-based course, I learned about how to run one. I saw first hand how many moving pieces there are and why you need help to run one. Everything from the first onboarding call through to how to structure the live Zoom calls and exercises.

Tiago Forte, one of the world’s leading experts on CBCs who runs the hugely successful Building A Second Brain course, joined this Keystone course and added his perspective to the program.

In addition, we had workshops on the operations side of running CBCs from course director Will Mannon, and on how to create engaging experiences for our students from learning designer Andrew Barry. Both of these added valuable insights outside of the marketing curriculum of the main course.

Encouraging curiosity, fostering peer-led learning and surprising students with guest speakers are all great ways to add value to a CBC.

I plan to implement all of these in the Pro Sheets Accelerator course.

5. Less Is More in Cohort-Based Courses

When it comes to content, less is more.

What this means in practice is that the emphasis of the course shifts from cramming in as much content as possible (a traditional signal of value) to focusing on students’ transformational learning experiences.

The goal of a CBC is for your students’ life to change. You’re guiding them across the bridge from their current status quo to the new, better life.

For example, in the Keystone course, I came away with a much deeper understanding of CBCs and education based marketing. I now have a playbook I can apply to my own business.

What I don’t have is a library of 300 videos on marketing, which I would never have the time to watch, much less implement.

This realization with regards to content – that less is more – was a key shift I noticed in myself during this Keystone course.

For the Pro Sheets Accelerator cohort-based course, my original plan was to focus on making it as comprehensive as possible, covering Google Sheets and Apps Script from top to bottom.

It would have been impossible to achieve and unmanageable for students.

Now, my plan is to focus on two areas:

  1. Developing a framework and skillset for doing data analysis with Google Sheets, and
  2. Automating that framework

Yes, they’re still big topics, but they’re focussed. I can build a syllabus that goes deep into these subjects and delivers huge value, in a way that won’t overwhelm students.

The material will be relevant. Students will learn just enough to experience a transformation but not too much that it gets diluted.

Applying These Lessons To Pro Sheets Accelerator

It’s been 4 years since I launched my first online course, How To Build Dashboards in Google Sheets.

Since then, over 40,000 students from 1,000s of organizations have registered for one or more of my online courses.

All of these courses are self-serve video courses.

Now it’s time to add a new experience-based course into the mix, as the next evolution of my education business.

I’m building a cohort-based course called Pro Sheets Accelerator, which will teach you how to leverage the power of data and automation in Google Sheets to grow your business and career.

The first cohort begins at the end of April. I’m super excited to bring together a group of Sheets aficionados for a transformative learning experience. Join us!

Sign up here to hear more about the Pro Sheets Accelerator cohort-based course:

PI Function in Google Sheets And Other Fun π Facts

The PI function in Google Sheets returns the value of the mathematical constant pi (π) to 9 decimal places: 3.141592654

Pi is defined as the ratio of a circle’s circumference to its diameter.

Pi is denoted by the Greek lowercase letter π

PI Function in Google Sheets: Syntax

=PI()

There are no arguments. If you put anything between the brackets the formula will give you an error.

Circle Calculations With The PI Function

If you know the circumference of a circle then you can calculate the diameter and vice versa.

How to calculate the circumference

Suppose you have a diameter of 10 (the units are irrelevant).

Then you calculate the circumference with this formula: the diameter multiplied by pi.

= 10 * PI()

How to calculate the diameter

Now suppose you know the circumference is value 10.

The diameter is calculated as the circumference divided by pi:

= 10 / PI()

How to calculate the area of a circle

The formula for the area of a circle is π * r² (pi times the radius squared).

To calculate the radius, you must divide the diameter by 2.

So, knowing the diameter d, you can calculate the area of a circle as follows: π * (d/2)²

Taking a diameter of 10, the area is:

= PI() * ( 10 / 2 )^2

for an answer 78.5398

Creating the PI Symbol with the CHAR Function

The amazing CHAR function, which converts numbers into characters per the Unicode table, can output the symbol for pi in your Google Sheet.

Use this formula to output the pi symbol π:

=CHAR(960)

Setting the cell to the Merriweather font gives it the nice rounded appearance shown in this image:

Pi Symbol With Char Function

By the way, this formula:

=CHAR(129383)

will output an image of an entirely different type of pie in your Google Sheet:

Pie in a Google Sheet

PI function template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

You can also read about it in the Google documentation.

Approximating Pi in Google Sheets With the Nilakantha Series

The Nilakantha series, named after the 15th century mathematician of the same name, is an infinite series that converges on pi as the number of iterations rises. It converges quickly so it’s a good algorithm to use to compute the digits of pi.

The Nilakantha series is:

Nilakantha Series
Image source: Wikipedia

Let’s build an approximation of pi in a Google Sheet using this series.

Nilakantha Series in Google Sheets With The PI Function

In cell A1, enter this SEQUENCE formula:

=SEQUENCE(1000,1,2,2)

And in B1:

=SEQUENCE(1000,1,3,2)

Then in C1:

=SEQUENCE(1000,1,4,2)

These three formulas give us three columns of numbers with 1,000 digits each. They start with 2, 3 and 4 respectively and increment by 2.

Look closely at the infinite series definition above and you’ll notice that each row represents a denominator for each iteration of the series.

In cell D1, multiply the values together for each row:

=PRODUCT(A1:C1)

Copy this formula down the column.

This formula generates the denominators for the series.

In cell E1, put this product as the denominator to calculate the series values:

= 4 / D1

Next, we need to alternate adding and subtracting these values, which we can do with this formula:

=IF( ISODD( ROW() ), E1 , UMINUS( E1 ) )

This IF formula checks if we’re on an odd numbered row (e.g. row 1) using the ISODD function and, if we are, returns the number unchanged. But if we’re on an even numbered row, it returns the negative value from column E, using the UMINUS function.

Finally we can approximate pi in column G.

In G1, put this formula to start the series:

= 3 + F1

In cell G2, put this formula:

= G1 + F2

and drag this formula all the way to the bottom of the range.

With 1,000 rows, we get a value for pi of 3.1415926533, which is a similar level of accuracy to the PI function.

Here are the first 16 iterations, the remaining rows up to 1,000 can be found in the template linked above in this article:

Nilakantha Series to approximate pi in Google Sheets

Now, you might be wondering how many digits of pi have been calculated…

…well it’s a whopping 31.4 trillion digits!

Obviously, that is not a calculation you can perform in your Google Sheet, but interestingly, it was performed on Google Cloud infrastructure!

Update: The record is now 50 trillion digits, broken by an enthusiast with a self-built server setup. Super impressive!

Read more about the chronology of computing digits of pi.

Getting More Digits of Pi in Google Sheets

Whilst we can’t get 50 trillion digits of pi into a Google Sheet, we can call the Pi Delivery API and retrieve more digits.

Here’s the code to request the first 1,000 digits of pi and display them in a Google Sheet as a text string (because numbers get truncated):

/** 
 * function to call the Pi API
 * https://pi.delivery/#apipi_get
 */
function getPI() {
  
  // endpoint
  const root = 'https://api.pi.delivery/v1/pi';
  
  // start point and number of digits
  const startDigit = 0;
  const numDigits = 1000; // max request is 1000 digits in 1 call

  // create endpoint
  const endpoint = root + '?start=' + startDigit + '&numberOfDigits=' + numDigits;

  // call the PI API to fetch digits of pi
  const response = UrlFetchApp.fetch(endpoint);
  const result = JSON.parse(response.getContentText());
  
  // get pi
  // keep pi as a string otherwise it gets truncated if a number
  // add single tick in front of the 3 to avoid automatic conversion to number in Sheet
  const piDigits = result.content;
  const pi = "'3." + piDigits.substring(1); 

  // paste result into Google Sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const piSheet = ss.getSheetByName('PI API');
  piSheet.getRange(3,1).setValue(pi);
  
}

The API is limited to calls of up to 1,000 digits at a time, so if you want more than that you’ll need to expand the script above to request batches of 1,000.

More Resources For Mathematics in Google Sheets

Complex Numbers In Google Sheets

How To Draw The MandelBrot Set In Google Sheets, Using Only Formulas