Formula for col. F

Microsoft Windows xp inside out, second...
July 23, 2010 at 00:45:53
Specs: Windows XP, pentium 4 3.00Ghz. 1gb ram
I have a large number of numbers as shown below in col. C, D and E.

  B	C	 D	  E	   F
10			371.630
11		5.42	367.370	 366.210
12		6	362.670	 361.370
13		5.9	359.010	 356.770
14		4	356.860	 355.010
15	1.54			 355.320
16		2.73	354.570	 354.130
17	1.62			 352.950
18		3.41	351.460	 351.160
19	1.57			 349.890
20		2.11	350.050	 349.350
21	3.21			 346.840
22		3.76	346.990	 346.290
23	1.23			 345.760
24	1.92			 345.070
25		4.22	346.990	 342.770
26	1.4			 345.590
27		0.15	349.060	 346.840
28	1.2			 347.860
29		1.35	348.800	 347.710
30	1.72			 347.080
31	2.31			 346.490
32	3.2			 345.600
33		1.6	348.250	 347.200
34


Here, F11=E10-D11
F15=E14-C15
F32=E29-C32

Can someone provide a formula for col. F so that when i pull it down under Col.F would calculate rest of the datas as shown in Col.F?


See More: Formula for col. F

Report •

#1
July 23, 2010 at 06:25:26
Hi,

This formula worked on the sample data you supplied. Enter this in cell F11:
=IF(C11<>"",IF(E10<>"",E10-C11,IF(E9<>"",E9-C11,IF(E8<>"",E8-C11,"Error"))),IF(E10<>"",E10-D11,IF(E9<>"",E9-D11,IF(E8<>"",E8-D11,IF(E7<>"",E7-D11,"Error")))))
Then drag it down in column F.

If your data is more variable than you have shown, then this formula would need to be extended, to cope with more empty cells.

The formula basically works by testing column C and taking one of two paths depending on whether there is any data in column C on that row.

Both paths then test cells in column E in rows above, to find a cell containing data, and when a cell containing data is found the relevant subtraction is performed.

As you can see from the formula it only tests four rows above for data. If you have some cases where the gap in column E is more than 4 rows the formula will fail.

If you have gaps of more than 4 rows you could just add additional tests to find the last row above that contains data.

Regards


Report •

#2
July 23, 2010 at 22:15:01
Thanks a lot Humar.

Report •

#3
July 24, 2010 at 04:04:51
Hi,

You're welcome.

Here is a simpler version of the formula:
=IF(E15<>"",E15,IF(E14<>"",E14,IF(E13<>"",E13,IF(E12<>"",E12,"Error"))))-IF(C16<>"",C16,D16)

This would be easier to extend for more empty rows between values in column E.

Regards


Report •

Related Solutions

#4
July 24, 2010 at 21:42:15
Much Better. Thanks a lot.

Report •

Ask Question