How To Calculate Exponential Integral In Excel

Exponential integral is a function used to calculate the displacement of data along an exponential distribution.

We can use this function to determine the rate of growth or decline of variables. For example, an online business owner can use this to calculate the growth rate of sales on their site.

To create an Exponential Integral function, you will need the source data. In our scenario the sales figures by day for the online store.

For the purposes of this guide sample data has been created for you to use in the scenario listed above.

Steps to create an Exponential Integral function

The steps below will take you through the process using sample data. A quick reference guide is available at the end of this document.

Open sample spreadsheet How To Calculate Exponential Integral In Excel sample data.

Click in any cell within the table. On the menu click Insert, in the Charts group, click the Scatter chart icon, choose the Scatter option (the first listing).

insert exponential scatter chart

Excel generates a scatter chart. Click on the chart, then click the + plus sign on the right of the chart, tick Axis Titles, text boxes appear.

exponential axis title

Enter Days in the text box of the X axis across the bottom, and Sales by day in the Y axis label, along the left.

See also  How to Calculate Incremental Working Capital

exponential left axis change

Right-click on any of the data points on the chart, click Add Trendline.

exponential chart add trendline

The Format Trendline pane opens to your right.

exponential chart format trendline

Under Trendline Options select Exponential.

exponential trendline

Stay within the pane, scroll down, and select Display Equation on chart and Display R-squared value on chart. The equations appear on the chart.

exponential trendline r squared

Calculating The Growth Factor and Growth Rate

We will use the EXP function and the exponent (the number in the top right of the equation on the chart) to calculate the growth factor. Exponent = 0.5654 in this scenario.

In an empty cell type =EXP(0.5654), press enter.

exponential exp formula

Excel displays the growth factor – 1.76015, subtract 1 from the factor to get the growth rate of 0.76015.

Forecasting With The Exponential Integral

We can use the growth factor and the initial amount, the y value from the chart, to forecast the sales figures for a given number of days. Initial amount (Y) = 9.1723 in this scenario.

In cell E11 enter 7, in the sales cell to the right enter this formula =9.1723*(1.76015)^E11, press enter.

exponential sales total

The result is at 10 days the online store can expect 2618 sales (rounded down).

exponential sales chart

Forecasting With The Format Trendline pane

We can use the Format Trendline pane, Forecast area to chart periods forwards from the current end point of the chart, or backwards from the beginning.

Right click on any of the data points on the chart, the Format Trendline pane opens.

exponential forecast

Scroll down to Forecast, in Forward, type 2, press Excel calculates 2 periods into the future and updates the chart.

See also  Gamma distribution calculator

exponential sales chart forecast r squared

Quick reference guide

Steps to create an Exponential Integral function

  1. Open spreadsheet containing your data.
  2. Click in any cell within the table. On the menu click Insert, in the Charts group, click the Scatter chart icon, choose the Scatter option.
  3. Excel generates a scatter chart. Click on the chart, then click the + (plus sign) on the right of the chart, tick Axis Titles, text boxes appear.
  4. Type the name for your X axis title across the bottom; type your Y axis title along the left.
  5. Right click on any of the data points on the chart, click Add Trendline.
  6. The Format Trendline pane opens to your right.
  7. Under Trendline Options select Exponential.
  8. Stay within the pane, scroll down, and select Display Equation on chart and Display R-squared value on chart. The equations appear on the chart.

Calculating The Growth Factor and Growth Rate

We will use the EXP function and the exponent (the number in the top right of the equation on the chart with an ‘x’ at the end) to calculate the growth factor.

  1. In an empty cell type =EXP(exponent value), press enter.
  2. Excel displays the growth factor, subtract 1 from the factor to get the growth rate.

Forecasting With The Exponential Integral

We can use the growth factor and the initial amount, the y value from the chart, to forecast the sales figures for a given number of days.

  1. In the cell beneath your last period/interval make an entry for the next period. g., if your table runs to 6 days, enter 7 in the last row, in the cell to the right enter this formula =initial amoun*(1.growth factor)^enter the cell address for the new period/interval you entered, press enter. 
  2. The result appears, round the result if appropriate, that is, the variable is the amount of visitors, whole numbers are appropriate.
See also  How to calculate XIRR Annualized Return

Forecasting With The Format Trendline pane

We can use the Format Trendline pane, Forecast area to chart periods forwards from the current end point of the chart, or backwards from the beginning.

  1. Right-click on any of the data points on the chart, the Format Trendline pane opens.
  2. Scroll down to Forecast, in Forward, type the number of interval periods you want to forecast, press enter.