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 January sales 2015 UK tech deals LIVE: Best New Year bargains and savings on phones, tablets,...

IDG UK Sites

Chromebooks: ready for the prime time (but not for everybody)

IDG UK Sites

Best Photoshop Tutorials 2014: 10 inspiring step-by-step guides to creating amazing art,...

IDG UK Sites

Apple TV expert tips: get US Apple TV content, watch Google Play, use multiple Apple IDs and more