Need Last Shown Value in my Cell I28

[jim may]

In the case below I need a formula in Cell I28 that will "bring-down" the last SHOWN value in the Range(I13:I27), which is W3. My Current cell I 23 doesn't show as "" has been assigned to it. Can someone assisy? TIA, Jim

Excel Workbook
	I
13	W2
14
15
16
17
18	W3
19
20
21
22
23
24
25
26
27
28
Managers Input

Excel 2007

Cell Formulas
Range	Formula
I13	=IF(H13<>0,"W2","")
I18	=IF(H18<>0,"W3","")
I23	=IF(H23<>0,"W4","")

 

[Jonmo1]

Try

=LOOKUP(2,1/(I13:I27<>""),I13:I27)

 

[T. Valko]

In the case below I need a formula in Cell I28 that will "bring-down" the last SHOWN value in the Range(I13:I27), which is W3. My Current cell I 23 doesn't show as "" has been assigned to it. Can someone assisy? TIA, Jim

Excel Workbook
	I
13	W2
14
15
16
17
18	W3
19
20
21
22
23
24
25
26
27
28
Managers Input

Excel 2007

Cell Formulas
Range	Formula
I13	=IF(H13<>0,"W2","")
I18	=IF(H18<>0,"W3","")
I23	=IF(H23<>0,"W4","")

Like this maybe...

=LOOKUP(2,1/(I13:I27<>""),I13:I27)

 

[jim may]

Thanks Guys - Terrific!! Just when I had thing set (I Thought,,,) I have near the same thing needed -- seems like my current formula is only considering POSITIVE Amts, not Negative. Cell I28 should be -781.08. How can I modify the existing formula in H28 to acheive... Jim

Excel Workbook
	H
13	1,562.16
14
15
16
17
18	(781.08)
19
20
21
22
23
24
25
26
27
28	1,562.16
Managers Input

Excel 2007

Cell Formulas
Range	Formula
H13	=G13*F13
H18	=G18*F18
H28	=IF(AND(H26=0,H21=0),LOOKUP(2,1/(ISNUMBER(H11:H16)),H11:H16),IF(H26=0,LOOKUP(2,1/(ISNUMBER(H11:H21)),H11:H21),LOOKUP(2,1/(ISNUMBER(H11:H27)),H11:H27)))

 

[jim may]

correction...

Cell H28 should be -781.08

 

[jim may]

Never Mind Guys - I Got It !!!

 

[Aladin Akyurek]

correction...

Cell H28 should be -781.08

Also an option involving your first query...

=LOOKUP(9.99999999999999E+307,SEARCH("?",I13:I27),I13:I27)

The latter query, involving numbers in H13:H27...

=LOOKUP(9.99999999999999E+307,H13:H27)

if there are no zeros in the range. But, if you want to avoid zero values...

=LOOKUP(9.99999999999999E+307,1/H13:H27,H13:H27)

 

[T. Valko]

Thanks Guys - Terrific!! Just when I had thing set (I Thought,,,) I have near the same thing needed -- seems like my current formula is only considering POSITIVE Amts, not Negative. Cell I28 should be -781.08. How can I modify the existing formula in H28 to acheive... Jim

Excel Workbook
	H
13	1,562.16
14
15
16
17
18	(781.08)
19
20
21
22
23
24
25
26
27
28	1,562.16
Managers Input

Excel 2007

Cell Formulas
Range	Formula
H13	=G13*F13
H18	=G18*F18
H28	=IF(AND(H26=0,H21=0),LOOKUP(2,1/(ISNUMBER(H11:H16)),H11:H16),IF(H26=0,LOOKUP(2,1/(ISNUMBER(H11:H21)),H11:H21),LOOKUP(2,1/(ISNUMBER(H11:H27)),H11:H27)))

For this one do you want to return the LAST (bottom-most) numeric value in the range?

=LOOKUP(1E100,H13:H27)