LOGEST producing values that will produce 0 for any value of X.

[Technetium]

I have the following set of data I'm feeding into LOGEST (covering A3:B21):
2/15/2022 447.888
2/16/2022 445.64856
2/17/2022 443.40912
2/18/2022 985.66968
2/19/2022 980.70774
2/20/2022 1906.3083
2/21/2022 1896.693548
2/22/2022 1887.078795
2/23/2022 1877.464043
2/24/2022 1867.84929
2/25/2022 1858.234538
2/26/2022 1848.619785
2/27/2022 1839.005033
2/28/2022 1829.39028
3/1/2022 2074.575528
3/2/2022 2063.686775
3/3/2022 2052.798023
3/4/2022 2041.90927
3/5/2022 2676.020518

When using =LOGEST(B3:B21,A3:A21,TRUE,TRUE)

I get the following result:

1.085689877 0
0.014614058 652.0208367
0.650561724 0.348905863
31.64950748 17
3.852862335 2.069500125

But LOGEST produces data for a formula of the form: y=b*m^x, where m = 1.085689877 and b=0. This means that y=0 for any value of x!

Also, I already had the other cell code in place and when it tried to evaluate this for x=11/8/2022, I got a #NUM! error, which I'm guessing is because Excel doesn't want to raise something to the 44873 power. Not really sure how to get around that since that's how Excel stores dates, but that's a separate issue. I think.

[MrShorty]

tl:dr I think it is all part of the very large numbers that Excel uses for dates in 2022. To fix this, you either need to take the logarithms yourself and use LINEST() to perform the regression, or you need to establish a more realistic time 0 so that the x values are much smaller.

Explanation of the current output: As near as I can tell, m=1.086 is correct and b=0 is kind of correct. b is probably a very tiny number that, when written to Excel is too small for Excel to report in double precision. Excel chooses to output 0 rather than some kind of underflow error.

Solution 1: use logs and LINEST(). y=b*m^x is equivalent to ln(y)=ln(b)+x*ln(m). If you add a couple of columns to take the log of x and y, you can use these two columns in the LINEST() function to get ln(b) and ln(m). Recognizing the probability of overflow/underflow errors, you will probably need to be very careful about formulating your estimating equation, probably preferring something like y=exp(ln(b)+x*ln(m)) rather than the previous form.

Solution 2: establish a more usable time 0. Based on the built in date/time serial numbers, time 0 is Jan 0 1900 -- 122 years (45000 days) before the data in question. I don't know what a better time 0 would be, so I would simply use the first date in the data set. Add a column =A1-$A$1 (copied down), and use that column for your x values in the LOGEST() function.

It seems to me that solution 2 will be less prone to rounding errors and overflow/underflow errors, so I would probably be inclined to use solution 2.

[Technetium]

Yeah, that ended up being the problem. The first value was correct but the second value was definitely not 0.

What I did was set the earliest date among my x values to 1, and then subtracted the earliest date from the other dates. This was easy to do because I have a cell elsewhere with a saved label for the start date, and in fact the list of dates is built procedurally from that.

[hrlngrv]

Consider your data.

deleteme-chart.png

I'll admit it's been decades since I left grad school, and my degree was in math rather than stats, and most of what I've done since involves survival functions, but the chart of your actual data indicates to me that you shouldn't try to use LOGEST.

I used 3 alternative fits.

Mixed:
D3:E7: =LINEST(LN(B3:B21),A3:A21,1,1)
L3: =EXP($E$3+$D$3*$A3)

AdjLog:
D10:E14: =LOGEST(B3:B21,A3:A21-DATE(2021,12,31),1,1)
M3: =$E$10*$D$10^($A3-DATE(2021,12,31))

Linear:
D17:E21: =LINEST(B3:B21,A3:A21,1,1)
N3: =$E$17+$D$17*$A3

None produce great fits. FWIW, Mixed and AdjLog produce the same fitted results, which means Mixed is simpler.

That said, to me the data looks like it's exponential decay with impulses at irregular intervals. If so, you'd be better off estimating the rate of decay by adjusting points 1-3, 4-5, 6-14, and 15-18. Far too few points to draw any inferences about the frequency or magnitude of the impulses.

[Technetium]

The data is not real data, it's sample data I created to try and design the cell formula for. I also create regressions for linear and quadratic, and the end result uses cell formulas to use r^2 to pick the best candidate for regression (with bias towards linear, then logarithmic, then quadratic, simply because it's easier to get a higher r^2 with quadratic and I trust linear more than logarithmic). The real data would probably have hundreds of data points over a much broader range of time. The regressions have to be made in a way where I don't know the data beforehand, and it can handle any incoming data and produce the best fit across a broad range of scenarios.

I forgot to mark this as solved, thank you both for the assistance.