(Excel2003) LINEST with null values

[ChrisGreaves]

Linest.png

Excel2003.

The attached workbook uses the nifty LINEST function to assist me in predicting how many days ("duration") it might take to complete an audio book project.
I chose the factors/parameters (columns B:F) because the data were relatively easy to obtain, and I felt that each of them contributed to delays in a project.

I key in the LINEST function and do the Ctrl+Shift+Enter trick, and am rewarded with a slew of #VALUE errors.

If I fill in a value for the blank cells B4 and C6 (purple), then the LINEST function is happy.

Question: Is there a relatively simple way to cope with null data being fed into LINEST without having to guess at an unknown datum?

(1) I could delete those rows with vacant cells, even do that automatically if I had to
(2) I will delete the three over-budget rows (triple-asterisks) to run this; my idea is to predict those projects might go over-busget
(3) Down the road the table could be massive (a group collaboration, with other people submitting project data)

Thanks, Chris

[HansV]

I'd go with (1)

[ChrisGreaves]

I'd go with (1)

Thanks, Hans.
I did see in a web page that there was a bug in LINEST, fixed, the page said, in Excel 2003.
If a least-squares method is being used, perhaps there is a rogue division-by-zero in there somewhere.
Sigh!
Chris

[SpeakEasy]

Try fiddling with this expression

=LINEST(G2:G14,IFERROR(B2:F14,0),TRUE,TRUE)

[p45cal]

LINEST does not want to work with anything but contiguous numeric values.
The danger of replacing errors/blanks/non-numeric values with 0 is that the zeroes are treated as zero values, not ignored, so will end up skewing the statistics.
Other functions such as FORECAST work OK with blank cells so you might be better off using those.