LINEST Question

[Jujubee18]

Hi,

I have data of product revenues from various states for 6 quarters, and my goal is to predict revenues for the next 4 quarters.

So currently, I have a Sales tab with a table that is structured like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2014 Q4[/TD]
[TD]2015 Q1[/TD]
[TD]2015 Q2[/TD]
[TD]2015 Q3[/TD]
[TD]2015 Q4[/TD]
[TD]2016 Q1[/TD]
[/TR]
[TR]
[TD]Pennsylvania[/TD]
[TD]500[/TD]
[TD]490[/TD]
[TD]520[/TD]
[TD]600[/TD]
[TD]640[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]Other states[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

And so I created a line graph of this data, and got a trendline.

To get the trendline equation into the cells (so that I can use the equation for the projections), I used the LINEST function to get the slope and intercept for this linear trendline.

Problem is, with the LINEST function, I need x-values that are numbers (like 1, 2, 3, etc) instead of the current ones (2014 Q4, 2015 Q1, 2015 Q2, etc.)

So, I created another row with the x values, and used that row for my LINEST calculations.

My question is, as new quarterly data comes in, I'd like to update the table and graph, and then use the new trendline slopes and intercepts to adjust the projections for the next 4 periods.

So when I add new data points, the graph will change because the points are from the table, but the LINEST calculations won't change, so I'd have to manually change the y-val and x-val part of the LINEST functions each time.

Since this spreadsheet may be used by others, I'd like to make it as less complex as possible, so I was wondering whether there would be a quick way to adjust the slope/intercept with new data using the LINEST function, or if there is an easier way to go about projecting the revenues (like using some other function)?

Thanks!

*Also, I'm using Excel 2010

 

[shg]

LINEST doesn't require any x values. If you don't specify any, it assumes the categorical series {1,2,3,...}.

 

[Jujubee18]

Oh right.

For another one of the states, I used a log trendline, so I used x values in the LINEST function: LINEST(B2:G2, LN(B3:G3), TRUE, FALSE).

For log trendlines, is there a way to get excel to also assume the categorical series?

 

[shg]

No convenient way I know of.

=LINEST(B2:G2, LN(TRANSPOSE(ROW(INDIRECT("1:" & COLUMNS(B2:G2))))))

 

[Jujubee18]

Okay, thanks!

 

[shg]

You're welcome.