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

JeffGrant

Well-known Member
Joined
Apr 7, 2021
Messages
558
Office Version
  1. 365
Platform
  1. Windows
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:
HorseDistance (m)Weight (kg)Time (min)Time (s)Speed (m/s)
Blow Dart10005700:56.7756.7717.61493747
Blow Dart10005600:56.7956.7917.61
Blow Dart900-00:54.2954.2916.58
Hard To Excel11005701:04.8064.8016.98
Hard To Excel11005601:03.6063.6017.30
The Sherpa850-00:51.9751.9716.36
The Sherpa900-00:54.8054.8016.42
Aero Nautica11005701:04.8064.8016.98
Aero Nautica1000-01:01.2061.2016.34
Diggers Reach10005400:57.2757.2717.46
Diggers Reach1000-01:02.8062.8015.92
Molecular10005500:56.7756.7717.61
Molecular900-00:55.2955.2916.28
One Penny10005500:56.7756.7717.61
One Penny1000-00:59.2659.2616.87
Sebrini800-00:51.1051.1015.66
Sebrini800-00:50.4250.4215.87
Sebrini800-00:50.8350.8315.74
Secia11005401:05.2665.2616.86
Secia11005501:05.8565.8516.70
Secia9005500:52.3352.3317.20
Shemakesastatement800-00:49.7949.7916.07
Shemakesastatement800-00:50.5250.5215.84
Shemakesastatement800-00:50.7650.7615.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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Without using VBA, you can actually get the linear regression equation easily using Excel. Probably less than a minute :)

 
Upvote 0
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.
 
Upvote 0
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
ABCDEFGHIJKLMN
1HorseDistance (m)Weight (kg)Time (min)Time (s)Speed (m/s)Horse SelectionHorseWeightSpeed
2Blow Dart1000570.0006570656.7717.61493747Blow DartBlow Dart5717.61493747
3Blow Dart1000560.00065729256.7917.60873393Blow Dart5617.60873393
4Blow Dart900-0.00062835654.2916.57763861Blow Dart-16.57763861
5Hard To Excel1100570.0007564.816.97530864Blow Dart   
6Hard To Excel1100560.00073611163.617.29559748Hard To Excel   
7The Sherpa850-0.00060150551.9716.35558976The Sherpa   
8The Sherpa900-0.00063425954.816.42335766Aero Nautica   
9Aero Nautica1100570.0007564.816.97530864Diggers Reach   
10Aero Nautica1000-0.00070833361.216.33986928Molecular   
11Diggers Reach1000540.00066284757.2717.46114894One Penny   
12Diggers Reach1000-0.00072685262.815.92356688Sebrini   
13Molecular1000550.0006570656.7717.61493747Secia   
14Molecular900-0.00063993155.2916.27780792Shemakesastatement   
15One Penny1000550.0006570656.7717.61493747    
16One Penny1000-0.0006858859.2616.87478907    
17Sebrini800-0.00059143551.115.6555773    
18Sebrini800-0.00058356550.4215.86671956    
19Sebrini800-0.0005883150.8315.73873697    
20Secia1100540.00075532465.2616.85565431    
21Secia1100550.00076215365.8516.70463174   
22Secia900550.00060567152.3317.19854768   
23Shemakesastatement800-0.00057627349.7916.06748343   
24Shemakesastatement800-0.00058472250.5215.83531275   
25Shemakesastatement800-0.000587550.7615.76044129   
Sheet1
Cell Formulas
RangeFormula
L2:L25L2=IFERROR(INDEX(Table1[Horse], SMALL(IF($I$2=Table1[Horse], ROW(Table1[Horse])-ROW($I$2)+1), ROW(1:1))),"" )
M2:M25M2=IFERROR(INDEX(Table1[Weight (kg)], SMALL(IF($I$2=Table1[Horse], ROW(Table1[Horse])-ROW($I$2)+1), ROW(1:1))),"" )
N2:N25N2=IFERROR(INDEX(Table1[Speed (m/s)], SMALL(IF($I$2=Table1[Horse], ROW(Table1[Horse])-ROW($I$2)+1), ROW(1:1))),"" )
I5:I20I5=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
CellAllowCriteria
I2List=OFFSET($I$5,,,COUNTIF($I$5:$I$20,"?*"))
 
Upvote 0
Thanks for your help, i will look at it later tonight.
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top