Stumped on Lookup Function

[Guest]

I would like to display the cell which holds the "last" positive number in a
column vector. (in other words...if the vector is a1:a3 with a1=2, a2=1,
a3=0, then I would like to display the value in a2 (1).) It seems like the
lookup function should work for this but I'm not sure how to specify "last
positive number" in the function? Thanks in advance for any ideas.

 

[Guest]

Here are 2 options:

For values in A1:A10

C1: =INDEX(A1:A10,SUMPRODUCT(MAX((A1:A10>0)*ROW(A1:A10))),1)

Or...this array formula*:
C1: =INDEX(A1:A10,MAX((A1:A10>0)*ROW(A1:A10)),1)

Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thank you for your time and solutions. I tried both and still do not get the
desired result. Could very well be my poor programming skills. Nevertheless
since your option #2 seems to get me close but not quite there. My exact
function is:

=INDEX(D344,MAX((D344>0)*ROW(D344)),1)

Cells D3 through D14 have positive numbers. Everything after D14 (D15, D16,
D17 etc.) have 0. I need for the function to give me the value from cell
D14, but instead it is giving me the value from D5. Also for what it is
worth, when I pull up the function arguments it gives me the appropriate
"Array" values for D344, the Row_num of 14 which is correct and Column_num
is 1 but the formula result = 0 which is inconcistent with what actually
shows up in the function cell. Am I applying the logic correctly? Thanks
again.

Here are 2 options:

For values in A1:A10

C1: =INDEX(A1:A10,SUMPRODUCT(MAX((A1:A10>0)*ROW(A1:A10))),1)

Or...this array formula*:
C1: =INDEX(A1:A10,MAX((A1:A10>0)*ROW(A1:A10)),1)

Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP

I would like to display the cell which holds the "last" positive number in a
column vector. (in other words...if the vector is a1:a3 with a1=2, a2=1,
a3=0, then I would like to display the value in a2 (1).) It seems like the
lookup function should work for this but I'm not sure how to specify "last
positive number" in the function? Thanks in advance for any ideas.

 

[Guest]

The 2nd argument of the INDEX function requires a positional reference
relative to the range referenced in the 1st argument AND your range does not
start at Row_1.
Consequently, we need to adjust the formula that calculates the positional
reference to compensate. In this case, I subtracted 2 from the row number.

Try this:
=INDEX(D344,MAX((D344>0)*(ROW(D344)-2)),1)

Note: For that array formula, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Thank you for your time and solutions. I tried both and still do not get the
desired result. Could very well be my poor programming skills. Nevertheless
since your option #2 seems to get me close but not quite there. My exact
function is:

=INDEX(D344,MAX((D344>0)*ROW(D344)),1)

Cells D3 through D14 have positive numbers. Everything after D14 (D15, D16,
D17 etc.) have 0. I need for the function to give me the value from cell
D14, but instead it is giving me the value from D5. Also for what it is
worth, when I pull up the function arguments it gives me the appropriate
"Array" values for D344, the Row_num of 14 which is correct and Column_num
is 1 but the formula result = 0 which is inconcistent with what actually
shows up in the function cell. Am I applying the logic correctly? Thanks
again.

Here are 2 options:

For values in A1:A10

C1: =INDEX(A1:A10,SUMPRODUCT(MAX((A1:A10>0)*ROW(A1:A10))),1)

Or...this array formula*:
C1: =INDEX(A1:A10,MAX((A1:A10>0)*ROW(A1:A10)),1)

Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP

I would like to display the cell which holds the "last" positive number in a
column vector. (in other words...if the vector is a1:a3 with a1=2, a2=1,
a3=0, then I would like to display the value in a2 (1).) It seems like the
lookup function should work for this but I'm not sure how to specify "last
positive number" in the function? Thanks in advance for any ideas.

 

[Guest]

Thanks for the clarification. I obviously don't completely understand the
formulation yet. Nevertheless, I made your recommended changes and I think I
am "almost" there. The value that your recommended function returns is 22,
which is from cell D3. However when I open the function argument box it
shows the function result as 25 which is the desired result from cell D16.
And to test it further, I added test values to cells, D17, D18, D19 etc. and
the function result in the argument box changes dynamically with the correct
result. I don't understand why the argument box result which is my "desired"
result, differs from what actually shows up in the function cell? The
function cell remains static with the value 22 (from D3) regardless of the
test values I enter in the array. Is this normal?

Again, thanks a ton for the knowledge.

The 2nd argument of the INDEX function requires a positional reference
relative to the range referenced in the 1st argument AND your range does not
start at Row_1.
Consequently, we need to adjust the formula that calculates the positional
reference to compensate. In this case, I subtracted 2 from the row number.

Try this:
=INDEX(D344,MAX((D344>0)*(ROW(D344)-2)),1)

Note: For that array formula, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Thank you for your time and solutions. I tried both and still do not get the
desired result. Could very well be my poor programming skills. Nevertheless
since your option #2 seems to get me close but not quite there. My exact
function is:

=INDEX(D344,MAX((D344>0)*ROW(D344)),1)

Cells D3 through D14 have positive numbers. Everything after D14 (D15, D16,
D17 etc.) have 0. I need for the function to give me the value from cell
D14, but instead it is giving me the value from D5. Also for what it is
worth, when I pull up the function arguments it gives me the appropriate
"Array" values for D344, the Row_num of 14 which is correct and Column_num
is 1 but the formula result = 0 which is inconcistent with what actually
shows up in the function cell. Am I applying the logic correctly? Thanks
again.

Here are 2 options:

For values in A1:A10

C1: =INDEX(A1:A10,SUMPRODUCT(MAX((A1:A10>0)*ROW(A1:A10))),1)

Or...this array formula*:
C1: =INDEX(A1:A10,MAX((A1:A10>0)*ROW(A1:A10)),1)

Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

I would like to display the cell which holds the "last" positive number in a
column vector. (in other words...if the vector is a1:a3 with a1=2, a2=1,
a3=0, then I would like to display the value in a2 (1).) It seems like the
lookup function should work for this but I'm not sure how to specify "last
positive number" in the function? Thanks in advance for any ideas.

 

[Guest]

Just to make sure you aren't missing the most critical step in creating an
ARRAY FORMULA...

After creating/editing the formula did you:

HOLD DOWN THE [CTRL] AND [SHIFT] KEYS WHEN YOU PRESSED [ENTER]

You can't just press the [Enter] key to commit an array formula.

If you did that properly, Excel will put braces { } around the formula.
(You can't type them in yourself.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Thanks for the clarification. I obviously don't completely understand the
formulation yet. Nevertheless, I made your recommended changes and I think I
am "almost" there. The value that your recommended function returns is 22,
which is from cell D3. However when I open the function argument box it
shows the function result as 25 which is the desired result from cell D16.
And to test it further, I added test values to cells, D17, D18, D19 etc. and
the function result in the argument box changes dynamically with the correct
result. I don't understand why the argument box result which is my "desired"
result, differs from what actually shows up in the function cell? The
function cell remains static with the value 22 (from D3) regardless of the
test values I enter in the array. Is this normal?

Again, thanks a ton for the knowledge.

The 2nd argument of the INDEX function requires a positional reference
relative to the range referenced in the 1st argument AND your range does not
start at Row_1.
Consequently, we need to adjust the formula that calculates the positional
reference to compensate. In this case, I subtracted 2 from the row number.

Try this:
=INDEX(D344,MAX((D344>0)*(ROW(D344)-2)),1)

Note: For that array formula, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Thank you for your time and solutions. I tried both and still do not get the
desired result. Could very well be my poor programming skills. Nevertheless
since your option #2 seems to get me close but not quite there. My exact
function is:

=INDEX(D344,MAX((D344>0)*ROW(D344)),1)

Cells D3 through D14 have positive numbers. Everything after D14 (D15, D16,
D17 etc.) have 0. I need for the function to give me the value from cell
D14, but instead it is giving me the value from D5. Also for what it is
worth, when I pull up the function arguments it gives me the appropriate
"Array" values for D344, the Row_num of 14 which is correct and Column_num
is 1 but the formula result = 0 which is inconcistent with what actually
shows up in the function cell. Am I applying the logic correctly? Thanks
again.


:

Here are 2 options:

For values in A1:A10

C1: =INDEX(A1:A10,SUMPRODUCT(MAX((A1:A10>0)*ROW(A1:A10))),1)

Or...this array formula*:
C1: =INDEX(A1:A10,MAX((A1:A10>0)*ROW(A1:A10)),1)

Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

I would like to display the cell which holds the "last" positive number in a
column vector. (in other words...if the vector is a1:a3 with a1=2, a2=1,
a3=0, then I would like to display the value in a2 (1).) It seems like the
lookup function should work for this but I'm not sure how to specify "last
positive number" in the function? Thanks in advance for any ideas.

 

[Guest]

You nailed it!!! I actually did follow your [ctrl] [shift] [enter]
instructions earlier but only after I had already entered the formula. I
re-entered it and now it works perfectly. Thanks so much! This process will
greatly simplify a weekly reporting function of which I am responsible for.

Just to make sure you aren't missing the most critical step in creating an
ARRAY FORMULA...

After creating/editing the formula did you:

HOLD DOWN THE [CTRL] AND [SHIFT] KEYS WHEN YOU PRESSED [ENTER]

You can't just press the [Enter] key to commit an array formula.

If you did that properly, Excel will put braces { } around the formula.
(You can't type them in yourself.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Thanks for the clarification. I obviously don't completely understand the
formulation yet. Nevertheless, I made your recommended changes and I think I
am "almost" there. The value that your recommended function returns is 22,
which is from cell D3. However when I open the function argument box it
shows the function result as 25 which is the desired result from cell D16.
And to test it further, I added test values to cells, D17, D18, D19 etc. and
the function result in the argument box changes dynamically with the correct
result. I don't understand why the argument box result which is my "desired"
result, differs from what actually shows up in the function cell? The
function cell remains static with the value 22 (from D3) regardless of the
test values I enter in the array. Is this normal?

Again, thanks a ton for the knowledge.

The 2nd argument of the INDEX function requires a positional reference
relative to the range referenced in the 1st argument AND your range does not
start at Row_1.
Consequently, we need to adjust the formula that calculates the positional
reference to compensate. In this case, I subtracted 2 from the row number.

Try this:
=INDEX(D344,MAX((D344>0)*(ROW(D344)-2)),1)

Note: For that array formula, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Thank you for your time and solutions. I tried both and still do not get the
desired result. Could very well be my poor programming skills. Nevertheless
since your option #2 seems to get me close but not quite there. My exact
function is:

=INDEX(D344,MAX((D344>0)*ROW(D344)),1)

Cells D3 through D14 have positive numbers. Everything after D14 (D15, D16,
D17 etc.) have 0. I need for the function to give me the value from cell
D14, but instead it is giving me the value from D5. Also for what it is
worth, when I pull up the function arguments it gives me the appropriate
"Array" values for D344, the Row_num of 14 which is correct and Column_num
is 1 but the formula result = 0 which is inconcistent with what actually
shows up in the function cell. Am I applying the logic correctly? Thanks
again.


:

Here are 2 options:

For values in A1:A10

C1: =INDEX(A1:A10,SUMPRODUCT(MAX((A1:A10>0)*ROW(A1:A10))),1)

Or...this array formula*:
C1: =INDEX(A1:A10,MAX((A1:A10>0)*ROW(A1:A10)),1)

Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

I would like to display the cell which holds the "last" positive number in a
column vector. (in other words...if the vector is a1:a3 with a1=2, a2=1,
a3=0, then I would like to display the value in a2 (1).) It seems like the
lookup function should work for this but I'm not sure how to specify "last
positive number" in the function? Thanks in advance for any ideas.

 

[Guest]

Thanks for the feedback, FH...If that didn't work, I was out of ideas!

***********
Regards,
Ron

XL2002, WinXP

You nailed it!!! I actually did follow your [ctrl] [shift] [enter]
instructions earlier but only after I had already entered the formula. I
re-entered it and now it works perfectly. Thanks so much! This process will
greatly simplify a weekly reporting function of which I am responsible for.

Just to make sure you aren't missing the most critical step in creating an
ARRAY FORMULA...

After creating/editing the formula did you:

HOLD DOWN THE [CTRL] AND [SHIFT] KEYS WHEN YOU PRESSED [ENTER]

You can't just press the [Enter] key to commit an array formula.

If you did that properly, Excel will put braces { } around the formula.
(You can't type them in yourself.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Thanks for the clarification. I obviously don't completely understand the
formulation yet. Nevertheless, I made your recommended changes and I think I
am "almost" there. The value that your recommended function returns is 22,
which is from cell D3. However when I open the function argument box it
shows the function result as 25 which is the desired result from cell D16.
And to test it further, I added test values to cells, D17, D18, D19 etc. and
the function result in the argument box changes dynamically with the correct
result. I don't understand why the argument box result which is my "desired"
result, differs from what actually shows up in the function cell? The
function cell remains static with the value 22 (from D3) regardless of the
test values I enter in the array. Is this normal?

Again, thanks a ton for the knowledge.

:

The 2nd argument of the INDEX function requires a positional reference
relative to the range referenced in the 1st argument AND your range does not
start at Row_1.
Consequently, we need to adjust the formula that calculates the positional
reference to compensate. In this case, I subtracted 2 from the row number.

Try this:
=INDEX(D344,MAX((D344>0)*(ROW(D344)-2)),1)

Note: For that array formula, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Thank you for your time and solutions. I tried both and still do not get the
desired result. Could very well be my poor programming skills. Nevertheless
since your option #2 seems to get me close but not quite there. My exact
function is:

=INDEX(D344,MAX((D344>0)*ROW(D344)),1)

Cells D3 through D14 have positive numbers. Everything after D14 (D15, D16,
D17 etc.) have 0. I need for the function to give me the value from cell
D14, but instead it is giving me the value from D5. Also for what it is
worth, when I pull up the function arguments it gives me the appropriate
"Array" values for D344, the Row_num of 14 which is correct and Column_num
is 1 but the formula result = 0 which is inconcistent with what actually
shows up in the function cell. Am I applying the logic correctly? Thanks
again.


:

Here are 2 options:

For values in A1:A10

C1: =INDEX(A1:A10,SUMPRODUCT(MAX((A1:A10>0)*ROW(A1:A10))),1)

Or...this array formula*:
C1: =INDEX(A1:A10,MAX((A1:A10>0)*ROW(A1:A10)),1)

Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

I would like to display the cell which holds the "last" positive number in a
column vector. (in other words...if the vector is a1:a3 with a1=2, a2=1,
a3=0, then I would like to display the value in a2 (1).) It seems like the
lookup function should work for this but I'm not sure how to specify "last
positive number" in the function? Thanks in advance for any ideas.