6 Best Free Wedding Budget Templates

This post will show you some free wedding budget spreadsheet templates for both google sheets and Microsoft Excel Spreadsheet, as well as some explanations of the features or important function points of these templates. You can then easily make modifications when using these templates in order to achieve your special requirements.

Why the Wedding Budget Spreadsheet Is Important?

A wedding budget spreadsheet can tell you what things to spend money on, however, later in the process of spending money, you will find many things over budget and some things less than budget, these are actually not very important, what is important is that once you have this budget spreadsheet, then at least your shopping list is clear at a glance and many things can be planned very well.

Wedding Budget Breakdown

When creating your wedding budget spreadsheet, you can also consult with your wedding planner in advance so that you can get a breakdown of the main costs of your wedding in advance, such as wedding photos, wedding party costs, bride and groom gifts and accessories, photography, flowers, honeymoon costs, etc.

The wedding budget spreadsheet will cover everything from the wedding ceremony, wedding venue, food, drinks, dress, cake, makeup artist, photographer and wedding insurance.

Every couple wants their wedding venue to be organized and able to stay on track with all the ceremonies, so it is essential to create a wedding budget spreadsheet through google sheets or Microsoft Excel Spreadsheet.

Create A Personalized Wedding Budget Template

Below you will find some free and simple wedding budget templates in google sheets or Microsoft Excel. You can use these free templates to customize your wedding budget spreadsheet and achieve your personal wedding planning goals.

1. Bridal Musings Wedding Budget Spreadsheet

free wedding budget spreadsheet teamplate1-1 This template needs to be opened via google sheet by default, if you need to use this template via Microsoft Excel spreadsheet, then you can download this wedding budget template spreadsheet for free by clicking on the Dropbox address here.

This template contains a variety of wedding essentials or some wedding services that you may need to purchase before and after the Wedding Day, and provides a record of the estimated and actual cost of these wedding essentials, and also automatically calculates the difference between the before and after pricing, so that you can easily calculate the amount over or under the budgeted price.

The template also provides a Where to Buy & Resources column to record the address of the online store where the current wedding item or service was purchased, allowing you to easily access the corresponding website to get instructions or customer reviews for the item or get some support from the online store’s customer service.

This template contains 3 simple Google sheets or Microsoft Excel formulas to automatically calculate the estimated cost, actual cost, over or savings of all wedding items or services purchased.

The three formulas are as follows:

=SUM(B7:B119)

=SUM(C7:C119)

=B120-C120

free wedding budget spreadsheet teamplate1-1

The first formula calculates the total estimated amount for all items or services from B7 to B119 by using the SUM function, and the second formula automatically calculates the total actual amount spent for all items or services from C7 to C119 by using the SUM function. The third formula calculates overestimated or saved amount by subtracting the total estimated amount from the total spent amount.

As you can see from the image above, the current wedding cost is $935 and the initial budget was $733, so the actual cost was $201 more than the initial budget.

If you have any other wedding cost items that you need to add to this template, then you can find the corresponding category and just insert a new line afterwards.

If you have any other personalization requirements, you can leave us a comment and we will assist you as well.

Click here to use this template.

2. Wedding Budget Templates By APW

free wedding budget template2-1 This template needs to be opened through google sheets by default, if you need to use this template through Microsoft Excel Spreadsheet, then you need to download this template in xls format through google sheets and then use this free wedding budget template through Excel Spreadsheet.

This template is relatively simple and does not use any google sheets or Excel formulas, so it does not provide any complicated summation and statistical functions.

The following are the distinct features of this template:

  • Provides some very useful statistics, such as industry averages for various wedding expense items, budgeted Estimate, Total Actual Cost, Deposit Amount Paid, Balance Due, Final Payment Due Date, etc. This allows you to easily track the current spending on wedding items.
  • The header row of the template is set to freeze so that when you have a lot of wedding items or services, the header row will always be displayed and you can easily record the value of the corresponding column.free wedding budget template2-1

This template can add some statistics through SUM function, for example, you can do summary statistics for all wedding items or services under a particular sub-category, such as total sub-category budget, total sub-category cost, total sub-category paid, etc.

free wedding budget template2-1

Of course, you can also do summary statistics for all wedding items or services. If you have such customized needs, you can send us a message and we will assist you with these complex statistical functions.

Click here to use this template.

3. Wedding Budget Templates By Microsoft Office

free wedding budget templates3-1 This template is a free wedding budget spreadsheet provided by Microsoft, if you want to use this template in google sheets, then you can import it into google sheets.

The template provides a beautiful background image and the overall color of the spreadsheet is very nice; the template only provides the two most common statistical fields: Estimated and Actual, so the template is very simple and easy to use.

The top row of the template provides statistics for the total wedding budget and the actual cost, which are summarized for all wedding items and wedding services through the SUM function.

free wedding budget templates3-1

This template provides total budget and total cost statistics for different wedding item categories by using the SUBTOTAL function. As the following figure shows.

free wedding budget templates3-1

Click here to use this template.

4. Wedding Budget Templates By Vertex

free wedding budget template4-1

This template needs to be opened through google sheets by default, if you need to use this template through Microsoft Excel Spreadsheet, then you need to download this template in xls format through google sheets and then use this free wedding budget template through Excel Spreadsheet.

This template is basically similar to the official Microsoft Office wedding budget template in Template 3, the only difference is that it puts the total budget statistics from the major categories of wedding items into a new Sheet. The advantage of this is that you can easily calculate the total budget automatically by adjusting the budget percentage for each major category. Please see the screenshot below:

free wedding budget template4-1

This template is easy to use, the google sheets or Excel formulas in the template are very simple and just accumulate the cell values.

free wedding budget template4-1

If you have a more complicated customization requirement, you can send us a message and we can support you.

Click here to use this template.

5. Wedding Budget Templates By SmartBride

free wedding budget template5-1 This template is a free interactive wedding budget spreadsheet provided by After The Wedding, this template is also very easy to use. The template needs to be opened in Microsoft Excel by default, if you need to use this wedding budget template in Google Sheets, then you just need to import it.

The template is an overall wedding planning spreadsheet that includes a wedding calendar, wedding checklist, wedding supplier information, wedding guest template, wedding day schedule template and more.

The template also provides an average cost reference for wedding items, so you can easily compare it with the actual cost value.

free wedding budget template5-2

Click here to use this template.

6. Wedding Venue Tracking Spreadsheet

free wedding venue tracking template6-1

This template needs to be opened through google sheets by default, if you need to use this template through Microsoft Excel Spreadsheet, then you need to download this template in xls format through google sheets and then you can open this free wedding budget template through Excel Spreadsheet.

The wedding reception will definitely take up most of the total wedding budget, and most wedding planners suggest that the wedding venue will take up 45% to 65% of the total budget. Therefore, if wedding venue rental costs are not reasonably controlled, it will definitely affect the cost of other wedding items or services.

This template provides a breakdown of costs for specific items at each venue, so you can easily track exactly what each venue offers and how much it will cost at the end of the wedding.

This template is a wedding venue information collection template and does not contain any google sheets or Excel formulas. If you need to personalize it, you can send us a message.

Related Functions

  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
  • Google Sheets SUBTOTAL function
    The Google Sheets SUBTOTAL function returns the subtotal of the numbers in a list or database. The syntax of the SUBTOTAL function is as below:= SUBTOTAL (function_num, ref1, [ref2])….

Free Wedding Guest List Templates

Free Wedding Guest List Templates For Google Sheets1

This article will show you some free wedding guest list templates for google sheet and Microsoft Excel Spreadsheet and will also provide some explanations about the features or important functional features of these templates. When you are using these templates, you can very easily make modifications to meet your specific requirements.

What is the Wedding Guest List ?

The wedding guest list is used to record the information of all the friends and relatives who are going to attend the wedding, so that we can easily know the information of each invited guest and help us to arrange the wedding scene quickly and reasonably.

The wedding guest list mainly contains some basic information about the guests, such as: name, phone number, email address, address, city, favorite food, disliked or restricted food, relationship with the guests, seating information, and whether they have children, etc.

The wedding guest list allows you to easily know the number of invited guests, the number of guests invited by each side of the bride and groom, who are the friends invited by the parents, the number of guests who are actually attending on the guest list and other useful information.

A wedding guest list can be used to help you keep track of everyone you have planned to invite to the wedding, it will help you stay organized and make sure you won’t forget to invite or send thank you letters to your guests.

Why the Wedding Guest List is Important?

As an important part of your wedding, the wedding guest list must be something that you need to determine only after much thought and planning. For a small wedding of a dozen people, the wedding guest list may not play much of a role; but for a wedding scene of hundreds of people, it is impossible for you to remember the information of all the invitees, and if you do not keep track of all the people attending the wedding through the guest list, then the wedding scene will definitely become cluttered as well. This is why you need to standardize your wedding guest list as much as possible, which will help you to easily keep track of everyone who attended the wedding and prepare for the occasion accordingly.

Every couple wants to have a well-organized wedding scene and keep all the ceremonies in order, so it is necessary to create a wedding guest list through Google Sheets or Microsoft Excel spreadsheet.

How to Make a Customized Wedding Guest List Template

Below you will find 11 free and useful wedding guest list templates for google sheets or Microsoft Excel, you can customize your wedding guest list based on these free templates that we provide.

1.  Wedding Guest List Template (Designed by excelhow.net)

free wedding guest list template11-1

This wedding guest list template was carefully designed by ExcelHow, and it contains a comprehensive set of guest information and guest statistics that you can download and use for free.

The best feature of this template is the addition of various statistics, including the number of adults attending the wedding, the number of children attending the wedding, the total number of people attending the wedding, the total cost of the wedding dinner, the number of adults attending the wedding for both the bride and groom, the number of children attending the wedding for both the bride and groom, the number of people attending the wedding for both the bride and groom, the total cost of the wedding, and more.

If you have other custom requests, such as adding additional statistics or adding new guest information, you can send us a message.

Click here to use this template.

2. Wedding Guest List Template

Free Wedding Guest List Templates For Google Sheets1

By default, this template needs to be opened in google sheets and then you can enter the information of your invited guests, save and print it out. If you need to use this template in Microsoft Excel, you will need to export it in xls format from google sheets.

The columns of this template include the names of the guests attending the dinner and reception, relationship with the guests, phone number, email address, address, number of invitees, actual number of attendees, table number, seat number, dinner meat selection, gifts, thank you notes, etc.

The most important feature of this template is that it includes invited guest statistics for both the reception and the dinner, making it easy for you to count and track the cost of both events and the number of invitees and attendees separately.

Let’s see how to use this template:

a) Relationship column

The template contains two types of relationships by default: Bride’s relatives and Groom’s relatives. You can select the corresponding values directly from the drop-down menu. See the following screenshot:

Free Wedding Guest List Templates For Google Sheets1

If you need to add a new guest relationship type, or change the value of an existing guest relationship type, then you can refer to the following steps.

Step 1: In the Guest Relationship column, select all of the guest relationship cells

Free Wedding Guest List Templates For Google Sheets1

Step 2: Click the “Data” menu, then click the “Data Validation” submenu, and the Data validation window will be popped up.

Free Wedding Guest List Templates For Google Sheets1

Step 3: In the Data validation window, you can enter the values for the guest relationship type in the Criteria field text box, and you should notice that each value must be separated by a comma. After entering the values, click the “save” button to make it effective.

For example, if we need to add “Groom’s cousin” to the drop-down list of the guest relationship, then we can directly enter a comma and then enter “Groom’s cousin”, see the screenshot below.

Free Wedding Guest List Templates For Google Sheets1

Step 4: Drag the Auto Fill Handle from cell C13 to the other cells, so that the other cells can also apply the same setting.

Free Wedding Guest List Templates For Google Sheets1

As we can see from the above screenshot, “Groom’s cousin” has been successfully added to the drop-down list of guest relationships.

b) RSVP Column

 For the RSVP column, there are two values to choose from “Y” and “N“; There is a very special setting, when you select the “Y” value, the background color of the cell will be changed to red. When you select the “N” value, the background color of the cell will not be changed.

If you want to make changes to the warning color, then you can refer to the following steps.

Step 1: In the RSVP column, select all of the cells

Free Wedding Guest List Templates For Google Sheets1

Step 2: Click the “Format” menu, then click the “Conditional formatting” submenu, and the Conditional format rules window will be popped up.

Free Wedding Guest List Templates For Google Sheets1

Step 3: On the Conditional format rules window, click on the rules you have created.

Free Wedding Guest List Templates For Google Sheets1

Step 4: Under the Single color tab, you can select the cell background color you want to set under the Formatting style section.

Free Wedding Guest List Templates For Google Sheets1

c) Meal Choice Column

The Meal choice column mainly used to count the invited guests’ choice of meat, so that we can exactly know the amount of meat needed. This template provides three main types of meat for guests to choose from: pork, beef, and chicken.

 If you need more meat choices, then you can add a new column under Meal Choice cell. The template allows you to use a drop-down list to select which meats your guests like to eat.

d) The Costs for Dinner and Reception

The most important feature of the template should be the cost calculation. The template will automatically count all the invitees, all the wedding attendees, and automatically calculate the total cost of the wedding.

The total cost of the wedding mainly includes the sum of the wedding dinner cost and the wedding reception cost.

The actual number of wedding dinner attendees is summed by the SUM function over the valid values in the Attending# column (M:M).

The actual number of attendees at the wedding reception is also summed by the SUM function to the valid values in the Attending# column (T:T) under the reception area. The functions are as follows.

=SUM(M:M)

And

=SUM(T:T)

Free Wedding Guest List Templates For Google Sheets1

The wedding dinner cost is calculated by multiplying the single guest cost with the number of all guests who attended.

The wedding reception cost is calculated by multiplying the single guest cost with the number of all guests attending the reception.

Use the following Google Sheets or Excel formulas:

=D6*D8

Free Wedding Guest List Templates For Google Sheets1

And

=G6*G8

Free Wedding Guest List Templates For Google Sheets1

Free Wedding Guest List Templates For Google Sheets1

If you have other new requirements to customize, you can refer to the above steps to do it or you can leave us a comment and we will support you.

Click here to use this template.

3. Wedding Guest List Tracker Template

free wedding guest list template2_1

By default, this template needs to be opened by google sheets, if you need to use this template in Microsoft Excel Spreadsheet, then you need to export the template in xls format in google sheets.

This template does not have any google sheets or excel formulas, it is very easy to use.

The most important feature of this template is that the rows of the template headers are frozen and the name columns are also frozen.

If there are many guests and you want to see the last row of records, you can still see the row where the table header is located. See the following screenshot.

free wedding guest list template2_1

As you can see from the image above, when you scroll down to row 39, the table header area of the template is still visible properly.

There are lots of table header fields provided in this template, all the fields information may not be fully displayed on the current screen, at this time you need to view the information of the last few fields by scrolling the scroll bar and then you will see the name column will be always displayed. See the screenshot below.

free wedding guest list template2_1

For the RELATIONSHIP column in the template, you can change its selection method from manual entry to selection from a drop-down list, for details on how to change it, refer to the Guest Relationship Type Customization section in the first template.

Click here to use this template.

4. Wedding Guest List Template

free wedding guest list template3-1

This template needs to be opened via google sheets by default, if you need to use it in Microsoft Excel Spreadsheet, you need to export it from google sheets.

The most important feature of this template is that the header rows are frozen so that they are always visible. This template also provides some useful notes for creating a wedding guest list.

This template not only contains the usual information about guests’ names, home addresses, email addresses, number of invitees, actual number of attendees, number of children attending, seating arrangements, etc., but also adds a table header field for dietary restrictions, which can be used to record guests’ dietary favorites, so that you can make dietary arrangements in advance.

free wedding guest list template3-1

The template does not use any google sheets or Microsoft Excel formulas, you can not directly see the number of guests attending the wedding, if you have this requirement, then you need to use SUM function to do so.

Click here to use this template.

5. Wedding Guest List Template

free wedding guest list template4-1

This template is a basic guest list template, you can create wedding guest list, annual guest list or various business event guest list templates based on this template. By default, you need to open it via google sheets, or if you need to use it in Microsoft Excel Spreadsheet, you need to export it from google sheets before using it.

The template contains the most common information for tracking or recording invited guests, if you need to add additional field values then you just need to add the corresponding columns in the template.

This template does not contain any google sheets or Excel formulas, so it does not have the ability to count various data, if you have statistical requirements then you can refer to the first template in this article or leave a message to us, we can support you.

Click here to use this template.

6. Wedding Guest List Template

free wedding guest list template5-1

This template is very easy to use, and the design is very simple, the columns in the template contains First name, Last Name, Email address, Phone number, Street Address, comments and other general information, and the status column can be used to record the guest’s personal situation, such as single, married and married with children, etc.

The comments column can be used to record some other information about the guest, such as whether the guest has special beliefs, etc.

free wedding guest list template5-1

This template needs to be opened by google sheets by default, if you need to use this template in Excel, then you need to export it from google sheets.

If you like this template, then you can download it for free; if you need to make more customization for this template, you can leave a message to us and we can provide support.

Click here to use this template.

7. Wedding Guest List Template

free wedding guest list template6-1

This template looks very clean, and it contains only the most common guest information. If your wedding is small and you are inviting a small number of guests, then this wedding guest list template will be ideal for you.

The difference between this wedding guest list template and the regular template is that it adds a personalized wedding guest list title “Thomas and Rebecca’s Wedding”, and you can change the name section with the names of the bride and groom.

The template also adds a meat choice, which will help you to know in advance how much meat your invited guests will need.

If you like this template, you can download it for free and if you have any other customization requirements, please feel free to send us a message and we can assist you as well.

Click here to use this template.

8. Wedding Guest List Template

free wedding guest list teamplate 7_1

This template is a google docs wedding guest list template which adds venue date and nice table background. It is a great wedding guest template for small weddings or weddings with few invited guests.

As the template is based on google docs, it is not too easy to do more customization. If you need to use the template via Microsoft Word, then you need to download the template locally in doc format.

Click here to use this template.

9. Wedding Invitation Tracker Template

free wedding guest list template8-1

This template is the official wedding invitation tracking template provided by Microsoft Excel, it contains the usual wedding guest information and adds some statistics for invited guests, such as the number of people attending the wedding, the number of people not attending the wedding, the number of days remaining for the wedding, the number of people who have sent invitations, the number of people who have replied, etc.

The WEDDING DATE, DAYS REMAINING, ATTENDING, NOT ATTENDING, OUTSTANDING, etc. in this template will be automatically calculated by the relevant Google Sheets or Excel formulas. Here we explain how these formulas work:

  • DAYS REMAINING

First you need to enter the exact date of the wedding under WEDDING DATE, and then DAYS REMAINING is automatically updated to the correct time. The formula used is as follows:

=WeddingDate-TODAY()

free wedding guest list template8-1

Where WeddingDate is a defined Name Range with a range value of B2.

  • ATTENDING

The Number of people Attending wedding is automatically updated in cell B6. We need to filter out all rows with the value “Yes” in the RSVP column and sum the values in the NUMBER IN PARTY column so that we can count all the guests who will be attending the wedding. The formula is as follows.

=SUMIF(rsvp,"Yes",number_in_party)

free wedding guest list template8-1

Where rsvp and number_in_party are the two defined Name Ranges, rsvp=F3:F18, number_in_party=G3:G18

  • NOT ATTENDING

The Number of people Not Attending wedding is automatically updated in cell B8. We need to filter out all rows with the value “No” in the RSVP column and sum the values in the NUMBER IN PARTY column, so that we can calculate the number of guests who did not attend the wedding. The formula is as follows:

=SUMIF(rsvp,"No",number_in_party)

free wedding guest list template8-1

Where rsvp and number_in_party are the two defined Name Ranges, rsvp=F3:F18, number_in_party=G3:G18

  • TOTALS

Three indicators are counted in the TOTALS area of the template: the number of people who have sent invitations, the number of RSVPs, and the number of people planning to attend the wedding.

Counting the number of invitations that have been sent by using the following formula:

=COUNTIF('Invitation Tracker'!$E$3:$E$18,"Yes")

free wedding guest list template8-1

Counting the number of RSVPs by using the following formula:

=COUNTA('Invitation Tracker'!$F$3:$F$18)

Counting the number of people planning to attend a wedding can be done by using the following formula:

=SUBTOTAL(109,number_in_party)

Click here to use this template in Google sheets.

If you wish to use this template in Microsoft Excel Spreadsheet, you can click here.

10. Wedding Guest List Template

free wedding guest list teamplate9-1

You can use this Wedding Guest List template to record who you have invited to your wedding, their contact details, meal requirements etc. It will help give you a comprehensive list of who’s doing what on the run up to your wedding day.

This template has a newly added Transport column compared to the other templates, which is used to record the mode of transportation to attend the wedding.

The template also does not contain any google sheets or Excel formulas, so there are no complicated statistical functions; if you need to do customization based on this template, then you can refer to the template above or you can send us a message and we can give you support.

Click here to use this template in Google sheets.

11. Wedding Guest List Template

free wedding guest list template10-1

This template can be opened via Google Sheets by default. If you need to use this template through Microsoft Excel, then you will need to download and export this template through google sheets first.

This template not only provides your regular wedding guest information, but also adds a hotel information field. This also makes it easier for you to keep track of your guests’ accommodation information and allows you to provide the necessary assistance.

Another feature of this template is that it provides a table arrangement form, as shown below.

free wedding guest list template10-1

Click here to use this template in Google sheets.

Related Functions

  • Google Sheets TODAY function
    The Google Sheets TODAY function get the current date as a date value. And it will be updated each time when your worksheet is changed or opened.The syntax of the TODAY function is as below:=TODAY()
  • Google Sheets COUNTIF function
    The google sheets COUNTIF function is used to count the number of cells in a cell range that meet a given condition.The syntax of the Google Sheets COUNTIF function is explained as follows: = COUNTIF (range, criteria)…
  • Google Sheets COUNTA function
    The google sheets COUNTA function counts the number of cells that are not empty in a range. And it returns the number of non-blank cells within a range or values. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…
  • Google Sheets SUBTOTAL function
    The Google Sheets SUBTOTAL function returns the subtotal of the numbers in a list or database. The syntax of the SUBTOTAL function is as below:= SUBTOTAL (function_num, ref1, [ref2])….

Calculate The Average Of The Last 3, 5, Or N Numeric Values In Google Sheets

As an Google sheets user, you might have come across a task in which you need to calculate the average values of the last 2 numeric values, and you might have done this task manually but suppose if the last values you want to calculate the average exceeds to 3, 5 or N numeric values then what would you do? If you would tend to do this task manually, then it would be your foolish decision because doing these kinds of lengthy tasks manually is very hard, and when it comes to N numeric values, then It would not be wrong to say that this is near to impossible to do it manually.

But don’t worry about it because after carefully reading this article, you can easily calculate the average values of the last 3, 5, and most important N numeric values within seconds.

So without any further delay, let’s dive into it;

Calculate The Average Of The Last 3 Values In Google Sheets

Average the last 3_ 5 or N numeric values in google sheets1 

The General Formula is as below based on the AVERAGE function, LOOKUP function, LARGE function and IF function:

=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(Values_Range),ROW(Values_Range)),{1,2,3}),ROW(Values_Range), Values_Range))

Note: this above is an array formula, you need to press “CTRL+SHIFT+ENTER” short cuts to make it as array formula.

 Let’s See How This Formula Works

You can use an array formula for getting the average of the last 3 values in the range. The formula in E3 in the example is as follows:

=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(Values_Range),ROW(Values_Range)),{1,2,3}), ROW(Values_Range), Values_Range))

Average the last 3_ 5 or N numeric values in google sheets1

Here “ Values_Range ” refers to the designated name range B4:B11.

Average the last 3_ 5 or N numeric values in google sheets1

This is an array formula, so input it using control + Shift + enter.

Because the AVERAGE function computes an average of numbers supplied in an array, practically all of the effort in this formula is to construct an array of the latest three numeric values in a range.

 =IF(ISNUMBER(Values_Range),ROW(Values_Range))

The IF function in the formula is used to “filter” numeric numbers from the inside out.

Because the ISNUMBER function returns TRUE for numeric values and FALSE for other values (including blanks), and the ROW function produces row numbers, the outcome of this operation is an array of row numbers corresponding to numeric entries:

={4;5;FALSE;7;8;FALSE;10;11}

Average the last 3_ 5 or N numeric values in google sheets1

=LARGE({4;5;FALSE;7;8;FALSE;10;11},{1,2,3})

The above array is sent to the LARGE function, using the array constants 1,2,3 for k. LARGE ignores FALSE values and returns an array with the greatest three integers, which correspond to the last three rows with numeric values:

={11,10,8}

Average the last 3_ 5 or N numeric values in google sheets1

=LOOKUP({11,10,8}, ROW(Values_Range), Values_Range))

The above array is used as the lookup value in the LOOKUP function. The ROW function provides the lookup array, and the return array is the named range “ Values_Range “:

={435,657,356}

Average the last 3_ 5 or N numeric values in google sheets1

The array of similar values in “ Values_Range ” returned by LOOKUP is then put into AVERAGE:

=AVERAGE({435,657,356})

Average the last 3_ 5 or N numeric values in google sheets1

Calculate The Average Of The Last 5 Values IN Google Sheets

Please use the following array formula, which would assist you in calculating the last 5 values in Google Sheets:

In a blank cell, type the following formula:

=IF(COUNT(B:B),AVERAGE(INDEX(B:B,LARGE(IF(ISNUMBER(B1: B10000),ROW(B1: B10000)),MIN(5,COUNT(B1: B10000)))):B10000),"no data found")

Average the last 3_ 5 or N numeric values in google sheets1

Note: B:B is the column that holds the data you used, B1: B10000 is a dynamic range that you may stretch as long as you need, and the number 5 represents the latest n values, and then press Ctrl + Shift + Enter to make the formula as array formula to obtain the average of the last 5 numbers. See the following screenshot:

And now, when you enter new numbers underneath the old data, the average is updated as well, as seen in the screenshot:

Average the last 3_ 5 or N numeric values in google sheets1

Related Functions

  • Google Sheets ROW function
    The Google Sheets ROW function returns the row number of a cell reference.The ROW function is a build-in function in Google Sheets and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • 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 Google Sheets and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value],
  • Google Sheets ISNUMBER function
    The Google Sheets ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Google Sheets LOOKUP function
    The Google Sheets LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
  • 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 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)…
  • 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 INDEX function
    The Google Sheets INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Google Sheets and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • 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])….
  • Google Sheets INDIRECT  function
    The Google Sheets ROW function returns the row number of a cell reference.The ROW function is a build-in function in Google Sheets and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Google Sheets SUBTOTAL function
    The Google Sheets SUBTOTAL function returns the subtotal of the numbers in a list or database. The syntax of the SUBTOTAL function is as below:= SUBTOTAL (function_num, ref1, [ref2])….

Google Sheets SUBTOTAL Function

This post will guide you how to use Google Sheets SUBTOTAL function with syntax and examples in Google Spreadsheet.

Description

The Google Sheets SUBTOTAL function returns the subtotal of the numbers in a list or database.

The SUBTOTAL function is a build-in function in Google Sheets and it is categorized as a Math and Trigonometry Function.

Syntax

The syntax of the SUBTOTAL function is as below:

= SUBTOTAL(function_num, ref1, [ref2])

Where the SUBTOTAL function argument is:

  • Function_num – This is a required argument.  It can be set as 1-11 or 101-111 for the subtotal.  1-11 that includes hidden rows and 101-111 excludes hidden rows.
Function_num
(includes hidden values)
Function_num
(ignores hidden values)
Function
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

 

Ref1– This is a required argument. The first named range or reference that you want to subtotal.

Google Sheets SUBTOTAL Function Example

the below examples will show you how to use Google Sheets SUBTOTAL function to return the subtotal of the numbers in a list.

#1 =SUBTOTAL(103,B:B)

google sheets SUBTOTAL Function1

Note: the above formula will call COUNTA function to count the number of cells(B:B) that contain numbers. It will return value 5.

#2 =SUBTOTAL(109,B:B)

google sheets SUBTOTAL Function2

Note: the above Google Sheets formula will call SUM function to add all numbers in range cell B:B, so it will return value: 160.

See Also:

Calculate The Average Of The Last 3, 5, Or N Numeric Values In Google Sheets