We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message
 
Contact Forum Editor

Send an email to our Forum Editor:


PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Tech Helproom


It's free to register, to post a question or to start / join a discussion


 

Excel formula for brain gone on holiday


Belfix

Likes # 0

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

Like this post
Belfix

Likes # 0

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

Like this post
Belfix

Likes # 0

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

Like this post
Woolwell

Likes # 0

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.

Like this post
Picklefactory

Likes # 0

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))

Like this post
Belfix

Likes # 0

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

Like this post
Belfix

Likes # 0

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

Like this post
Alan2

Likes # 0

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)

Like this post

Reply to this topic

This thread has been locked.



IDG UK Sites

Amazon 3D smartphone release date, price and spec: The hologram phone?

IDG UK Sites

iPhone 5s review: why the iPhone 5s is still the best phone you can buy in 2014

IDG UK Sites

Passwords don't work: here's four ways to fix them

IDG UK Sites

Developers get access to more Sony camera features