Tech Helproom
It's free to register, to post a question or to start / join a discussion
Excel formula for brain gone on holiday
Likes # 0
Posted October 11, 2012 at 12:32PM
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
Likes # 0
Posted October 11, 2012 at 12:35PM
sorry..I meant to say how do I get the fixed cell to always use the latest E column figure automatically
Likes # 0
Posted October 11, 2012 at 12:35PM
sorry..I meant to say how do I get the fixed cell to always use the latest E column figure automatically
Likes # 0
Posted October 11, 2012 at 12:57PM
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.
Likes # 0
Posted October 11, 2012 at 1:13PM
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))
Likes # 0
Posted October 25, 2012 at 2:06PM
Thanks Picklefactory...knew it was there somewhere - certainly works for me!
Likes # 0
Posted October 25, 2012 at 2:07PM
Thanks Picklefactory...knew it was there somewhere - certainly works for me!
Likes # 0
Posted October 25, 2012 at 6:22PM
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)
Reply to this topic
This thread has been locked.
Check out PC Advisor's other tech forums
Top 5 Most Popular
