Slope equation value when empty cells are included

[mvillasana]

Hello everyone!
I'm pretty new to Microsoft Excel and I'm having issues with mathematical equations applied to manually calculate the slope of a line when some of the Y values are unknown.
Being the data:

X	Y
5
10
15
30	3.69
45	3.47
60	3.26
90	2.82
120	2.38

The excel calculated slope is -0.01455, which is the same as the described value on the scientific article I'm currently working with, but when I try to calculate it by hand using the above formula, it gives me a different value until I fill the empty cells with some value (i.e. 0, 1 or any other), so the equation works when there aren't blank cells. So, my question is, which value does Microsoft excel implements on empty cells to get the right result? How does it handles empty cells to get the right results? I've tried almost every "m" equation I've found without success. Thank you very much!

 

[Cubist]

How does it handles empty cells to get the right results?

SLOPE ignores blank cells.

when I try to calculate it by hand using the above formula, it gives me a different value until I fill the empty cells with some value (i.e. 0, 1 or any other), so the equation works when there aren't blank cells.

You'd have to show what you've tried.

 

[mvillasana]

SLOPE ignores blank cells.


You'd have to show what you've tried.

I've tried calculating the average of Y's dividing by only the number of filled cells (5 instead of 8), multiplying the corresponding X values by zero or 1, calculating the average without taking into account the X values corresponding to the empty Y cells (without 5, 10 and 15), everything gives me a different result than the expected one, so there must be something wrong or different. I've also used the following formulas for the linear regression:

(multiplying by the number of cells)

(without multiplying by the number of cells)

By adding all these individual results.

Nothing seems to work. I cannot get the same -0.01455 slope value, the formulas only works when there are not empty cells on Y columns.

 

[mvillasana]

Also, the difference between the Y values is non linear, so I cannot predict its values.

 

[Cubist]

Using non-blank cells only.See if you can follow:

Book1
	A	B	C	D	E	F	G	H	I	J
1	X	Y		x-xbar	y-ybar	product	Numerator	Denominator	SLOPE	FORMULA
2	5			-39	0.566	-22.074	-75.93	5220	-0.014546	-0.014546
3	10			-24	0.346	-8.304
4	15			-9	0.136	-1.224
5	30	3.69		21	-0.304	-6.384
6	45	3.47		51	-0.744	-37.944
7	60	3.26
8	90	2.82
9	120	2.38
Sheet8

Cell Formulas
Range	Formula
D2:E6	D2	=A5:A9-AVERAGE(A5:A9)
F2:F6	F2	=D2#*E2#
G2	G2	=SUM(F2#)
H2	H2	=SUM(D2#^2)
I2	I2	=G2/H2
J2	J2	=SLOPE(B2:B9,A2:A9)
Dynamic array formulas.

 

[mvillasana]

Using non-blank cells only.See if you can follow:

Book1
	A	B	C	D	E	F	G	H	I	J
1	X	Y		x-xbar	y-ybar	product	Numerator	Denominator	SLOPE	FORMULA
2	5			-39	0.566	-22.074	-75.93	5220	-0.014546	-0.014546
3	10			-24	0.346	-8.304
4	15			-9	0.136	-1.224
5	30	3.69		21	-0.304	-6.384
6	45	3.47		51	-0.744	-37.944
7	60	3.26
8	90	2.82
9	120	2.38
Sheet8

Cell Formulas
Range	Formula
D2:E6	D2	=A5:A9-AVERAGE(A5:A9)
F2:F6	F2	=D2#*E2#
G2	G2	=SUM(F2#)
H2	H2	=SUM(D2#^2)
I2	I2	=G2/H2
J2	J2	=SLOPE(B2:B9,A2:A9)
Dynamic array formulas.

Sir, thank you VERY much! That's exactly what I was needing. You're the best.