How Do I Do This In Excel......

[ToraToraTora]

I have a column with some figures not every line contains a number, those that don't are blank. Now I would like a total at the bottom that subtracts the figure nearest the bottom from the figure nearest the top. eg if colum A contains 100, 98, blank, 56, 42,blank,blank, 38. The I want the total to be 100-38=62. Thanks

[mccfluff]

=a1-a26 (or whatever number cell 38 is in)

[mccfluff]

sorry type that formula into the cell you want the total in.

[chelle7272]

=sum(a1:a30) or whatever the cells are

in another cell =100-a30

[chelle7272]

sorry yes, same for mine!

[tamborine]

Subtract figures must have parenthesis ( ) either side.

[chelle7272]

Yes that's also true...I would usually type =100- and then click the cell so it finishes the sum automatically for me (that's my excuse for forgetting the parenthesis....)

thanks fro the reminder tamborine

[Calibax]

Are they always in descending order? Do you want to take the lowest value from the highest? If so you can use:
=MAXA(A1:A20)-MINA(A1:A20)

[zoe76]

=sum(cell no containing '100' - cell no containing 38)

eg =sum(A1-A6)

[ToraToraTora]

no I don't want to add up the column, no they are are not always in descending order. basically I want to subtract the last value in the column from the first. The number of rows is constantly changing so lines can be added at the end or in the middle. eg =A1-An would work now but later If lines are added after line N then it would not.

[New Judge]

Is the position of the top number variable as well? If it is this may not work. If it is fixed, try this. In the cell where you want the answer eneter this formula:

=A1-(INDEX(A1:A9999,MATCH(9.9999E+307,A1:A9999)))

This should give you the result of A1 minus the last cell with an entry in the range A1:A9999.

Try it for starters anyway.

[ToraToraTora]

thacks judge, that created a circular reference, I don't understand the significance of 9.9999 x 10 to the power of 307.

[THECORBYLOON]

That number is the largest number that excel can cope with and the formula looks for the last number that is less than or equal to that number.

[THECORBYLOON]

This link shows how to find the first and last numbers in a column http://www.ozgrid.com/forum/showthread.php?t=43195

[ToraToraTora]

right, got it working, judge/TCL many thanks, that's perfect

[New Judge]

Should have said that the formula needs to be somewhere other than between A1 and A9999 (say, B1 for instance). That's probably why you got the "circular reference".

Anyway, glad it finally worked.

[ToraToraTora]

yes I realised that after a bit of reasoning! Thanks