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

Top 5 Android tips and tricks for smartphones and tablets

IDG UK Sites

How to join Apple's OS X Beta Seed Program: Get OS X Yosemite on your Mac before public release

IDG UK Sites

Why the BBC iPlayer outage was caused by a DDoS attack: Topsy and Tim isn't *that* popular

IDG UK Sites

BBC using Glasgow 2014 Commonwealth Games to trial 4K/UHD, pan-around video, augmented video and...