Excel LINEST Formula with non-adjacent cells

[MattGreer]

I'm trying to use the LINEST formula with a set of data that isn't in contiguous cells. The reason being is the data is easier to enter into the spreadsheet this way. There are pairs of values that go together, essentially. I'm very open to setting this whole thing up differently, if someone has a suggestion.

So the data table is set up like this:

[tt]Name L1H L1D L2H L2D L10H L10D ... ... mH bH mD bD
--------------------------------------------------------------------------------------
Name val val val val val val val val slopeH y-interceptH slopeD y-interceptD
[/tt]

(I took out all the columns for clarity)

So the LINEST formula needs to grab L1H, L2H, L3H, and so on.

I've tried using commas to separate the non-adjacent cells but of course that didn't work.

Can this be done? I want to leave the data in rows as shown so I can use a lookup function in *another* table to be able to interpolate between the values. If someone has a suggestion for a different setup I'm all ears. There are 10 H values and 10 D values for each Name. The other/user table looks like this:

[tt]
Name Level Health Damage
---------------------------------------
Name 1 [user entered] val val
[/tt]

There's more data in the 2nd table, but this is the essence of how I'm using it.

Thanks for your help! (I don't know why the monospaced font isn't working in preview...)


Thanks!!


Matt

 

[SkipVought]

Hi,

Why can't you lookup in a 2-column x-y table? Then you'd have an x-range and a y-range for your LINEST function.

Maybe I'm missing something.

If you're saying that you have multiple groups of x-y data based on Name1 ,Name2 etc, you can use the OFFSET() function (5 arguments) to pick off sub ranges based on Name1 ,Name2 etc. So in that case you'd have a 3-column table, with a column for Name.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

SKIP! Always good to see a reply from you.

So this is just a little helper spreadsheet for this game I'm playing (and trying to show my daughter the POWER of Excel, heh). Each row represents a critter; Name is just the name of the critter. Each critter has a unique value for health and damage depending on the level of the critter. I was doing this one by one and replacing the data with a little table like this:

Table Name: Stats
[tt]
Level Health Damage
1 2000 625
2 2400 750
5 3600 1125
10 5600 1750
15 7600 2375
20 9600 3000
25 11600 3625
30 13600 4250
Health (slope) (y-intercept)
Damage (slope) (y-intercept)
[/tt]

So in the Main Table, that's where I have additional data unique to the critter. But you can enter it's actual level and it's current health and damage capabilities would be calculated based off the LINEST calc in the Stats table.

Hope this makes sense! I'm pulling the data manually off of a game-based website; the data entry goes easier if I enter the numbers, alternating, from left to right. I suppose it wouldn't be that big of a deal to just enter the data into Excel and then re-arrange it before I put it into the stats table. I was just wondering if there was a trick to using non-adjacent cells in the LINEST function.

Thanks!!


Matt

 

[SkipVought]

Bottom line: I know of no way to define a non-contiguous range for LINEST.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

Gotcha. Thank you for trying!

Thanks!!


Matt