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

Best Black Friday 2014 tech deals: Get bargains on smartphones, tablets, laptops and more

IDG UK Sites

What the Internet of Things will look like in 2015: homes will get smarter, people might get fitter

IDG UK Sites

See how Trunk's animated ad helped Ade Edmondson plug The Car Buying Service

IDG UK Sites

Yosemite tips: Complete Guide to OS X Yosemite