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

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