How to Use LOGEST Function in Google Sheets

This guide will explain how to use the LOGEST function in Google Sheets.

When we need to calculate an exponential curve that fits our data set, we can easily perform this using the LOGEST function in Google Sheets.

The rules for using the LOGEST function in Google Sheets are the following:

• We must enter the function as an array formula. Otherwise, the function will only return the first m value in the calculated array. 
• When the known_data_y argument is a two-dimensional array or range, the known_data_x argument must have the same dimensions. Otherwise, it must be omitted. 
• When the known_data_y is a one-dimensional array or range, the known_data_x may represent multiple independent variables in a two-dimensional array or range. 
• If the verbose argument is TRUE, the LOGEST function returns additional regression analysis statistics.

Google Sheets has several built-in functions that let us accurately perform different statistical analyses. There are many functions we can utilize to perform statistical analysis.

One of them is the LOGEST function, which we can utilize to find the exponential curve that fits our data. This function is also being used during regression analysis.

In this guide, we will provide a step-by-step tutorial on how to use the LOGEST function in Google Sheets. Additionally, we will explore the syntax and a real example of using the function.

Great! Let’s dive right in.

The Anatomy of the LOGEST Function

The syntax or the way we write the LOGEST function is as follows:

=LOGEST(known_data_y,[known_data_x],[b],[verbose])

• = the equal sign is how we start any function in Google Sheets.
• LOGEST() refers to our LOGEST function. This function is used to return the statistics that describe an exponential curve matching the known data points in the data set.
• known_data_y is a required argument. This refers to the array or range containing the dependent (y) values that are already known.
• known_data_x is an optional argument. This must have the same length as the known_data_y argument. This refers to the values of the independent variables corresponding with the values in the known_data_y. 
• b is also an optional argument. Based on the general exponential form of y = b*m^x for a curve fit, it will calculate b if TRUE or force b to be 1 and only calculates the m values if FALSE. By default, this is TRUE.
• verbose is another optional argument. This refers to the flag specifying whether to return additional regression statistics or only the calculated coefficient and exponents. By default, this is FALSE. 

Note: The syntax of the LOGEST function in Excel slightly differs from the one above. 

The Optional Arguments of the LOGEST Function

Firstly, the known_data_x is an optional argument refers  to the values of the independent variables corresponding to the values in the known_data_y. 

If the known_data_y is a one-dimensional array or range, the known_data_x may represent multiple independent variables in a two-dimensional array or range. If the known_data_y is a single row, each row in the known_data_x is interpreted as a separate independent value. 

Secondly, the b optional argument refers to the general exponential form of y=b*m^x for a curve fit. By default, the argument is TRUE, which calculates b or forces b to be 1. If we input FALSE, the function will only calculate the m values. 

Lastly, the verbose argument will specify whether the function will return additional regression statistics or only the calculated coefficients and exponents. By default, this is FALSE, which only returns the coefficients and exponents. 

If we input TRUE, the function will return the set of exponents for each independent variable and the coefficient b. 

Additionally, it will also return the following:

• The standard error for each exponent and the coefficient
• The coefficient of determination (between 0 and 1, where 1 indicates perfect correlation)
• The standard error for the dependent variable values,
• The F statistic or F-observed value indicates whether the observed relationship between dependent and independent variables is random rather than exponential
• The degrees of freedom
• The regression sum of squares
• The residual sum of squares

A Real Example of Using LOGEST Function in Google Sheets

Let’s say we have a data set containing x and y values. So our initial data set would look like this:

The LOGEST function is based on the curve equation, y=b*m^x. Before we use the LOGEST function, we will first verify that the values in our data set follow an exponential curve. 

To do this, we will quickly insert a line chart of the x and y values.

Once we can see that the data follows an exponential curve, we can use the LOGEST function.

We will utilize the formula below:

=LOGEST(B2:B11,A2:A11,TRUE,TRUE)

The first part of the formula is our known_data_y argument. We simply selected the array B2:B11 containing the y values. Next, we selected the array A2:A11 containing the x values as our known_data_x.

Then, we set the b argument to TRUE so the function can calculate b or force b to be 1. Lastly, we input TRUE as our verbose argument since we want to return additional regression statistics. 

Our final data set would look like this:

You can make your own copy of the spreadsheet above using the link below. 

Amazing! Now we can dive into the steps of using the LOGEST function in Google Sheets.

How to Use LOGEST Function in Google Sheets

1. First, we need to verify that the x and y values in our data set follow an exponential curve. We will select the data set and click the Insert tab to do this. 

2. Then, we will select Chart in the dropdown menu.

3. Once the chart has been inserted, we can now verify that our data set follows an exponential curve.

4. Now, we can use the LOGEST function to get the exponential curve formula. We will select an empty cell to input our formula and display the results.

5. To begin, we will type an equal sign and the function name. Our formula will be “=LOGEST(”.

6. Next, we will select the array containing the y values. Our formula will become “=LOGEST(B2:B11”.

7. We will also select the array containing the x values. Our formula would become “=LOGEST(B2:B11,A2:A11”.

8. Then, we will input TRUE as our b argument to calculate the b value. In this case, our formula would become “=LOGEST(B2:B11,A2:A11,TRUE”.

9. Lastly, we will input TRUE as our verbose argument to return additional regression statistics. Our final formula would be “=LOGEST(B2:B11,A2:A11,TRUE,TRUE)”.

10. We will press the Enter key to return the result.

And tada! We have successfully used the LOGEST function in Google Sheets.

You can apply this guide whenever you need to calculate an exponential curve that fits our data set. You can now use the LOGEST function and the various other Google Sheets formulas available to create great worksheets that work for you.

That’s pretty much it! Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.