display last cell in a range

[John Caines]

Hello All.
I'm wanting to display the last cell in a range.
Basically it's a running total of £'s.

Column is W24:W274
As an example,, it goes something like this;
£100
£102
£99
£154
£111
etc, etc
And it's just whatever the last value displayed is the required value (That will be depicked into another cell via a formula).

I have googled etc,,, but there seems to be quite a few ways to do it, some work, some don't,,,

And I just really wanted a bit of clarity as to what was the best 1 to use (If there is such a thing,, IE best,,, maybe what I mean is the best as in simplest code, or purist excel code).
I found a formula here;
http://www.techonthenet.com/excel/questions/last_value.php

=INDEX(W24:X274,MAX(MATCH(9.99999999999999E+307,W24:X274),MATCH(REPT("z",255),W24:X274)))

I couldn't get this to work,, but it seemed a bit long to be honest.
I found something here;
http://www.cpearson.com/excel/LastCell.aspx

=MAX(ROW(W24:W274)*(W24:W274<>""))

Again, this didn't work,, keeps coming up with not valid in a merged cell,,, but this range now isn't merged? (But above 24,, W4:W22 some cells are merged), so maybe this is the problem?

Managed to get 3 to work,
http://help.lockergnome.com/office/cell-row-range-result--ftopict960544.html

=LOOKUP(9.9999999999999E+307,W24:W274)

=INDEX($W24:W274,COUNTA($W24:W274))

=LOOKUP(2,1/($W24:W274<>""),$W24:W274)

Not sure which of these 3 is best to use?
Could someone just advise me what is best, or if there is a better alternative please.

Many thanks
John Caines

 

[VoG]

Hi John. I would go with

=LOOKUP(9.9999999999999E+307,W24:W274)

Here's a handy reference for this sort of thing http://www.xldynamic.com/source/xld.LastValue.html

 

[John Caines]

Hi VOG,!!

Thanks for your reply,,,
A good article,,,

Trying to be clever and use it as a defined name,,
IE Defined Name:
LastNumber
code

=LOOKUP(9.9999999999999E+307,)

So my formula would now be (I think)

=LOOKUP(LastNumber,W24:W274)

Not working

Hey, Ho,,, so much for trying to be clever,,,
I'll just stick with;

=LOOKUP(9.9999999999999E+307,W24:W274)

Many thanks again VOG,,,
I can't believe the amount of posts you are now on VOG!! Amazing,, well done,,
I'm sure when I 1st got a reply from you you were only on about 1000,,,,, how time flys,,
All the best VOG

John Caines

 

[montecarlo2079]

this is the formula I use and it works quite well

Return the last value in a column of Data
=OFFSET(C2,MATCH(9.99999999999999E+307,C2:C30)-1,0,1,1)

 

[John Caines]

Many thanks MonteCarlo,, another good 1.

 

[Aladin Akyurek]

Hi VOG,!!

Thanks for your reply,,,
A good article,,,

Trying to be clever and use it as a defined name,,
IE Defined Name:
LastNumber
code

=LOOKUP(9.9999999999999E+307,)

So my formula would now be (I think)

=LOOKUP(LastNumber,W24:W274)

Not working

Hey, Ho,,, so much for trying to be clever,,,
I'll just stick with;

=LOOKUP(9.9999999999999E+307,W24:W274)

Many thanks again VOG,,,
I can't believe the amount of posts you are now on VOG!! Amazing,, well done,,
I'm sure when I 1st got a reply from you you were only on about 1000,,,,, how time flys,,
All the best VOG

John Caines

Define BigNum as referring to:

=9.99999999999999E+307

When such is done:

=LOOKUP(BigNum,Range)

where Range is one-dimensional reference like A:A, A2:A100, E2:M2, or 2:2,
you'll get the last numeric value from Range.

Define BigStr as referring to:

=REPT("z",255)

When such is done, you can invoke

=LOOKUP(BigStr,Range)

where Range is one-dimensional reference like A:A, A2:A100, E2:M2, or 2:2,
you'll get the last text value from Range.

Note that if Range houses a formula-blank (i.e., "") as last text value, the latter will return that formula-blank.

Note that these two expressions operate very fast, due to the algorithm LOOKUP and MATCH, VLOOKUP, HLOOKUP with match-type set to 1 use.

See post #7 in:

http://www.mrexcel.com/forum/showthread.php?t=310278

 

[Aladin Akyurek]

this is the formula I use and it works quite well

Return the last value in a column of Data
=OFFSET(C2,MATCH(9.99999999999999E+307,C2:C30)-1,0,1,1)

I don't understand the motive for this formula at all... It denies the very reason of:

=LOOKUP(9.99999999999999E+307,C2:C30)

=VLOOKUP(9.99999999999999E+307,C2:C30,1,1)

=INDEX(C2:C30,MATCH(9.99999999999999E+307,C2:C30,1))

which are very fast, while OFFSET is a volatile function that prolongs the recalc times.