Using a Linear Regression on Data that has 2 or 3 data sets

[JeffGrant]

Hi All,

May I say thanks for all of your help up front. The knowledge base in this forum is astounding.

Anyway, I have an issue that I don't know how to solve.

I have data set, that is generated externally. It looks like this:

Horse	Distance (m)	Weight (kg)	Time (min)	Time (s)	Speed (m/s)
Blow Dart	1000	57	00:56.77	56.77	17.61493747
Blow Dart	1000	56	00:56.79	56.79	17.61
Blow Dart	900	-	00:54.29	54.29	16.58
Hard To Excel	1100	57	01:04.80	64.80	16.98
Hard To Excel	1100	56	01:03.60	63.60	17.30
The Sherpa	850	-	00:51.97	51.97	16.36
The Sherpa	900	-	00:54.80	54.80	16.42
Aero Nautica	1100	57	01:04.80	64.80	16.98
Aero Nautica	1000	-	01:01.20	61.20	16.34
Diggers Reach	1000	54	00:57.27	57.27	17.46
Diggers Reach	1000	-	01:02.80	62.80	15.92
Molecular	1000	55	00:56.77	56.77	17.61
Molecular	900	-	00:55.29	55.29	16.28
One Penny	1000	55	00:56.77	56.77	17.61
One Penny	1000	-	00:59.26	59.26	16.87
Sebrini	800	-	00:51.10	51.10	15.66
Sebrini	800	-	00:50.42	50.42	15.87
Sebrini	800	-	00:50.83	50.83	15.74
Secia	1100	54	01:05.26	65.26	16.86
Secia	1100	55	01:05.85	65.85	16.70
Secia	900	55	00:52.33	52.33	17.20
Shemakesastatement	800	-	00:49.79	49.79	16.07
Shemakesastatement	800	-	00:50.52	50.52	15.84
Shemakesastatement	800	-	00:50.76	50.76	15.76

I would like to run a linear regression, using Weight as the x axis and Speed as the y axis. No problem there if I do it one by one.

The problem is that sometimes, there are 2 data sets and other times there are 3 data sets per horse. I can not control that part of the input.

Because the data sets vary between 2 and 3 on a horse by horse basis, I have no idea how to code for the variation.

I have tried transposing the data so that everything was all on the same line, but it wasn't long before the data was in the wrong equation.

Is this something that would be better handled via VBA to transpose the data, where it could be programmed to always have 3 data sets?

Any suggestions would be exceptionally appreciated.

Regards

Jeff

 

[Zot]

Without using VBA, you can actually get the linear regression equation easily using Excel. Probably less than a minute

 

[JeffGrant]

Thanks Zot. My Apologies, I probably wasn't very clear.

In the above example entire data set, I would be looking to apply 10 linear regressions, not one. That would be one linear regression per horse, not 1 for the entire data set.

The issue is that:
Blow Dart has 3 sub data sets
Hard To Excel has 2 sub data sets
The Shepa has 2 etc.

In fact, The Sherpa, Sebrinia and Shemakesastatement, would not have an LR at all because all of the x axis are zero.

If possible, without using VBA, how can I get excel to differentiate between those sub data sets with 3 data points from those that have 2 data points?

But if I have to use VBA, I will.

Is this an example where Pivot Tables would be good? I am not all that familiar with pivot tables.

 

[Zot]

So, I guess you want to extract the data belong to Blow Dart, Hard to Excel into a table and do linear regression for each. Here is the the sheet I created to extract those date based on drop down selection in range I2

I also make data as table. I think easier that way. Each time you add more data, no need to change range in formula.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Horse	Distance (m)	Weight (kg)	Time (min)	Time (s)	Speed (m/s)			Horse Selection			Horse	Weight	Speed
2	Blow Dart	1000	57	0.00065706	56.77	17.61493747			Blow Dart			Blow Dart	57	17.61493747
3	Blow Dart	1000	56	0.000657292	56.79	17.60873393						Blow Dart	56	17.60873393
4	Blow Dart	900	-	0.000628356	54.29	16.57763861						Blow Dart	-	16.57763861
5	Hard To Excel	1100	57	0.00075	64.8	16.97530864			Blow Dart
6	Hard To Excel	1100	56	0.000736111	63.6	17.29559748			Hard To Excel
7	The Sherpa	850	-	0.000601505	51.97	16.35558976			The Sherpa
8	The Sherpa	900	-	0.000634259	54.8	16.42335766			Aero Nautica
9	Aero Nautica	1100	57	0.00075	64.8	16.97530864			Diggers Reach
10	Aero Nautica	1000	-	0.000708333	61.2	16.33986928			Molecular
11	Diggers Reach	1000	54	0.000662847	57.27	17.46114894			One Penny
12	Diggers Reach	1000	-	0.000726852	62.8	15.92356688			Sebrini
13	Molecular	1000	55	0.00065706	56.77	17.61493747			Secia
14	Molecular	900	-	0.000639931	55.29	16.27780792			Shemakesastatement
15	One Penny	1000	55	0.00065706	56.77	17.61493747
16	One Penny	1000	-	0.00068588	59.26	16.87478907
17	Sebrini	800	-	0.000591435	51.1	15.6555773
18	Sebrini	800	-	0.000583565	50.42	15.86671956
19	Sebrini	800	-	0.00058831	50.83	15.73873697
20	Secia	1100	54	0.000755324	65.26	16.85565431
21	Secia	1100	55	0.000762153	65.85	16.70463174
22	Secia	900	55	0.000605671	52.33	17.19854768
23	Shemakesastatement	800	-	0.000576273	49.79	16.06748343
24	Shemakesastatement	800	-	0.000584722	50.52	15.83531275
25	Shemakesastatement	800	-	0.0005875	50.76	15.76044129
Sheet1

Cell Formulas
Range	Formula
L2:L25	L2	=IFERROR(INDEX(Table1[Horse], SMALL(IF($I$2=Table1[Horse], ROW(Table1[Horse])-ROW($I$2)+1), ROW(1:1))),"" )
M2:M25	M2	=IFERROR(INDEX(Table1[Weight (kg)], SMALL(IF($I$2=Table1[Horse], ROW(Table1[Horse])-ROW($I$2)+1), ROW(1:1))),"" )
N2:N25	N2	=IFERROR(INDEX(Table1[Speed (m/s)], SMALL(IF($I$2=Table1[Horse], ROW(Table1[Horse])-ROW($I$2)+1), ROW(1:1))),"" )
I5:I20	I5	=IFERROR(INDEX(Table1[Horse],MATCH(0,INDEX(COUNTIF($I$4:I4,Table1[Horse]),),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
I2	List	=OFFSET($I$5,,,COUNTIF($I$5:$I$20,"?*"))

 

[JeffGrant]

Thanks for your help, i will look at it later tonight.

 

[JeffGrant]

Thanks for your help. the solution provided by Zot was not quite right. Sorry mate.

I did however, find a solution at How to quickly transpose group of rows to column in Excel?, that I can use to solve my problem.

 

[Zot]

Thanks for your help. the solution provided by Zot was not quite right. Sorry mate.

I did however, find a solution at How to quickly transpose group of rows to column in Excel?, that I can use to solve my problem.

Good to hear you find solution. Actually I thought you want to find linear regression for each one of the horse. So, need to create group for each