# I want to find the missing values..but i don't know how ??!!

#### NOURALI

##### New Member
I need tp find the missing values y1 and y2
• I have 5 sources of seed (1,2,3,4,5) and four grounds(I,II,III,IIV)
 I II III IV 1 157 162 154 165 2 163 173 169 171 3 y1 161 170 167 4 178 173 180 182 5 165 175 y2 181

<tbody>
</tbody>
So can you help to know how by using excel or any other programe??
thanx

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### BengalMagic

##### Banned user
I need tp find the missing values y1 and y2
• I have 5 sources of seed (1,2,3,4,5) and four grounds(I,II,III,IIV)
 I II III IV 1 157 162 154 165 2 163 173 169 171 3 y1 161 170 167 4 178 173 180 182 5 165 175 y2 181

<tbody>
</tbody>
So can you help to know how by using excel or any other programe??
thanx

It must be April 1st already in some parts of the world

#### NOURALI

##### New Member
I'm not kidding I just want to find way to solve it...but anyway thanx for your kind words

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

I think the point being made was that you have provided no information at all about what results would be expected or how to work out those results.
Is there some pattern or logic we should know about?

#### BengalMagic

##### Banned user
I apologise if my original response appeared somewhat flippant.

However, I've noticed a lot of this lately; I call it "forum noise", i.e meaningless posts intended to distract solvers away from genuine questions.

I wonder how many of these posts are from members of rival forums who have a hidden agenda?
With the unemployment rate so high and school holidays in full swing, I expect to see a lot more posts like the OP's.

#### Peter_SSs

##### MrExcel MVP, Moderator
However, I've noticed a lot of this lately; I call it "forum noise", i.e meaningless posts intended to distract solvers away from genuine questions.

I wonder how many of these posts are from members of rival forums who have a hidden agenda?
With the unemployment rate so high and school holidays in full swing, I expect to see a lot more posts like the OP's.
If you really believe any of that then surely you'd have to put both your own posts in this thread in the same category.
You are standing on thin ice.

Last edited:

#### mirabeau

##### Banned user
I need tp find the missing values y1 and y2
• I have 5 sources of seed (1,2,3,4,5) and four grounds(I,II,III,IIV)
 I II III IV 1 157 162 154 165 2 163 173 169 171 3 y1 161 170 167 4 178 173 180 182 5 165 175 y2 181

<tbody>
</tbody>
So can you help to know how by using excel or any other programe??
thanx
Nourali,

The problem you pose is very reasonable. It's a fairly standard statistical problem. And yes, you do give enough information to get the results you request.

Set up the model

y(i,j)= S(i) + G(j) + random error.

You give 18 outcome values for the y(i,j) (because 2 are unknown) and have 9 parameters to determine, viz. the "effects" of the 5 seed sources and of the 4 types of ground.

This model has a loss of one degree of freedom because the parameters have one linear constraint. This is generally easily resolved by putting one of them equal to zero.

So available degrees of freedom in the model are 18 - 9 - 1.

This is >0 so the effects can be estimated and your missing values predicted by several statistical techniques.

Sometimes this type of model is extended by including an interactive effect, whereby the model becomes
y(i,j)= S(i) + G(j) + S(i)*G(j) + random error
somewhat more complex and usually not done without good reason for supposing the interactive effect may be real.

I've done this sort of thing in the past by variants of the standard regression (Excel's LINEST etc) and if you get your data layout right it's actually quite easy.

But I won't do any more without knowing just why you want to do this, what is your statistical background, and what other sources have you investigated for a possible solution?

#### NOURALI

##### New Member
This is the hole question that my teacher give to me
For different </SPAN></SPAN>kinds of</SPAN> sources</SPAN> in</SPAN> the</SPAN> following table</SPAN>, </SPAN>the</SPAN> average</SPAN> plant height</SPAN> data</SPAN> of</SPAN> the</SPAN> different</SPAN> test</SPAN> ground</SPAN> and rape seed</SPAN> flowering period</SPAN>, </SPAN>assuming</SPAN> the</SPAN> No. </SPAN>Ⅰ</SPAN> test</SPAN> ground</SPAN> No. 3</SPAN> seed source</SPAN>and </SPAN>Ⅲ</SPAN> No.</SPAN> test</SPAN> ground</SPAN> No. 5</SPAN> seed source</SPAN> rape</SPAN> are missing because it was</SPAN> some</SPAN> kind</SPAN> of</SPAN> vandalism</SPAN> but have not secured </SPAN>expected values </SPAN>​​</SPAN>and</SPAN> complete</SPAN> the</SPAN> rest</SPAN> of</SPAN> the</SPAN> data</SPAN> in the table below</SPAN>. </SPAN>Try</SPAN> to</SPAN> compensate</SPAN> for</SPAN> these</SPAN> two</SPAN> dropout</SPAN> data</SPAN>, </SPAN>and to make</SPAN> the</SPAN> analysis of variance</SPAN>.</SPAN>

Different </SPAN>tests</SPAN> on the ground</SPAN> of different</SPAN> the</SPAN> rapeseed</SPAN> flowering</SPAN> period</SPAN> average</SPAN> plant height</SPAN> (</SPAN>cm)</SPAN>

 Ⅰ Ⅱ Ⅲ Ⅳ 1 157 162 154 164 2 163 173 169 171 3 y1 161 170 167 4 178 173 180 182 5 165 175 y2 181

<TBODY>
</TBODY>

So is this enough information and please i need to learn how to do it
Thanx and all respect

#### mirabeau

##### Banned user
Nourali,

OK. But I don't want to be too involved in doing your projects/assignments, since you should be doing the learning.

Here's a data layout that'll enable you to get your missing values by doing a standard regression. There's a regression in the Excel Data Analysis add-ins, or you can use LINEST, or if all else fails maybe I could give you one.

The estimates of the missing values are the coefficients of y1 and y2 in the regression output. Standard errors and other statistics are also generated, so you can calculate fiducial limits (roughly, confidence intervals) for the missing values, etc. if you wish.

Output (missing values)
 y1 160.147 y2 174.238

<tbody>
</tbody>

<tbody>
</tbody>

Data layout
 Y I II III IV 1 2 3 4 y1 y2 157 1 0 0 0 1 0 0 0 0 0 163 1 0 0 0 0 1 0 0 0 0 0 1 0 0 0 0 0 1 0 -1 0 178 1 0 0 0 0 0 0 1 0 0 165 1 0 0 0 0 0 0 0 0 0 162 0 1 0 0 1 0 0 0 0 0 173 0 1 0 0 0 1 0 0 0 0 161 0 1 0 0 0 0 1 0 0 0 173 0 1 0 0 0 0 0 1 0 0 175 0 1 0 0 0 0 0 0 0 0 154 0 0 1 0 1 0 0 0 0 0 169 0 0 1 0 0 1 0 0 0 0 170 0 0 1 0 0 0 1 0 0 0 180 0 0 1 0 0 0 0 1 0 0 0 0 0 1 0 0 0 0 0 0 -1 165 0 0 0 1 1 0 0 0 0 0 171 0 0 0 1 0 1 0 0 0 0 167 0 0 0 1 0 0 1 0 0 0 182 0 0 0 1 0 0 0 1 0 0 181 0 0 0 1 0 0 0 0 0 0

<tbody>
</tbody>

And a minor correction to my original answer to your post. A degree of freedom should be added for each linear restriction, not subtracted, so the DF in your problem is 10.

Study the data layout table to see the reason for it, and that should assist you with your project.

Last edited:

#### NOURALI

##### New Member
Thank you for your cooperation I hope you send me more details of the solution, I am a student and I want to learn more
My questions are why you introduced 4 seed sources while the given are 5 sources , and why replaced unknown values ​​to -1
All respcet to you Sir

Replies
4
Views
1K
Replies
5
Views
189
Replies
4
Views
679
Replies
3
Views
124
Replies
3
Views
210

1,195,903
Messages
6,012,206
Members
441,681
Latest member
AkosiJessica

### 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.

### Which adblocker are you using?

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

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