LINEST Array for a specific sub segment

[CJBAMBAM]

Hi guys,

I am using LINEST Array to calculate price elasticity and its related coefficients. For a multi-product portfolio.

Y values: Monthly Price elasticity for each SKU
X Values: Coefficitents for 5 items:

• Season (4 separate coefficients)
• Relative price level vs substitute products

Right now, I am using a formula as follows: {=LINEST(Y Values,X Values^{1,2,3,4,5},TRUE,FALSE)}

As the data I am analyzing has a number of SKUs. I want to only pull the relevant X (coefficient) and Y (monthly elasticity) values for each product. Right now I am doing this manually laugh, but would like to automate the process. How can I do this?

Thanks very much in advance. I have not been able to replicate other posts that are using LINEST Array's with conditions to replicate this process.

 

[Domenic]

Let's assume that we have the following data...

A1:C11

[TABLE="width: 225"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Product[/TD]
[TD="class: xl65, width: 64"]Y Values[/TD]
[TD="class: xl65, width: 64"]X Values[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]3100[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]4500[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]4400[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]5400[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]7500[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]8100[/TD]
[/TR]
</tbody>[/TABLE]
To perform a LINEST calculation based on Product B, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(LINEST(INDEX(C2:C11,N(IF(1,MODE.MULT(IF(A2:A11="B",(ROW(A2:A11)-ROW(A2)+1)*{1,1}))))),INDEX(B2:B11,N(IF(1,MODE.MULT(IF(A2:A11="B",(ROW(A2:A11)-ROW(A2)+1)*{1,1}))))))*{9,1})

For earlier versions of Excel, try the following instead that also needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(LINEST(N(OFFSET(C2:C11,SMALL(IF(A2:A11="B",ROW(A2:A11)-ROW(A2)),ROW(INDIRECT("1:"&COUNTIF(A2:A11,"B")))),0,1)),N(OFFSET(B2:B11,SMALL(IF(A2:A11="B",ROW(A2:A11)-ROW(A2)),ROW(INDIRECT("1:"&COUNTIF(A2:A11,"B")))),0,1)))*{9,1})

Hope this helps!

 

[CJBAMBAM]

Flawless, thanks Domenic!

Due to my data set the formula is ~10x longer, but that worked great.

Chris