final value in a column?

[George1981]

Hello I'm new here. I've got a question I'm struggling with in Excel. I'm using Excel to keep track of my accounts, at the moment I've got different worksheet for each account, whenever another item gets added it's appended to the bottom of the sheet. I'd like to add another worksheet that can display at a glance the balance from each account. It needs to display the final value in column E for example, but as the number of items in the spreadsheet is increasing every few days this won't be a fixed point. Any ideas?
Thanks
George

[Carim]

Hi,

Following should help

Please Login or Register  to view this content.

HTH
Carim

[George1981]

Thanks Carim that works a treat.
Cheers
George

[Carim]

Glad to see you're Glad ...

Thanks for the feedback

Carim

[Dhruva101]

Thank you Carim for this wonderful solution.

I am at loss to understand how the formula is working ?

Formula is - LOOKUP(2,1/(E1:E1000<>0),E1:E1000)

I believe, we are looking for "2" in the result coming out of the function "1/(E1:E1000<>0)" and returning the same cell value as the result ?

I tried with the following samples of data, it works fine.

Sample - A
4
2
3
1

Answer is - 1

Sample - B
1000
2
3
100

Answer is - 100


Sample - C
1000
2
0
100

Answer is - 2 (I think if it encounters 0, it does not go forward)

Sample - D
1000
4

100

Answer is - 4 (I think if it encounters spaces, it does not go forward)

The question I have is - How the formula is able to pick-up the last value of that column provided there is no "0" or spaces in that column.

Could you spend some time to explain the formula ?

[MDubbelboer]

The question I have is - How the formula is able to pick-up the last value of that column provided there is no "0" or spaces in that column.

Could you spend some time to explain the formula ?

it's explained well here:
http://www.dailydoseofexcel.com/arch...he-last-value/

* Formula: =LOOKUP(2,1/(1-ISBLANK(A1:A65535)),A1:A65535)
* This formula uses LOOKUP in its vector syntax form, with the lookup value as the first parameter, the lookup vector as second, and the result vector as the last parameter.
* The most interesting part of this formula is the lookup vector (the 2nd parameter). The formula element
1/(1-ISBLANK(A1:A65535))
in this example returns the following array
{1;1;1;1;1;1;1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;…;#DIV/0!}
that is, the ISBLANK function returns an array of TRUE (blank cell) or FALSE (non-blank cell) values. Subtracting this from 1 converts the array to an array of 0 (blank) or 1 (non-blank) values. Dividing 1 by this array then returns an array of #DIV/0 (blank) or 1 (non-blank) values.
* The LOOKUP searches for the value ‘2′ within the array (which now consists only of ‘1′ and #DIV/0 values). The LOOKUP will not find this value, so it matches the last value that is less than or equal to lookup value. This is the last ‘1′ within the range which represents the last filled cell.
* Restriction: you can’t use a complete column reference such as A:A for this type of formula.
* This type of formula can be used for a lot of similar problems using the second parameter to create a lookup vector consisting of either ‘1′ or ‘#DIV/0′ errors by setting the Boolean expression accordingly. I personally saw this usage first in a posting from Aladin Akyurek.

[tuph]

Hi,

Following should help

Please Login or Register  to view this content.

Thanks, Carim.

I loooove this forum!!! Sometimes I learn things I didn't even know I wanted to know (if that makes sense ).

[VBA Noob]

Hi,

This formula might help

=INDEX(E:E,MATCH(9.99999999999999E+307,E:E))

VBA Noob

[starguy]

Thank you Carim for this wonderful solution.

I am at loss to understand how the formula is working ?

Formula is - LOOKUP(2,1/(E1:E1000<>0),E1:E1000)

I believe, we are looking for "2" in the result coming out of the function "1/(E1:E1000<>0)" and returning the same cell value as the result ?

I tried with the following samples of data, it works fine.



The question I have is - How the formula is able to pick-up the last value of that column provided there is no "0" or spaces in that column.

Could you spend some time to explain the formula ?

try this to return last value when you have zero's or blanks as well

=LOOKUP(2,1/(E1:E1000<>""),E1:E1000)

see following link for explanation

http://www.mrexcel.com/board2/viewto...sc&start=0#top

[VBA Noob]

Hi Starguy,

Your formula will return the last entry where as mine will return the last value which is what I believe he asked for

"display at a glance the balance from each account"

Guess it's good to give him both options thou

VBA Noob