Excel formula for brain gone on holiday

  Belfix 12:32 PM 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 PM 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 PM 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 PM 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 PM 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 PM 25 Oct 12

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

  Belfix 14:07 PM 25 Oct 12

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

  Alan2 18:22 PM 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)

Advertisement

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

New Moto G 2015 confirmed: Motorola Moto G UK release date, price and specification details - New…

Why Vodafone’s frequency choice should interest you: The benefits of 800MHz

Digital visionaries lined up for dConstruct

Apple Pay UK launch guide and participating UK banks: HSBC, First Direct finally support Apple Pay