Exponential Trend Line Will Not Display on Chart When Using Dates on X-axis

I'm trying to plot coronavirus data against time and add an exponential trend line.

The domain runs from March 1 2020 into April, so the numbers that represent the dates run from 43891 to 43935.
The range is from 75 to 33,500 (as of today).

There are no zero or negative values in the range, so the exponential trend line selection is not greyed out.

But nothing happens when I add an exponential trend line.

If I switch the domain values for the dates to numbers from 1 to 45, the exponential trend line plots just fine.

In other charts in the past I have been able to use dates along the x-axis, but these dates spanned a longer period, from 1940 to the present (date code numbers from 14612 to 43890)

Is this an accuracy/precision problem with the internal exponential trendline calculations?

Is there some minimum date domain time period you need to specify to get this to work, or should I just delete the regular axis labels and add my own text string for the dates?

Or, should I just shift the dates down to March 1, 1900 so that the date values range from 61 to 106?

Axis may be set to categorical. Try setting it to continuous or dates/numbers.

Is this an accuracy/precision problem with the internal exponential trendline calculations?

I don't know exactly how microsoft has it programmed under the hood, but I strongly suspect that, yes, this is an accuracy/precision problem inside of the chart trendline calculations.

The basic exponential equation that the chart trendline uses is y=b*exp(mx) (or, equivalently, as the LOGEST() function does it, y=b*m^x). Anytime you have a number like 44000 (your date values) in the exponent, you have the potential for some massive (or tiny) numbers -- easily extending beyond what Excel can handle.

One solution, as you've already discovered, is to transform dates from the actual serial number to "days since a recent start date".

Another solution is to perform all the calculations in log(y) vs. x space, then wait until the very end to perform the final exponentiation.

I'm sure there are others. The basic idea is -- using actual date serial numbers in a regression has the potential to create overflow errors, so you need to do something to avoid those overflows.

Hi jerbes,

I'm at home looking for viruses (like looking for electricity)

Here is an example that shows dates, days and an exponential graph. I always work better from an example. If you click on the curve itself it brings up options for it specifically. The user interface is a bit different between different versions of Excel. See if this helps.
Exponential Trend Line Virus.xlsx
I made up some random data using the Rand() function. Each cell was (1+Rand()) times the cell above it. I copy and pasted values only to keep it from changing for the final save. If you had some real numbers you could type over my numbers and get a much better idea and formula for the trend.

Hmm, on my end using Date type axis has no issue showing exponential trendline.

Best bet is to upload sample file.

Ah, I see the issue. I didn't check for the wide range of y values.

Your technique is probably the right one.

The one caution I would make when mapping dates down to 1900 is to be aware of the long standing "bug" where Excel treats 1900 as a leap year. It works for 2020, because 2020 is a leap year, but other years, your dates will potentially be "off" by a day.

To see why the problem in the OP occurs, enter =LINEST(LOG(B4:B25),A4:A25) across two cells. You will see that the b in y=b*exp(mx) wants to be about 1E-5500, but the smallest value Excel's double precision can handle is about 1E-300. This underflow error is what causes the chart trendline to fail.

In addition to this solution (mapping dates to the year 1900), you could also change the order of calculation to avoid the underflow. With the output from the LINEST(...) function above, you can use =10^(a*date+b) to compute the smoothed values. This order of operations should avoid the underflow error that causes the chart trendline to fail.