Donate SIGN UP

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

Avatar Image
ToraToraTora | 12:24 Wed 28th May 2014 | Technology
16 Answers
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
Gravatar

Answers

1 to 16 of 16rss feed

Best Answer

No best answer has yet been selected by ToraToraTora. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.
=a1-a26 (or whatever number cell 38 is in)
sorry type that formula into the cell you want the total in.
=sum(a1:a30) or whatever the cells are

in another cell =100-a30
sorry yes, same for mine!
Subtract figures must have parenthesis ( ) either side.
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
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)
=sum(cell no containing '100' - cell no containing 38)

eg =sum(A1-A6)
Question Author
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.
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.
Question Author
thacks judge, that created a circular reference, I don't understand the significance of 9.9999 x 10 to the power of 307.
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.
This link shows how to find the first and last numbers in a column http://www.ozgrid.com/forum/showthread.php?t=43195
Question Author
right, got it working, judge/TCL many thanks, that's perfect
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.
Question Author
yes I realised that after a bit of reasoning! Thanks

1 to 16 of 16rss feed

Do you know the answer?

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

Answer Question >>