# Excel formula for brain gone on holiday

Belfix 12:32 11 Oct 12
Locked

Hi..very basic but I think my brain has done a runner. If I have a basic list spread sheet showing various transaction columns A=date, B=description, C=income, D=expense and E=current balance, that is previous line total+income-expense, how do I get a fixed cell eg \$z\$100 equal to the latest line E cell regards

Belfix 12:35 11 Oct 12

sorry..I meant to say how do I get the fixed cell to always use the latest E column figure automatically

Belfix 12:35 11 Oct 12

sorry..I meant to say how do I get the fixed cell to always use the latest E column figure automatically

Woolwell 12:57 11 Oct 12

Not quite sure what you mean. Do you want Z100 to equal E1 and then E2, etc? Or do you want the cell to always ref column E in which case \$E.

Picklefactory 13:13 11 Oct 12

If you want to use cell range Z100 to equal the value of the last occupied cell in column E then in Z100 place

=INDEX(E:E,MATCH(9.99999999999999E+307,E:E))

Belfix 14:06 25 Oct 12

Thanks Picklefactory...knew it was there somewhere - certainly works for me!

Belfix 14:07 25 Oct 12

Thanks Picklefactory...knew it was there somewhere - certainly works for me!

Alan2 18:22 25 Oct 12

Belfix, try one of these:

Assuming your income (col C) is shown positive and expenditure (col D) is also shown positive and the range of rows - not necessarily filled - runs from 5 to 101 then for cell Z100 the formula would be:

=sum(c5:c101)-sum(d5:d101)

Alternatively if the expenditure(col D)is shown negative then the formula in Z100 would be:

=sum(c5:d101)

This thread is now locked and can not be replied to.

Hands-on: Acer Predator Triton 700 review

D&AD Awards 2017: see the best design, advertising, illustration, animation and VR of the past year

How to lose weight with an Apple Watch