Inconsistant Formula result...

[jim may]

My formula L5 does not PRODUCE the Expected Result -- Of 5;
I'm sure there is a reason, but I don't at the moment know why it is.
Any help appreciated

Jim

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1									Last Value In		Last Value In
2		a	b	c	d	e	f		COLUMNS		ROWS
3	1	5.00	7.00	3.00	15.00	4.00	9.00		9.00		4.00
4	2	3.00		2.00					2.00		3.00
5	3	7.00		8.00					8.00		22.00	<< Why not 5?
6	4	2.00	3.00	22.00	88.00				88.00		88.00
7	5	4.00							4.00		11.00
8	6			5.00		11.00			11.00		9.00
Sheet1

Excel 2007

Cell Formulas
Range	Formula
I3	=LOOKUP(2,1/(A3:G3<>""),A3:G3)
I4	=LOOKUP(2,1/(A4:G4<>""),A4:G4)
I5	=LOOKUP(2,1/(A5:G5<>""),A5:G5)
I6	=LOOKUP(2,1/(A6:G6<>""),A6:G6)
I7	=LOOKUP(2,1/(A7:G7<>""),A7:G7)
I8	=LOOKUP(2,1/(A8:G8<>""),A8:G8)
K3	=LOOKUP(1,1/(B$3:B$8<>""),B$3:B$8)
K4	=LOOKUP(1,1/(C$3:C$8<>""),C$3:C$8)
K5	=LOOKUP(1,1/(D$3:D$8<>""),D$3:D$8)
K6	=LOOKUP(1,1/(E$3:E$8<>""),E$3:E$8)
K7	=LOOKUP(1,1/(F$3:F$8<>""),F$3:F$8)
K8	=LOOKUP(1,1/(G$3:G$8<>""),G$3:G$8)

 

[xenou]

I think you need a 2 as the lookup value, not a 1:
=LOOKUP(1,1/(D$3:D$8<>""),D$3:D$8)

Amend to:
=LOOKUP(2,1/(D$3:D$8<>""),D$3:D$8)

The result you have with your original formula, using a 1 for the first argument, is a bit hard for me to understand -- why not return a 3, in fact. But LOOKUP ranges must be sorted and your lookup vector is not sorted. "If the values in the Lookup_range are not sorted in ascending order, the Lookup function will return the incorrect value."

I found a treasure trove of such formula here:
http://excel.tips.net/Pages/T003785_Last_NonZero_Value_in_a_Row.html
And a page using on the technique you are employing:
http://www.dailydoseofexcel.com/archives/2004/12/02/getting-the-last-value/

 

[Dryver14]

Hi,

For rows try =HLOOKUP(999999,A3:G3,1,1)

For columns try =LOOKUP(999999,G3:G8)

Where 999999 just input a large number you are never likely to see

 

[Aladin Akyurek]

My formula L5 does not PRODUCE the Expected Result -- Of 5;
I'm sure there is a reason, but I don't at the moment know why it is.
Any help appreciated

Jim

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1									Last Value In		Last Value In
2		a	b	c	d	e	f		COLUMNS		ROWS
3	1	5.00	7.00	3.00	15.00	4.00	9.00		9.00		4.00
4	2	3.00		2.00					2.00		3.00
5	3	7.00		8.00					8.00		22.00	<< Why not 5?
6	4	2.00	3.00	22.00	88.00				88.00		88.00
7	5	4.00							4.00		11.00
8	6			5.00		11.00			11.00		9.00
Sheet1

Excel 2007

Cell Formulas
Range	Formula
I3	=LOOKUP(2,1/(A3:G3<>""),A3:G3)
I4	=LOOKUP(2,1/(A4:G4<>""),A4:G4)
I5	=LOOKUP(2,1/(A5:G5<>""),A5:G5)
I6	=LOOKUP(2,1/(A6:G6<>""),A6:G6)
I7	=LOOKUP(2,1/(A7:G7<>""),A7:G7)
I8	=LOOKUP(2,1/(A8:G8<>""),A8:G8)
K3	=LOOKUP(1,1/(B$3:B$8<>""),B$3:B$8)
K4	=LOOKUP(1,1/(C$3:C$8<>""),C$3:C$8)
K5	=LOOKUP(1,1/(D$3:D$8<>""),D$3:D$8)
K6	=LOOKUP(1,1/(E$3:E$8<>""),E$3:E$8)
K7	=LOOKUP(1,1/(F$3:F$8<>""),F$3:F$8)
K8	=LOOKUP(1,1/(G$3:G$8<>""),G$3:G$8)

Rowwise, just enter and copy down...

=LOOKUP(9.99999999999999E+307,B3:G3)

Columnwise, just enter and copy down:

=LOOKUP(9.99999999999999E+307,B3:B8)

You are interested in the last numeric value. The foregoing is the fastest
formula which delivers the goods.

=LOOKUP(N,/(B$3:B$8<>""),B$3:B$8)

is expensive and only needed in particular situations like having the need
to fetch any last value.

Grove, who first used such a formula, has set N to 2. The formula is of
course a variation on LOOKUP(9.99999999999999E+307,Ref). In fact, there is no need to invent particular constants for N. Just 9.99999999999999E+307
(or 9.99E+307) suffice. This number is mentioned in Excel's Help and provides the anchoring.

 

[jim may]

Thanks to All,

Jim