(Excel2003) LINEST with null values
-
- PlutoniumLounger
- Posts: 15587
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
(Excel2003) LINEST with null values
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
You do not have the required permissions to view the files attached to this post.
Last edited by ChrisGreaves on 05 Oct 2022, 18:12, edited 1 time in total.
There's nothing heavier than an empty water bottle
-
- Administrator
- Posts: 78412
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlutoniumLounger
- Posts: 15587
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: (Excel2003) Linest with null values
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
There's nothing heavier than an empty water bottle
-
- 4StarLounger
- Posts: 544
- Joined: 27 Jun 2021, 10:46
Re: (Excel2003) LINEST with null values
Try fiddling with this expression
=LINEST(G2:G14,IFERROR(B2:F14,0),TRUE,TRUE)
=LINEST(G2:G14,IFERROR(B2:F14,0),TRUE,TRUE)
-
- 2StarLounger
- Posts: 144
- Joined: 11 Jun 2012, 20:37
Re: (Excel2003) LINEST with null values
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.
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.