Cannot use Linest Fn with blanks in it

[93arpan]

Hi Everyone.

I'm working on this complex formula (that's what I think, I know for some of the individuals it will be easy)

=((INDEX(LINEST($N$4:$N$19,LN($O$4:$O$19),TRUE,FALSE),1))*(LN(O4)))+INDEX(LINEST($N$4:$N$19,LN($O$4:$O$19),TRUE,FALSE),2)

Customers (Column L)	Category (Column M)	Avg. NPP (Column N)	Qty in KG (Column O)	Demand Curve (Column P) -Formula
C1	Low	7.1	765.0	7.76
C2	Low	2.3	2,655.0	7.64
C3	Medium	8.4	29,205.0	7.42
C4	Medium	8.7	1,140.0	7.72
C5	Medium	8.6	240.0	7.87
C6	Medium	8.8	765.0	7.76
C7	Medium	8.6	765.0	7.76
C8	Medium	8.8	300.0	7.85
C9	Medium	9.4	1,530.0	7.70
C10	Medium	3.7	765.0	7.76
C11	Medium	7.5	1,530.0	7.70
C12	Low	8.3	3,825.0	7.61
C13	High	8.5	765.0	7.76
C14	High	8.5	1,155.0	7.72
C15	High	8.1	2,805.0	7.64
C16	Transactional	8.0	9,945.0	7.52

Using the above formula I can calculate the demand curve in the last column.

However, if there's a blank value in between I get a 'Value Error' as shown below.

Customers (Column L)	Category (Column M)	Avg. NPP (Column N)	Qty in KG (Column O)	Demand Curve (Column P) -Formula
C1	Low	7.1	765.0	#VALUE!​
C2	Low			#VALUE!​
C3	Medium	8.4	29,205.0	#VALUE!​
C4	Medium	8.7	1,140.0	#VALUE!​
C5	Medium	8.6	240.0	#VALUE!​
C6	Medium	8.8	765.0	#VALUE!​
C7	Medium	8.6	765.0	#VALUE!​
C8	Medium	8.8	300.0	#VALUE!​
C9	Medium	9.4	1,530.0	#VALUE!​
C10	Medium	3.7	765.0	#VALUE!​
C11	Medium	7.5	1,530.0	#VALUE!​
C12	Low	8.3	3,825.0	#VALUE!​
C13	High	8.5	765.0	#VALUE!​
C14	High	8.5	1,155.0	#VALUE!​
C15	High	8.1	2,805.0	#VALUE!​
C16	Transactional	8.0	9,945.0	#VALUE!​

Can anyone please help me, how I can fix this issue or make this formula work in excel so that it can give me values despite there are blank values.

Thanks in advance

 

[Eric W]

Welcome to the MrExcel forum!

The values I get are not exactly what your example shows, but try this:

Book1
	L	M	N	O	P
3	Customers (Column L)	Category (Column M)	Avg. NPP (Column N)	Qty in KG (Column O)	Demand Curve (Column P) -Formula
4	C1	Low	7.1	765	8.035343817
5	C2	Low
6	C3	Medium	8.4	29,205.00	8.220993456
7	C4	Medium	8.7	1,140.00	8.055676769
8	C5	Medium	8.6	240	7.976255693
9	C6	Medium	8.8	765	8.035343817
10	C7	Medium	8.6	765	8.035343817
11	C8	Medium	8.8	300	7.98762967
12	C9	Medium	9.4	1,530.00	8.070674617
13	C10	Medium	3.7	765	8.035343817
14	C11	Medium	7.5	1,530.00	8.070674617
15	C12	Low	8.3	3,825.00	8.117379394
16	C13	High	8.5	765	8.035343817
17	C14	High	8.5	1,155.00	8.056343074
18	C15	High	8.1	2,805.00	8.101570311
19	C16	Transactional	8	9,945.00	8.166083312
Sheet1

Cell Formulas
Range	Formula
P4:P19	P4	=IFERROR(LET(a,FILTER($N$4:$O$19,($N$4:$N$19<>"")*($O$4:$O$19<>"")),b,LINEST(INDEX(a,0,1),LN(INDEX(a,0,2)),1,0),INDEX(b,1)*LN(O4)+INDEX(b,2)),"")

 

[93arpan]

Hi Eric,

Thank you very much for a prompt response. You're a legend, this formula totally works and I get the correct values (tested 5-6 times).

 

[Eric W]

Happy to help!