Count Attendance and Absence with Google Sheets COUNTIF

In our campus life and work life, we usually record everyone’s attendance. Today we will introduce you the application of Google Sheets COUNTIF function to count the attendance.

Using a week as an example, we use the following example to show you how to use Google Sheets COUNTIF function to count attendance.

Count Attendance and Absence1

As shown above, attendance and absence are counted correctly by Google Sheets COUNTIF function.

FORMULA

In this example, we have used only the COUNTIF function, without nesting other functions. In a simple case like the one above, we can use COUNTIF function to solve our problem directly.

COUNTIF function belongs to Google Sheets Statistical functions. It counts the number of cells that meet the provided criterion in a certain range.

Syntax:

=COUNTIF(range, criteria)

In this example, we can directly apply this function with entering our own range and criteria.

=COUNTIF(B2:F2,"√") – in G2

EXPLANATION

In this example, the “√” in the cell indicates attendance on the corresponding day. Calculating attendance is equivalent to calculating how many ticks there are in B2:F2.

FULL ATTENDANCE

Use G2 as an example, the range for recording attendance is B2:F2. The condition is “√”. So the formula is =COUNTIF(B2:F2,”√”). We cannot enter “√” in this formula, but we can copy a √ into criteria field and include it in brackets.

In the formula bar, we can expand B2:F2, we can get an array of “√”

=COUNTIF({"√","√","√","√","√"},"√")

Count Attendance and Absence1

Google Sheets COUNTIF function will count the number of times “√” appears in the array.

Obviously, the result is 5.

PARTIAL ATTANDENCE

Copy down the formula. G3 for example, B3 and E3 cells in the absence of “√”, so in this formula, there are only three “√” in the range.

Count Attendance and Absence1

After running the formula, the result is 3.

Count Attendance and Absence1

ABSENCE

If the cell is empty, it means it was absent that day. Just replace “√” with “”.

The formula is

=COUNTIF(B2:F2,"")

Count Attendance and Absence1

Note that there are no spaces between the “”. If we enter a space, such as ” “, we will count how many cells in the range B2:F2 contain spaces.

Count Attendance and Absence1

Related Functions

  • Google Sheets COUNTIF function
    The Google Sheets COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…

Calculate Average Of Last 5 Or N Values In Columns in Google Sheets

Suppose you come across a task where you need to calculate the average of the last 2 or 3 numeric values in google sheets, then what would you do?

If you are new to google sheets, then your first attempt might be doing this task manually, which is an acceptable way but only when the values of which you want to calculate the average is limited to 2 or 3 but when it comes to calculating the last 5 or N values in the columns then it becomes nearly impossible to do this cumbersome task on time!

Don’t worry about it because after reading this article, you will know the easiest way to calculate the average of the last 5 or N values from the columns within seconds.

So, let’s dive into the article.

Average last N values in columns in google sheets1

General Formula in google sheets

To get the average of the last N values in google sheets, use the formula below.

=AVERAGE(OFFSET(first cell,0,COUNT(range_values)-N,1,N))

Explanations For Syntax:

  • AVERAGE: In google sheets, the AVERAGE Function may be used to calculate the arithmetic mean of a set of integers.
  • OFFSET: This Function outputs a reference to a range made up of pieces such as a beginning point, a row, and column offset, and a final height and width in rows and columns. Learn more about the OFFSET Function.
  • COUNT: The COUNT Function returns the result as a number and counts the number of cells that contain numbers.
  • First Cell: It indicates the first cell in the provided input range.
  • Range: This is the input value from your google spreadsheet.
  • The comma sign (,): is a separator that separates a list of values.
  • Parenthesis (): The main Function of this symbol is to group the elements.
  • Minus Operator (-): This symbol subtracts 2 values.

Explanation

To average the latest 5 data values in a range of columns in google sheets, use the AVERAGE Function conjunction with the COUNT and OFFSET functions. The formula in I2 in the example is as follows:

=AVERAGE(OFFSET(A2,0,COUNT(A2:G2)-5,1,5))

  Average last N values in columns in google sheets1

The OFFSET function may create dynamic ranges from a beginning cell and specify rows, columns, height, and width.

The rows and columns parameters act as “offsets” from the beginning reference. The height and width parameters, all optional, decide how many rows and columns are included in the final range. We want the OFFSET function to return a range that starts at the last item and grows “backward,” therefore we provide the following arguments:

A2 is the initial reference — it is the cell directly to the right of the formula and the first cell in the range of values we are dealing with.

Rows – We use 0 for the rows option because we want to stay in the same row.

Columns – We use the COUNT function to count all values in the range for the columns input, then deduct 5. This moves the start of the range of 5 columns to the left.

Height – we use 1 since we want a 1-row range as the end result.

Width – we pick 5 since we want a final range with 5 columns.

For the formula in I2, OFFSET yields a final range of C2:G2. This is sent to the AVERAGE Function, which returns the average of the five values in the range.

To understand better, consider the following example, which follows a step-by-step procedure:

  • Consider the following example to calculate the average of the last 5 values.
  • This illustration will provide input data from Column A to Column G.
  • Next, enter the supplied formula in the formula bar section.
  • Finally, we shall obtain the result in the selected cell I2.

Less Than 5 Values Average in google sheets

If you are want to compute the average of the last 2, 3, or 4 data in google sheets, you don’t need to do it manually. Utilizing the formula discussed above will result in a circular reference mistake when computing the average of the 2, 3, or 4 values in the columns; the range will extend back into the cell that contains the formula. To avoid this mistake and complete your task, modify the formula as follows:

=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))

So If you want to average last N values in columns that less than 5 values in google sheets, you can use the following formula:

=AVERAGE(OFFSET(A2,0,COUNT(A2:C2)-MIN(5,COUNT(A2:C2)),1,MIN(5,COUNT(A2:C2))))

Average last N values in columns in google sheets1

In this case, we utilize the MIN function to “catch” cases where there are fewer than 5 values and the real count when there are.

Related Functions

  • Google Sheets AVERAGE function
    The Google Sheets AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Google Sheets COUNT function
    The Google Sheets  COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. It returns a numeric value that indicate the number of cells that contain numbers in a range…
  • Google Sheets OFFSET function
    The Google Sheets OFFSET function returns a range reference shifted a specified number of rows and columns from a starting cell reference.The syntax of the OFFSET function is as below:= OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])…
  • Google Sheets MIN function
    The Google Sheets MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Google Sheets and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….

 

 

Calculating Average Of The Numbers in Google Sheets

Are you weary of investing a lot of time and effort in manually calculating the average of the numbers by including or excluding 0 and calculating the average of the top 3 scores? Then congratulations because you have just landed on the right article.

In this article, you will get to know the smarter ways to do these cumbersome tasks in a matter of seconds.

So without any delay, let’s dive into the article,

Average Of The Numbers By Including 0

_3 Average numbers in google sheets1

General Formula

Use the formula below to calculate the average of a set of numbers in Google Sheets:

=AVERAGE(range)

Explanations for Syntax:

  •  AVERAGE: In Google Sheets, the AVERAGE Function can calculate the arithmetic mean of a set of numbers.
  • Range: This is the input value from your worksheet.
  • Comma symbol (,): It acts as a separator that helps to separate a list of values.
  • Parenthesis (): The main Function of this symbol is to group the elements.

Explanation

Use the AVERAGE function to calculate the average of a set of numbers.

In the example, the formula in E3 is:

=AVERAGE(A3:C3)

_3 Average numbers in google sheets1

which is then copied down in the table.

AVERAGE is a built-in function in Google Sheets in which you supply a range of cells to average, and this function returns the result. In cases where cells are not adjacent, you can also provide individual arguments to the Function.

The AVERAGE function automatically ignores all the blank text and cells, but zero values are included.

Average Of The Numbers By Ignoring 0

_3 Average numbers in google sheets1

General Formula

The formula below will assist you in calculating the average of a number that excludes zero:

=AVERAGEIF(range,"<>0")

Explanations for Syntax:

  • AVERAGEIF: This Function returns the average of a set of input values that satisfy a single condition or criteria. Learn more about the AVERAGEIF Function.
  • Range: This is the input value from your worksheet.
  • Comma symbol (,): It acts as a separator that helps to separate a list of values.
  • Parenthesis (): The main Function of this symbol is to group the elements.
  • Operator (>): The supplied criteria is “>0,” which means “not equal to zero.”

Explanation

The AVERAGEIF function computes the average of a set of numbers while excluding or ignoring zero values. In the example, the formula in E9 is:

=AVERAGEIF(A9:C9,"<>0")

_3 Average numbers in google sheets1

The formula in E3 in the example is based on the AVERAGE Function:

=AVERAGE(A3:C3)

Because (129+299+0)/ 3 = 166, the answer is 166.

To remove the zero from the calculated average, the formula in E10 employs the AVERAGEIF function, as shown below:

=AVERAGEIF(A10:C10,"<>0") / returns 249

The provided criterion is “>0,” which means “not equal to zero”.

Data with no values

Because the AVERAGE, AVERAGEIF, and AVERAGEIFS functions all ignore blank cells (and cells with text values), there isn’t any need to provide criteria to filter out empty cells.

Average Of The Top 3 Numbers

General Formula

The formula below will assist you in calculating the average of the top 3 numbers:

=ArrayFormula(AVERAGE(LARGE(range,{1,2,3})))

Explanations for Syntax:

  • AVERAGE: In Google Sheets, the AVERAGE Function can calculate the arithmetic mean of a set of numbers.
  • Range: This is the input value from your worksheet.
  • Comma symbol (,): It acts as a separator that helps to separate a list of values.
  • Parenthesis (): The main Function of this symbol is to group the elements.
  • LARGE: This Function returns the Nth largest value from the given range of data. Learn more about the LARGE Function.
  • Values: This is the range of cells that contain values.

Explanation

To average the top three values in a data set, use a formula based on the LARGE and AVERAGE functions. In this example, the formula in G15 is mentioned below:

=ArrayFormula(AVERAGE(LARGE(A15:E15,{1,2,3})))

The LARGE function is used to find the top nth value in a set of numbers. For example, the LARGE(A15:E15,{1,2,3}) will return the highest value, LARGE(A15:E15,2)will return the second-highest value, and so on:

=LARGE(A15:E15,1)/First largest value
=LARGE(A15:E15,2)/ 2nd largest value
=LARGE(A15:E15,3)/ 2nd largest value

In this case, we are requesting multiple values by passing an array constant 1,2,3 into LARGE as the second argument. This causes LARGE to return an array of the top three values.

= LARGE(A15:E15,{1,2,3})

returns an array similar to this:

{299,199,54}

This array is passed directly to the AVERAGE function:

=AVERAGE(299,199,54) / returns 184

The AVERAGE Function then returns the average of these values.

Because the AVERAGE Function can handle arrays natively, there is no need to enter this formula with control + shift + enter.

 Related Functions

  • Google Sheets AVERAGE function
    The Google Sheets AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Google Sheets AVERAGEIF function
    The Google Sheets AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
  • Google Sheets LARGE function
    The Google Sheets LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…

      

 

Add Workdays in Google Sheets

To add or subtract workdays days from a date, respecting holidays but assuming a 7-day week: you can use the WORKDAY.INTL function with an argument that tells it how many days should be subtracted/added as well as which specific holidays to ignore. In our formula below C2 for this example:

=WORKDAY.INTL(A2,B2,"0000000",A7:A8)

Add workdays no weekends in google sheets1

With this formula, you can have a Thursday evening deadline instead of Monday afternoon! This means that all days in the week are treated as working hours. With two holidays  in range A7:A8  and weekends set using the special syntax “0000000“, your work will be done by 5/29/2022, at midnight PST.

Explanation

WORKDAY.INTL allows you to calculate a date in the future or past that respected holidays and weekends with its special codes for designating which days are considered as such, but using masks is more flexible since it allows any day of the week to be designated into being treated like weekend ones & zeros!

You can calculate the number of days required to complete your project in an official workplace with this handy formula. Simply multiply Saturday’s total hours by 0,8 and add it onto Sunday-Friday count!

To add days excluding weekends, simply do the following:

Type this formula =WORKDAY(A2, B2) into the blank cell, and press Enter key to get your answer.

Add workdays no weekends in google sheets1

Tip: To change the start date of your spreadsheet, simply enter “A2” as desired. This will allow you to input future dates into B2 for each day added on top!

We have now reached the end date, which adds 50 business days, excluding weekends.

Note: The number you’re trying to math is a 5-digit one, so in order for it to show up on your screen correctly formatted as date, click Format> Number > Date. See screenshot:

Add workdays no weekends in google sheets1

Now that you have cells containing all of your holiday data, excluding weekends and any other days we want is easy.

Type this formula =WORKDAY(A2, B2, A7:A8) in blank cells and hit the enter to see the result.

Add workdays no weekends in google sheets1

Tip: To create a formula that excludes holidays, use this equation. A2 is the start date, and A7:A8 are days you want to be excluded from your calculation.

Related Functions

  • Google Sheets WORKDAY function
    The Google Sheets WORKDAY function returns a serial number that represents a date that is the indicated number of working days before or after the starting date you specified.The syntax of the WORKDAY function is as below:=WORKDAY(start_date, days, [holidays])…
  • Google Sheets WORKDAY.INTL function
    The Google Sheets WORKDAY.INTL function returns the serial number of the date before or after a specified number of workdays.The syntax of the WORKDAY.INTL function is as below:= WORKDAY.INTL (start_date, days, [weekend], [holidays])…

 

Add Row Numbers And Skip Blanks in Google Sheets

Do you ever have to input a list of numbers into a spreadsheet in Google Sheets, and some of the cells are blank? It can be difficult to keep track of what number is in which cell when you have to scroll up and down the sheet to look for the right spot. Well, today, I’m going to show you how to add row numbers and skip blanks in Google Sheets so that your data entry process is a little bit smoother. Let’s get started!

Add Row Numbers And Skip Blanks in google sheets1

Generic formula in Google Sheets

=IF(ISBLANK(B2),"",COUNTA($B $2: B 2))

Summary

To add row numbers to a list of data, skipping blank cells in Google Sheets, you can use the formula based on COUNTA and ISBLANK. Just using the following formula:

=IF(ISBLANK(B2),"",COUNTA($B$2:B2))

Add Row Numbers And Skip Blanks in google sheets1

A simple way is to use IF statements with anondata formulas to show certain information when it comes up while keeping other things hidden from view unless desired by users who will open them up specifically looking for these details themselves!

Explanation 

The purpose is to add row numbers in column A only when there’s a value present elsewhere.

The IF function checks whether or not cell B2 has any values with ISBLANK.

=ISBLANK(B2) // FALSE if not, TRUE if empty

Add Row Numbers And Skip Blanks in google sheets1

When B2 is empty, ISBLANK returns TRUE, and the IF function produces a plain string like this: “”. If there’s anything in between them (i.e., not an expandable reference), we get COUNTA with whatever values are stored at each cell of that array; namely, nothing if it exists or NaN for non-numbers.

=COUNTA($B$2:B2) // expanding range

Add Row Numbers And Skip Blanks in google sheets1

As the formula is copied down, each row’s count expands to include all non-blank cells in that range. COUNTA will count both numbers and text, making it easy for you to determine who was counting what during your data entry!

Method2: Autonumber Rows If Adjacent Cell Not Blank in Google Sheets

The easiest way to auto number your column is by using this formula: =IF(B2<>””,COUNTA($B$2:B2),””) and then dragging down with the filehandle. You can also add row numbers manually if you want, but why not make things easier on yourself in Google Sheets?

Add Row Numbers And Skip Blanks in google sheets1

Note: When you enter a formula to auto number cells, make sure that B2 is populated with the relevant value.

Entering new data or deleting existing values will automatically cause the sequence number in column A to be renumbered.

Related Functions

  • Google Sheets ISBLANK function
    The Google Sheets ISBLANK function returns TRUE if the value is blank or null.The syntax of the ISBLANK function is as below:= ISBLANK (value)…
  • Google Sheets IF function
    The Google Sheets IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Google Sheets COUNTA function
    The Google Sheets COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…

 

Add Months To Date In Google Sheets

It is important to adjust time periods when performing financial modeling. In Google Sheets, you can use the MONTH function to add a specific number of months to a date. The MONTH function takes one argument: the number of months you want to add. Here will show you how to add months to date in Google Sheets and even provide some examples that might be useful!

Add Months To Date in google sheets1

Method1: Add Months to Date using EDATE Function

Generic formula:

=EDATE(Sart_date, months)

Arguments

  • Start_date: the starting date on which you want to add months
  • Months: the month that you wish to add

Return Value:

The function will return the date that is the indicated number of months after the start_date. If you enter a negative number of months, it’ll count back from the end date.

When you enter the MONTH function, Google Sheets looks at the date in the cell that you specify as the start_date argument. It then adds the number of months that you entered as the second argument to that date. If you enter a negative number for the second argument, Google Sheets subtracts that number of months from the start_date.

To add a number of months is placed at B2 and add months to the date in cell A2. The formula for this would be:

=EDATE(A1,B1)

Add Months To Date in google sheets1

Or simply enter the number you want in your formula, and it will be replaced with that value.

=EDATE(A1,10)

Add Months To Date in google sheets1

Explanation

This will add 10 months to the date in cell A1. The function will return the date that is the indicated number of months after the start_date. If you enter a negative number of months, it’ll count back from the end date.

The EDATE function is a great way to find out your next date. Simply enter any valid date and then select how many months away from now you want the new one! It’s really easy; just feed it with an amount less than or equal to the number of desired days between these two points-and voila.

Add Months To Date in google sheets1

Method2: Add Months to Date using Date Function

Generic formula:

=DATE(YEAR(date),MONTH(date))+months,DAY(date)))

Arguments

  • Date: The starting date on which you want to add months.
  • Months: Months are important when adding or subtracting a certain number of months from/to dates. They add the indicated amount, depending on whether it’s positive and negative values respectively; for example, -1 means one month less than what you want (in other words: ago).
  • YEAR(date): The year component of the date in Date.
  • MONTH(date): The month component of the date in Date.
  • DAY(date): The day component of the date in Date.

Return Value:

A date is the indicated number of months after the start_date. If you enter a negative number of months, it’ll count back from the end date.

How This Formula Work

To add months to the date in cell A1, and a number of added is placed at B1. The formula for this would be as follows:

=DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))

Add Months To Date in google sheets1

With the Year, Month, and Day functions, you can easily create your date from any combination of these three values. With the Date function, you can create dates based on any year and month of your choice.

Related Functions

  • Google Sheets DAY function
    The Google Sheets DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…
  • Google Sheets EDATE function
    The Google Sheets EDATE function returns the serial number that represents the date that is a specified number of months before or after a specified date.The syntax of the EDATE function is as below:=EDATE (start_date, months)…
  • Google Sheets DATE function
    The Google Sheets DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
  • Google Sheets MONTH Function
    The Google Sheets MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Google Sheets YEAR function
    The Google Sheets YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…

 

Add Minutes to Time in Google Sheets

Adding whole hours or decimal minutes to a given time in google sheets is a fairly simple process. You can do this by using the Start_time+TIME(0,minutes,0) and Start_time+minutes/1440 functions. In this add minutes to time guide, we will show you how to do this.

Add Minutes to Time in google sheets1

Adding whole Minutes

If you want to add whole minutes to a given time, you can use the following formula:

=Start_time+TIME(0, minutes,0)
  • Start_time: the time that you want to add minutes to
  • Minutes: the minutes that you wish to add

This function returns a time formatted string.

Cell C2 shows the number of minutes you want to add, while Cell D2 displays a formula that will calculate how many more cells need changing.

=B2+TIME(0,C2,0)

Or

=B4+C4/1440

To see the result, press Enter key.

Add Minutes to Time in google sheets1

Explanation

The TIME function is a great way to convert decimal numbers into an appropriate time format. Here, we change the number of minutes from 50 (which converts it into PM) and then add:00 before adding another set amount for minutes-in, in this case, 0 because there aren’t any other digits available on our keyboard!

Note: To subtract the minutes from a time period, you need to use this formula.

= MOD(Start_time-TIME(0, minutes,0),1)

For Instance, Cell B2 shows time, and cell C2 shows the number of minutes you want to subtract. If you want to subtract a number of minutes, use the below formula:

=MOD(B2-TIME(0,C2,0),1)

To see the result, hit Enter key.

Add Minutes to Time in google sheets1

The MOD (modulus) function is an important tool for financial professionals. It can be used to flip negative numbers into positive ones yield accurate results in most cases!

Add decimal Minutes to time

 Generic formula:

=Start_time+ minutes /1440
  • Start_time: the time that you wish to add minutes to.
  • minutes: the minutes that you wish to add to start_time.

The value should be returned in time format.

For instance, cell B2 contains the time value, and cell C2 displays the number of minutes you want to add. Please see the below formula for this purpose.

=B2+C2/1440

To see the result hit Enter key.

Add Minutes to Time in google sheets1

24 hours in a day, and one minutes is 1/1440th of the whole duration.

Note: Subtracting the minutes from time is easy with this formula.

=MOD(Start_time- minutes /1440,1)

The number of minutes you want to subtract is displayed in cell C2, and time is displayed in B2. To find the correct formula, please use this simple equation:

=MOD(B2-C2/1440,1)

To see the result hit Enter key.

Add Minutes to Time in google sheets1

When google sheets detects negative values in a cell, it converts them to positive with the MOD function. This means you can just flip your negatives around and get an accurate time reflecting how long something takes!

Related Functions

  • Google Sheets TIME function
    The Google Sheets TIME function returns a date in serial number format for a particular time (the hour, minute and second).The syntax of the TIME function is as below:= TIME(hour, minute, second)…
  • Google Sheets MOD function
    the Google Sheets MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….

Add Hours to Time in Google Sheets

Adding whole hours or decimal hours to a given time in Google Sheets is a fairly simple process. You can do this by using the Start_time+TIME(hours,0,0) and Start_time+hours/24 functions. In this add hours to time guide, we will show you how to do this.

Add hours to time in google sheets1

Adding whole hours

If you want to add whole hours to a given time, you can use the following formula:

=Start_time+TIME(hours,0,0)
  • Start_time: the time that you want to add hours to
  • Hours: the hours that you wish to add

This function returns a time formatted string.

Cell C2 shows the number of hours you want to add, while Cell D2 displays a formula that will calculate how many more cells need changing.

=B2+TIME(C2,0,0)

Or

=B4+C4/24

To see the result, press Enter key.

Add hours to time in google sheets1

Explanation

The TIME function is a great way to convert decimal numbers into an appropriate time format. Here, we change the number of hours from 5 (which converts it into PM) and then add:00 before adding another set amount for minutes-in, in this case, 0 because there aren’t any other digits available on our keyboard!

Note: To subtract the hours from a time period, you need to use this formula.

= MOD(Start_time-TIME(hours,0,0),1)

For Instance, Cell B2 shows time, and cell C2 shows the number of houses you want to subtract. If you want to subtract a number of hours, use the below formula:

=MOD(B2-TIME(C2,0,0),1)

To see the result, hit Enter key.

Add hours to time in google sheets1

The MOD (modulus) function is an important tool for financial professionals. It can be used to flip negative numbers into positive ones yield accurate results in most cases!

Add decimal hours to time

 Generic formula:

=Start_time+hours/24
  • Start_time: the time that you wish to add hours to.
  • Hours: the hours that you wish to add to start_time.

The value should be returned in time format.

For instance, cell B2 contains the time value, and cell C9 displays the number of hours you want to add. Please see the below formula for this purpose.

=B2+C2/24

To see the result hit Enter key.

Add hours to time in google sheets1

24 hours in a day, and one hour is 1/24th of the whole duration.

Note: Subtracting the hours from time is easy with this formula.

=MOD(Start_time-hours/24,1)

The number of hours you want to subtract is displayed in cell C2, and time is displayed in B2. To find the correct formula, please use this simple equation:

=MOD(B2-C2/24,1)

To see the result hit Enter key.

Add hours to time in google sheets1

When google sheets detects negative values in a cell, it converts them to positive with the MOD function. This means you can just flip your negatives around and get an accurate time reflecting how long something takes!

Note:

  1. The number of hours when using the Start_time + hours / 24 formula can be a decimal number. See screenshot:

Add hours to time in google sheets1

2. If the result is over 24 hours, then under the Formate cell’s Number tab, go to the Custom section and format the result as [h]:mm:ss.

Add hours to time in google sheets1

Related Functions

  • Google Sheets TIME function
    The Google Sheets TIME function returns a date in serial number format for a particular time (the hour, minute and second).The syntax of the TIME function is as below:= TIME(hour, minute, second)…
  • Google Sheets MOD function
    the Google Sheets MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….

 

Average Last N Values in Google Sheets

Average is a valuable function in google sheets that allows you to rapidly compute the average of the past N values in a column. However, you may need to insert new numbers beneath your original information from time to time, and you just want the average result to be updated automatically when new data is entered. This means you would like the average to always represent the latest N values in your data list, regardless of whether or not you add new data points.

Average last 5 values in google sheets1

The Generic formula is as follow:

=AVERAGE(OFFSET(C2,COUNT(C:C)-N_Values,0,N_Values))

Summary 

The AVERAGE function, in conjunction with the COUNT and OFFSET functions, may be used to calculate an average of the latest 5 data points. You may use this strategy to average the most recent N data points, such as the most recent 3 days, the most recent 6 measures, and so on. The following is the formula in F6 in the case shown:

=AVERAGE(OFFSET(C2,COUNT(C:C)-5,0,5))

Average last 5 values in google sheets1

Let’s See How This Formula Works

This function may be used to create dynamic rectangular ranges using a beginning reference and the values of the rows, columns, height, and width parameters. The rows and columns parameters are treated as “offsets” from the initial reference in the same manner. The number of rows and columns in the final range is determined by the height and width parameters (both of which are optional). For the sake of this example, OFFSET is set as follows:

  • reference = C2
  • rows = COUNT(C:C)
  • cols = 0
  • height = -5
  • width = (not provided)

The beginning reference is supplied as C2, which is the cell immediately above the actual data in the table. Because we want OFFSET to provide a range of values starting with the last item in column C. We use the COUNT function to count all of the values in column C to get the needed row offset for OFFSET. COUNT solely counts numeric numbers, which means that the heading in row 1 is discarded automatically.

OFFSET resolves to the following when there are 10 numerical values in column C:

=OFFSET(C2,COUNT(C:C)-5,0,5)

Average last 5 values in google sheets1

This combination of parameters begins at C2, offsets 11 rows to C12, and then uses -5 to expand the rectangular range up “backwards” 5 rows to form the range C8:C12, which is the range C2:C12.

At the end of the process, the OFFSET function passes the range C8:C12 to the AVERAGE function, which computes the average values in the range.

With a dynamic range

For the sake of simplicity, the example in this section utilises a complete column reference for the data that is being averaged – when new information is added to column C. The formula will continue to function correctly since reference C includes the whole column C. However, you may also utilise a dynamic range in the following manner:

 =AVERAGE(OFFSET(C2,COUNT(DATA_Range)-5,0,5))

Average last 5 values in google sheets1

Where ” DATA_Range ” refers to a dynamic named range or a reference to a column in an google sheets Table, respectively. google sheets Tables and dynamic named ranges can dynamically expand as new data is entered, which is crucial for using this method. Take note that the reference to OFFSET is still in the row before the data appears.

Related Functions

  • Google Sheets AVERAGE function
    The Google Sheets AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Google Sheets COUNT function
    The Google Sheets  COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. It returns a numeric value that indicate the number of cells that contain numbers in a range…
  • Google Sheets OFFSET function
    The Google Sheets OFFSET function returns a range reference shifted a specified number of rows and columns from a starting cell reference.The syntax of the OFFSET function is as below:= OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])…

Assigning Points based on Late Time in Google Sheets

It is shown in this lesson how to utilize the IF function in Excel to allocate points based on the amount of time that has passed.

assign points based on late time in google sheets1

If you intend to pursue along this guide, you may do so by downloading the template spreadsheet.

Generic formula:

=IF(C2<VALUE("0:2"),0,IF(C2<VALUE("0:35"),1,IF(C2<VALUE("0:45"),2,IF(C2<VALUE("1:00"),3,IF(C2<VALUE("3:00"),4,5)))))

assign points based on late time in google sheets1

Return value

This formula provides the number of points that have been assigned depending on the late time.

How this formula work

For example, below is a list of point regulations and time records; kindly use the formula below to find the appropriate rule or record.

=IF(C2<VALUE("0:2"),0,IF(C2<VALUE("0:35"),1,IF(C2<VALUE("0:45"),2,IF(C2<VALUE("1:00"),3,IF(C2<VALUE("3:00"),4,5)))))

A pop-up with further information will appear if you press the Enter key.

To use this formula, just drag the fill handles over the cells.

assign points based on late time in google sheets1

IF function is being used to evaluate for defined criteria and then returns the results that match those circumstances. Explain the formula in five parts, as follows:

IF (late_by_time<VALUE ("0:2"),0, It returns 0 if the late time is much less than 2 minutes.

assign points based on late time in google sheets1

IF (late_by_time <VALUE ("0:35"),1, The function returns 1 if the late time is much less than 35 minutes but higher than or equivalent to 2 minutes.

assign points based on late time in google sheets1

IF (late_by_time <VALUE ("0:45"),2, If the late time is even less than 45 minutes but higher than or equivalent to 35 mins, it returns 2.

assign points based on late time in google sheets1

IF (late_by_time <VALUE ("1:00"),3, If the late time is even less than 60 minutes but higher than or equivalent to 45 mins, it returns 3.

assign points based on late time in google sheets1

IF (late_by_time <VALUE ("3:00"),4, The result is 4 if the late time is less than 3 hours but larger than or equivalent to 60 minutes.

assign points based on late time in google sheets1

5))))) This function returns five if the late time is more than or equal to three hours.

When converting text to numbers, the value function is employed. It does this by converting the text time to a number.

Related Functions

  • Google Sheets IF function
    The Google Sheets IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Google Sheets VALUE function
    The google sheets VALUE function converts a text value that represents a number to a numeric value.The syntax of the VALUE function is as below:= VALUE  (text)…

 

Google Sheets Nexted IF Functions (Statements) Tutorial (15 IF Formulas examples)

This tutorial will guide you how to use nested Google Sheets IF function  (include multiple If statements in Google Sheets formula) with syntax and provide about 15 nested IF formula examples with the detailed explanation in Google Spreadsheets.

Description

The Google Sheets IF function perform a logical test to return one value if the condition statement is TRUE and return another value if the condition statement is FALSE. The IF function is a build-in function in Google Spreadsheets and it is categorized as a Logical Function.

The Google Sheets if function just only test one condition and if you want to deal with more than one condition and return different actions depending on the result of the tests,  then you need to include several IF statements (functions) in one Google Sheets IF formula, these multiple IF statements are also called Google Sheets Nested IF formula(Nested IFs). It’s also similar with IF-THEN-ELSE statement.

The nested IF function is formed by multiple if statements within one Google Sheets if function. This Google Sheets nested if statement makes it possible for a single formula to take multiple actions.

Syntax

The syntax of Nested IF function is as below:

=IF(Condition_1,Value_if_True_1,IF(Condition_2,Value_if_True_2,Value_if_False_2))

Where the Nested IF function argument is:

  • Condition_1 –  The condition that you want to test  in the first IF statement.
  • Value_if_True_1The value that is returned if first IF statement is True. If the condition_1 return False, then move into the next IF function.
  • Condition_2The condition that you want to test in the second IF statement.
  • Value_if_True_2The value that is returned if second IF statement is True.
  • Value_if_False_2The value is returned if second IF statement is False.

This is equivalent to the following IF THEN ELSE statement:

IF Condition_1 THEN
    Value_if_True_1
ELSEIF Condition_2
    Value_if_True_2
ELSE
    Value_if_False_2
END IF

Examples of Nested IF function (Statement) in Google Sheets

The below examples will show you how to use Google Sheets Nested IF function with the detailed explanation of their syntax and logic.

Example 1#  The most basic Nested IF function with one level of nesting

If you want to write a nested if function to test the following calculation logic for assigning value in the cell A1.

IF A1 =="excelhow" THEN
    return "Google Sheets "
ELSEIF A1 == "excelhow.net" THEN
    return "Google Spreadsheets "
ELSE
    return "GS"END
 IF

we can write a nested IF function based on the above logic as follows:

=IF(A1="excelhow", "Google Sheets", IF(A1="excelhow.net"," Google Spreadsheets","GS"))

google sheets nested if function1

In above Nested IF formula, the nested if function is is inside the outer IF function. we can see that if A1 is not equal to the “excelhow“, then the second nested  IF function will be test. and if second IF condition statement return FALSE, then the entire IF function will return “GS” value.

Example 2#  The Nested IF function with two levels of nesting

Assuming that you want to test more than one condition statement in the above nested if function, add one condition to test if the value of the cell A1 reference is equal to “www.excelhow.net” , If TRUE, then return “google spreadsheets “.

The calculation Logic is as below:

IF A1 =="excelhow" THEN
    return "google"
ELSEIF A1 == "excelhow.com" THEN
    return "google sheets"
ELSEIF A1 == "www.excelhow.net" THEN
    return "google spreadsheets"
ELSE    return "gs"
END IF

we can add one more IF statement inside the second IF function in the above google sheets nested if formula in example1. let’s see the below nested if function with tow level nesting:

=IF(A1="excelhow", "google", IF(A1="excelhow.com"," google sheets",IF(A1="www.excelhow.net","google spreadsheets","gs")))

google sheets nested if function1

In the above nested google sheets IF formula, the first nested if function is marked with red color, and the second nested google sheets if function is marked with blue color.

If the both first and second conditions are False and the third IF condition will be check, IF A1 is equal to “www.excelhow.net” , then return “google spreadsheets “, or the entire nested IF formula will return “gs“.

Example 3#  Describes the each IF function contained in the nested IF function

​We will use one typical example of google sheets nested if function to describe each IF function included in the nested if function.

Assuming that you need to assign a grade based on a score with the following test conditions:

Score Grade
80-100 excellent
60-79 good
0-59 bad

Let’s write a nested if function based on the above logic as follows:

=IF(A1>=80, "excellent", IF(A1>=60, "good", IF(A1>0, "bad", "no valid score")))

google sheets nested if function1

For the above google sheets if formula, lets describe it for each IF function statement.

1# IF Cell A1 is greater than or equal to 80, then the formula will return “excellent” or move to the second If function.

2# If Cell A1 is greater than or equal to 60, then the formula will return  “good” or move to the third IF function

3# IF Cell A1 is greater than 0, then the formula will return “bad”, or the IF function will return “no valid score”.

Example 4#  Describes each If function in the google sheets Nested IF Statement (another simple example of if function)

Let’s describe the below Nested IF Function example:

=IF(A1<=6,60, IF(A1<=8,80,IF(A1<=10,100,200)))

a) If cell A1 is equal to 6 or less than 6, then return value 60 in cell C1. Let’s see below screenshot.

google sheets nested if function1

b) If Cell A1 is greater than 6 and less or equal to 8, then retrun value 80 in Cell C1.

google sheets nested if function1

c) If cell A1 is greater than 8 and less than or equal to 10, then return value 100 in cell C1.

google sheets nested if function1

d) If cell A1 is greater than 10 , then the Nested if function will return the last value “200”in cell C1.

google sheets nested if function1

Example 5#  Google Sheets Nested IF function with arithmetic operator (+, -, * , /)

Assuming that you want to write a Nested If function to reflect the following logic tasks:

a) IF Cell A1 is less than 10, then multiply by 10.

b) IF Cell A1 is greater than or equal to 10 but less than 20, then add 20

c) IF Cell A1 is greater than or equal to 20 but less than 30, then minus 20

d) IF Cell A1 is greater than or equal to 30 but less than 50, then divided by 20

The nested IF formula is as follows:

=IF(A1<10,A1*10,IF(A1<20,A1+20,IF(A1<30,A1-20,IF(A1<50,A1/20))))

google sheets nested if function1

a1) if Cell A1 is less 10 (A1=5), then the first If condition matched and will take multiply action, A1 * 10=5*10=50, so it will return 50 in the cell C1

b1) if Cell 10<=A1<20 (A1=15), then the second if condition matched and will take add action, A1+20=15+20=35, so it will return 35 in the cell C1.

c1) if Cell 20<=A1<30(A1=25), then the third if condition matched and will take minus action, A1-20=25-20=5, so it will return 5 in the cell C1.

d1) if Cell 30<=A1<50 (A1=35), then the forth if condition matched and will take divide action, A1/20=35/20=1.75, so it will return 1.75 in the cell C1.

Example 6#  Google Sheets Nested IF function with logical function –AND

Assuming that you need a nested if function to reflect the following logic:

a) IF A1+B1 is less than 10, then return 10

b) IF A1+B1 is greater than 10 but less than or equal to 20, then return 20

c) IF A1+B1 is greater than 20 but less than or equal to 30, then return 30.

d) IF A1+B1 is greater than 30, then return 200.

Let’s write the following nested IF formula in the cell C1:

=IF(A1+B1<10,10, IF(A1+B1<=20,20, IF(A1+B1<=30,30,200)))

google sheets nested if function1

The above formula just use basic nested IF function syntax, we also can use logic function to re-write it, the nested if formula with AND function is as follows:

=IF((A1+B1)<10,10,IF(AND((A1+B1)>10,(A1+B1)<=20),20, IF(AND((A1+B1)>20,(A1+B1)<=30),30,200)))

google sheets nested if function1

The above nested IF formula combined with two AND function.

In the second IF Statement, AND((A1+B1)>10,(A1+B1)<=20) will check if 10<A1+B1<=20, If TRUE, then the formula will return 20.

In the third IF Statement, AND((A1+B1)>20,(A1+B1)<=30) will check if 20<A1+B1<=30, If TRUE, then the formula will return 30.

Example 7# Google Sheets Nested IF function with logical function –OR

Assuming that you need a nested if function to reflect the following logic:

a) IF Cell A1=5, return A1/B1

b) IF Cell A1=10, return A2/B2

c) IF Cell A1=15 or A1=20, return A3/B3

In Cell C1, we can write the below nested if formula based on the above conditions.

=IF(A1=5,A1/B1,IF(A1=10,A2/B2,IF(OR(A1=15,A1=20),A3/B3)))

google sheets nested if function1

One OR function be used in the above excel nested if function, it will check if A1=15 or A1=20, if TRUE, then return A3/B3.

Example 8#  Google Sheets nested if function with text and logical function AND

Wrote a nested if function with text to reflect the following logic:

a) If Cell A1=”E” and Cell B1=5, then return “Excel”

b) If Cell A1=”P” and Cell B1=3, then return “PPT”

c) If Cell A1=”W” and Cell B1=4, then return “Word”

d) Else return “Access”

In Cell C1, try to enter into the following excel nested If formula with AND function:

=IF(AND(A1="E",B1=5),"Excel", IF(AND(A1="P",B1=3),"PPT", IF(AND(A1="W",B1=4),"Word","Access")))

google sheets nested if function1

Example 9#  Google Sheets nested if function with ISBLANK function and logical function AND

a) If you want to wrote a nested if function with ISBLANK function and logical function AND to reflect the following logic:

b) If both Cell A1 and Cell B1 are empty, then return “”

c) If only Cell A1 is empty, then return B1-today()

d) If both two cells A1 and B1 are not empty, then return “excel” string.

In Cell C1, use the following excel nested If formula with ISBLANK and AND function:

=IF(AND(ISBLANK(A1),ISBLANK(B1)),"",IF(ISBLANK(A1),B1-TODAY(),"excel"))

google sheets nested if function1

Example 10#  Using nested IF functions to check grade level based on student’s score(multiple IF statements)

The logic is as below:

Scores Grade
<50 Fail
51 to 60 Grade C
61 to 80 Grade B
81 to 95 Grade A
96 to 100 Grade A+

We will write a nested If function that reflect the above logic, and will check if the score is below 50, If TRUE, it is considered as “Fail”. If FALSE, move into the next IF statement to test if the score is between 51 and 60 and it is considered as “Grade C”. If False, we will move into another IF statement to check if the score is between 61 and 80, IF True and it is considered as “Grade B”. If FASLSE, just check the rest conditions.

We can use a nested if formula as follows:

=IF(B2<=50,"Fail",IF(AND(B2>=51, B2<=60), "Grade C", IF(AND(B2>=61, B2<=80),"Grade B",IF(AND(B2>=81,B2<=95),"Grade A", "Grade A+"))))

google sheets nested if function1

Example 11#  Nested IF function for checking two Empty Cells

Let’s see the below image a product table of a company (need to create a Google sheets table firstly):

a) If we need to check both “Price” cell and “Quantity” cell are empty, If True, then return empty. If the only “price” cell is empty only, IF True, return empty.

b) If the only “Quantity” cell is empty, IF True, return empty.

c) If both “price” and “Quantity” are not empty, then return multiply Price * Quantity as subtotal value.

So To check both “Price” and Quantity cells, we can use table header name as condition variable to test each Price cells or Quantity cells, so we can write the nested if formula as follows:

=IF([Price]="","",IF([Quantity]="","",[Price]*[Quantity]))

Just using the above Google sheets if formula in the subtotal cells, the formula will check the first IF statement if Price Cell is empty, IF TRUE, then will return empty (“”) in the subtotal cell. IF FALSE, then move to the next IF statement and so on. Last, IF neither cell is empty, then will return the value of multiply [Price]*[Quantity] in subtotal cell.

Of course, we can use another nested if function to achieve the above calculation logic (easy to understand).

=IF(ISBLANK(B2),"",IF(ISBLANK(C2),"", B2*C2))

OR

=IF(B2="","",IF(C2="","", B2*C2))

Nested IF Functions Order

There is one important thing that need us keep in mind when write Google sheets Nested IF Function, it is the order of nested IF function. It can nested up to 64 If statements, and how to order multiple IF condition statements, it is key point. Or the wrong result will be returned. The point is that Google sheets nested if function will test the first if condition in the order, once any condition is met, and the subsequent if conditions will not be checked.

So let’s remember the below rules while writing Google sheets nested if function:

  • The most important condition First or Harder Test First

Let’s see the below example what it means:

Example 12#

There are two test conditions in the following Google sheets nested if function:

=IF(B1>=90,"excellent",IF(B1>=60,"good","bad"))

google sheets nested if function1

When using this formula in the cell B3, If the amount in cell B1 is 95, then “excellent” would be returned. because it is greater than 90. And the second IF condition will not be evaluated.

However, if the order of nested if statment are reversed as follows:

=IF(B1>=60,”good”,IF(B1>=90,”excellent”,”bad”))

google sheets nested if function1

The above formula would check for the condition B1>=60 first, if the amount in cell B1 is 95,  then the value “good” would be returned in cell B3. Because the Cell B1 match the first test condition, and it will not check the second if condition and will return the incorrect result.

Nested IF Function Alternatives

To make your google sheets formulas more efficiency and fast, you can try to use the following alternatives to google sheets nested if function.


1)
google sheets nested if function can be easily replaced with the VLOOUP, Lookup, INDEX/MATCH or CHOOSE functions.

Example 13#  Use VLookup function instead of nested IF function

Nested IF function:

=IF(D1=39,"excelhow.net", IF(D1=45,"two",IF(D1=34,"google.com","")))

google sheets nested if function1

Vlookup function:

=VLOOKUP(39,A1:B3,2,FALSE)

google sheets nested if function1

Example 14#  Use CHOOSE function instead of nested if function

Nested If unction:

=IF(B1>=60,"good","bad")

google sheets nested if function1

Using CHOOSE function as follows:

=CHOOSE(IF(B1<60,1,2),"bad","good")

google sheets nested if function1

2) Use IFS instead of nested if function

3) Use the CONCATENATE function or the concatenate operator (&).

Example 15#

Nested IF function:

=IF(B1=”A”,”excellent”,IF(B1=”B”,”good”,”bad”))

google sheets nested if function1

Questions & Asked

Question1: Is there any tool to help write Google Sheets formulas and nested Ifs?

This is a Google Sheets formula with nested IF statements:

=IF((B2="East"),4,IF((B2="West"),3,IF((B2="North"),2,IF((B2="South"),1,""))))

To essentially accomplish this:

If cell B2 = "East"
   return "4"
ElseIf cell B2 = "West"
   return "3"
ElseIf cell B2 = "North"
   return "2"
ElseIf cell B2 = "South"
   return "1"
Else
   return ""

Can Google Sheets formulas be written in such a “more readable” manner and converted to the official syntax? Is there any tool to help write Google Sheets formulas?

Answer:  Google Sheets Formula Formatter add-in by Rob van Gelder, mentioned at Daily Dose of Google Sheets.

Google Sheets formula bar ignores line feeds and white space, so you can Alt+Enter and spacebar to format the formulas however you like. I’ve tried it and I quickly stopped doing it. Too much spacebar-ing, especially if you need to edit.

Question2: I am working on a Google Sheets file, and i am trying to use a nested if formula to achieve what i would like.

i have two columns:A B.  And the condition is this: if the value in a2=a3, then check if the minus of b2 and b3 is certain value, and if it is, put a yes, else put a no. this will iterate till the end of the Google Sheets file.

so far here is what i have. not sure how to use the Google Sheets formulas. any help is much appreciated.

if(a2=a3,b2-b3=5 or b2-b3=-5 or b2-b3=20 or b2-b3=-20, "yes", "no")

Answer: you should be able to use the OR function within your nested if formula to test for “B2-B3=5,B2-B3=-5,B2-B3=20,B2-B3=-20” as follows:

=IF(A2=A3,IF(OR(B2-B3=5,B2-B3=-5,B2-B3=20,B2-B3=-20),"yes","no"),"no")

Related Functions

  • Google Sheets ISBLANK function
    The Google Sheets ISBLANK function returns TRUE if the value is blank or null.The syntax of the ISBLANK function is as below:= ISBLANK (value)…
  • Google Sheets COUNTIF function
    The Google Sheets CHOOSE function returns a value from a list of values based on index. The syntax of the CHOOSE function is as below:=CHOOSE (index_num, value1,[value2],…) …
  • Google Sheets VLOOKUP Function
    The Google Sheets VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position..The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])…
  • Google Sheets IF function
    The Google Sheets IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Google Sheets Choose function
    The Google Sheets CHOOSE function returns a value from a list of values based on index.The syntax of the CHOOSE function is as below:=CHOOSE (index_num, value1,[value2],…)….

Calculate Interest Rate in Google Sheets

Financial choices play a critical role in the development and execution of corporate strategies and plans. In daily life, we also face a slew of financial choices. For instance, suppose you’re applying for a loan to purchase a new automobile. It will undoubtedly be beneficial to discover the actual interest rate you will be required to pay your bank. Google Sheets has the RATE function for such circumstances, which is specifically intended to calculate the interest rate for a certain term.

Google Sheets Rate Function

RATE is an Google Sheets financial function that calculates the interest rate on an annuity over a specified time. The function calculates iteratively and may return zero or more solutions.

The following is the syntax:

=RATE(number_of_periods, payment_per_period, present_value, [future_value], [end_or_beginning], [rate_guess])

Where:

  • number_of_periods (needed) – the total number of payment periods, which may include years, months, and quarters.
  • payment_per_period (mandatory) – the set monthly payment amount that cannot be adjusted throughout the annuity’s life. It typically includes principle and interest but excludes taxes.
  • present_value (needed) – the present value of the loan or investment, i.e. its current worth.
  • future_value (optional) – the future value, that is, the cash balance you desire to have after the previous payment. If omitted, the default value is 0.
  • end_or_beginning (optional) – specifies the manner in which payments are made: 0 or omitted (default) – payment is due at the end of the term
  • guess (optional) – your best guess for the rate. If left blank, it defaults to 10%.

Note:

  • The RATE function performs trial and error calculations. If it does not reach a solution after 20 iterations, it returns a #NUM! error.
  • By default, interest is computed on a per-payment basis. However, as seen in this example, you can get an annuity solve for interest rate by multiplying.
  • While the RATE syntax specifies present_value as a mandatory parameter, it may actually be removed if the future_value argument is included. Typically, this syntax is used to calculate the interest rate on a savings account.
  • In most circumstances, the guess parameter may be deleted since it serves just as a beginning value for an iterative method.
  • When computing RATE for various periods, ensure that the values for number_of_periods and guess are constant. For instance, if you’re making yearly payments on a two-year loan with an annual interest rate of 4%, use 2 for number_of_periods and 4% for estimate.
  • If you’re making monthly payments on the same loan, use 2*12 for the number_of_periods  and 4% /12 for the estimate.

RATE formula Examples

In this example, we’ll look at the easiest way to create a RATE formula in Google Sheets for the purpose of calculating interest rates.

Assume you borrowed $20,000 and are required to repay it in full over the following three years. You intend to pay three annual payments of $7200 each. How much will interest cost on a yearly basis?

calculate interest rate1

Please note that the yearly payment (pmt) is specified as a negative figure since it is an outgoing cash payment.

Assuming that the payment will be paid at the end of each year, we may omit or change the parameter to the default value. Also deleted are the two optional parameters and guess.

As a consequence, we get the following straightforward formula:

=RATE(B1,B2,B3)

calculate interest rate1

If the payment must be recorded as a positive integer, include the negative sign exactly before the pmt parameter in the formula:

=RATE(B1,-B2,B3)

calculate interest rate1

Now that you’re familiar with the fundamentals of utilizing RATE in Google Sheets, let’s look at a few particular use scenarios.

Determine a Loan’s Monthly Interest Rate

Given that the majority of installment loans are repaid monthly, it may be advantageous to know the monthly interest rate, correct? This is accomplished by providing an adequate number of payment periods to the RATE function.

Assume the loan is to be repaid in monthly installments over a two-year period. We multiply two years by twelve months to get the total amount of payments (2*12=24).

calculate interest rate1

The remaining parameters are listed below:

  • number_of_periods (number of periods) in A1: 24
  • payment_per_period (monthly payment) in A2: -1000
  • present_value (load amount) in A3: 20000

Assuming the payment is due at the end of each month, the monthly interest rate may be calculated using the well-known formula:

=RATE(B1,B2,B3)

calculate interest rate1

Calculate the interest rate on a monthly basis in Google Sheets

If your source data contains the number of years required to repay the loan, you may do the multiplication inside the number_of_periods argument:

=RATE(B1*12,B2,B3)

calculate interest rate1

Calculate annuity for Interest rate on Monthly Payments

Continuing our example, how do you calculate the yearly interest rate on monthly payments? Simply multiply the RATE value by the number of periods in a year, which in our instance is twelve:

=RATE(B1,B2,B3)*12

calculate interest rate1

Calculate Quarterly Interest Rate

Assume the loan is to be repaid in quarterly installments over a two-year period. We multiply two years by 4 quarters to get the total amount of payments (2*4=8).

To begin, the entire number of periods is converted to quarterly:

number_of_periods: 2 (years) multiplied by 4 (quarters per year) equals 8.

Then, compute the quarterly interest rate in cell B4 using the RATE function:

=RATE(B1,B2,B3)

calculate interest rate1

If you want to get the yearly interest rate based on quarterly interest rate, you still need to multiply the quarterly interest rate by four, using the following formula:

=RATE(B1,B2,B3)*4

calculate interest rate1

Related Functions

  • Google Sheets RATE function
    The google sheets RATE function returns the interest rate per payment period of an annuity.The syntax of the RATE function is as below:= RATE(number_of_periods, payment_per_period, present_value, [future_value, end_or_beginning, rate_guess])….