So, maybe I've just been working way too hard on this project, and my Excel program is sick of seeing me every day for 18-20 hours at time, but I really don't get this one.
I'm using the following power forecast formula:
=(EXP(INDEX(LINEST(LN(MyPrices),LN(MyUnits),,),1,2)))*SubjectUnit^(INDEX(LINEST(LN(MyPrices),LN(MyUnits),,),1))
to predict the value of a property from a set of comps along a nonlinear regression analysis. If you don't know what all of that means, don't sweat it. It's not that important.
What *is* important is that I've been using it for years, and it'll work for just about anything. Might not be terribly accurate it you feed it bad data, but it'll still spit out a value.
I've been using a database set using the same subject and comps for several days, putting together an analysis interface, when I started getting errors on this formula, and always on dates. I figured I just had something going on in my code that was wrong, and was just going to ignore it while I built the interface, and then go back and fix any bugs that were still hanging around.
Until today.
Today, a part of my code that's been in place and untouched for quite a while started throwing the same error. On the same dataset that's been working fine for months without throwing errors. So I get annoyedly curious (if I can coin a word).
I've isolated the problem down to an identifiable breaking point, but I still have no idea what's causing this never-before-experienced error on seemingly legitimate data that's been working fine for weeks.
I've included two screenshots so you can see *exactly* what's happening on my screen; one works, the other throws an error.
Both screenshots show a list of 23 properties that have sold in the past year, closing dates shown in column A, and closing prices shown in column B. There's a scatter chart showing all of the data points of the 23 properties, by date and price. In cell H2, we have the forecast formula I referenced above, with a copy of the actual formula in use immediately below it so you can see it.
The only thing that's different between the screenshots in the data set is the value of cell B24 (highlighted in yellow), the closing price of the 23rd comp. If I set the closing price at $131,000 or above, everything's fine. The predicted value works in cell H2, and the power trendline is displayed on the scatter chart.
If I set the value of cell B24 below $131,000 (for example: $130,000), then the formula errors out to #NUM, and the trendline disappears from the scatter chart. The data point of this 23rd comp is identified in the scatter chart in red, and as you can see, there's nothing all that extraordinary about it. It's not on the fringe. It's not throwing the data set into to some skewed formation. It's just sitting there nicely in the middle as a good little comp.
I've used this formula and charting system on thousands of properties, with anywhere from three to three thousand comps at a time. I've never seen this happen. I have no idea what's causing it, and so I have no idea how to fix it.
Thoughts?
Thanks!
James
Works:
[ATTACH=CONFIG]71236[/ATTACH]
Doesn't Work:
[ATTACH=CONFIG]71237[/ATTACH]
Questions abound in my mind like "Why have I used this formula on this dataset for weeks and it never errored out before?" and "I've used this formula for years, and it's never errored out before. Why now on such a typical dataset?"
In the end, if there's a capacity issue with Excel, then that's a legitimate reason. If you recreated it on your computer, it's not something wrong with my computer. If it mathematically hits the ceiling, then 1+1=2, and I can complain about it all day long, but tomorrow 1+1 is still going to = 2. Thanks for letting me know! Just 'cause I don't like the answer, doesn't make it not right. lol!