Microsoft Windows xp inside out, second...

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?

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

Thanks a lot Humar.

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

Much Better. Thanks a lot.

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History