software

I would like to display the cell which holds the quot;lastquot; 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 quot;last
positive numberquot; in the function? Thanks in advance for any ideas.

Here are 2 options:

For values in A1:A10

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

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

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

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

XL2002, WinXPquot;FishHeadquot; wrote:

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

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((D344gt;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
quot;Arrayquot; 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.quot;Ron Coderrequot; wrote:

gt; Here are 2 options:
gt;
gt; For values in A1:A10
gt;
gt; C1: =INDEX(A1:A10,SUMPRODUCT(MAX((A1:A10gt;0)*ROW(A1:A10 ))),1)
gt;
gt; Or...this array formula*:
gt; C1: =INDEX(A1:A10,MAX((A1:A10gt;0)*ROW(A1:A10)),1)
gt;
gt; Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter].
gt;
gt; Does that help?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;FishHeadquot; wrote:
gt;
gt; gt; I would like to display the cell which holds the quot;lastquot; positive number in a
gt; gt; column vector. (in other words...if the vector is a1:a3 with a1=2, a2=1,
gt; gt; a3=0, then I would like to display the value in a2 (1).) It seems like the
gt; gt; lookup function should work for this but I'm not sure how to specify quot;last
gt; gt; positive numberquot; in the function? Thanks in advance for any ideas.

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((D344gt;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, WinXPquot;FishHeadquot; wrote:

gt; Thank you for your time and solutions. I tried both and still do not get the
gt; desired result. Could very well be my poor programming skills. Nevertheless
gt; since your option #2 seems to get me close but not quite there. My exact
gt; function is:
gt;
gt; =INDEX(D344,MAX((D344gt;0)*ROW(D344)),1)
gt;
gt; Cells D3 through D14 have positive numbers. Everything after D14 (D15, D16,
gt; D17 etc.) have 0. I need for the function to give me the value from cell
gt; D14, but instead it is giving me the value from D5. Also for what it is
gt; worth, when I pull up the function arguments it gives me the appropriate
gt; quot;Arrayquot; values for D344, the Row_num of 14 which is correct and Column_num
gt; is 1 but the formula result = 0 which is inconcistent with what actually
gt; shows up in the function cell. Am I applying the logic correctly? Thanks
gt; again.
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Here are 2 options:
gt; gt;
gt; gt; For values in A1:A10
gt; gt;
gt; gt; C1: =INDEX(A1:A10,SUMPRODUCT(MAX((A1:A10gt;0)*ROW(A1:A10 ))),1)
gt; gt;
gt; gt; Or...this array formula*:
gt; gt; C1: =INDEX(A1:A10,MAX((A1:A10gt;0)*ROW(A1:A10)),1)
gt; gt;
gt; gt; Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter].
gt; gt;
gt; gt; Does that help?
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP
gt; gt;
gt; gt;
gt; gt; quot;FishHeadquot; wrote:
gt; gt;
gt; gt; gt; I would like to display the cell which holds the quot;lastquot; positive number in a
gt; gt; gt; column vector. (in other words...if the vector is a1:a3 with a1=2, a2=1,
gt; gt; gt; a3=0, then I would like to display the value in a2 (1).) It seems like the
gt; gt; gt; lookup function should work for this but I'm not sure how to specify quot;last
gt; gt; gt; positive numberquot; in the function? Thanks in advance for any ideas.

Thanks for the clarification. I obviously don't completely understand the
formulation yet. Nevertheless, I made your recommended changes and I think I
am quot;almostquot; 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 quot;desiredquot;
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.

quot;Ron Coderrequot; wrote:

gt; The 2nd argument of the INDEX function requires a positional reference
gt; relative to the range referenced in the 1st argument AND your range does not
gt; start at Row_1.
gt; Consequently, we need to adjust the formula that calculates the positional
gt; reference to compensate. In this case, I subtracted 2 from the row number.
gt;
gt; Try this:
gt; =INDEX(D344,MAX((D344gt;0)*(ROW(D344)-2)),1)
gt;
gt; Note: For that array formula, hold down [Ctrl] and [Shift] when you press
gt; [Enter], instead of just pressing [Enter].
gt;
gt; Does that help?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;FishHeadquot; wrote:
gt;
gt; gt; Thank you for your time and solutions. I tried both and still do not get the
gt; gt; desired result. Could very well be my poor programming skills. Nevertheless
gt; gt; since your option #2 seems to get me close but not quite there. My exact
gt; gt; function is:
gt; gt;
gt; gt; =INDEX(D344,MAX((D344gt;0)*ROW(D344)),1)
gt; gt;
gt; gt; Cells D3 through D14 have positive numbers. Everything after D14 (D15, D16,
gt; gt; D17 etc.) have 0. I need for the function to give me the value from cell
gt; gt; D14, but instead it is giving me the value from D5. Also for what it is
gt; gt; worth, when I pull up the function arguments it gives me the appropriate
gt; gt; quot;Arrayquot; values for D344, the Row_num of 14 which is correct and Column_num
gt; gt; is 1 but the formula result = 0 which is inconcistent with what actually
gt; gt; shows up in the function cell. Am I applying the logic correctly? Thanks
gt; gt; again.
gt; gt;
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Here are 2 options:
gt; gt; gt;
gt; gt; gt; For values in A1:A10
gt; gt; gt;
gt; gt; gt; C1: =INDEX(A1:A10,SUMPRODUCT(MAX((A1:A10gt;0)*ROW(A1:A10 ))),1)
gt; gt; gt;
gt; gt; gt; Or...this array formula*:
gt; gt; gt; C1: =INDEX(A1:A10,MAX((A1:A10gt;0)*ROW(A1:A10)),1)
gt; gt; gt;
gt; gt; gt; Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter].
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;FishHeadquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I would like to display the cell which holds the quot;lastquot; positive number in a
gt; gt; gt; gt; column vector. (in other words...if the vector is a1:a3 with a1=2, a2=1,
gt; gt; gt; gt; a3=0, then I would like to display the value in a2 (1).) It seems like the
gt; gt; gt; gt; lookup function should work for this but I'm not sure how to specify quot;last
gt; gt; gt; gt; positive numberquot; in the function? Thanks in advance for any ideas.

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, WinXPquot;FishHeadquot; wrote:

gt; Thanks for the clarification. I obviously don't completely understand the
gt; formulation yet. Nevertheless, I made your recommended changes and I think I
gt; am quot;almostquot; there. The value that your recommended function returns is 22,
gt; which is from cell D3. However when I open the function argument box it
gt; shows the function result as 25 which is the desired result from cell D16.
gt; And to test it further, I added test values to cells, D17, D18, D19 etc. and
gt; the function result in the argument box changes dynamically with the correct
gt; result. I don't understand why the argument box result which is my quot;desiredquot;
gt; result, differs from what actually shows up in the function cell? The
gt; function cell remains static with the value 22 (from D3) regardless of the
gt; test values I enter in the array. Is this normal?
gt;
gt; Again, thanks a ton for the knowledge.
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; The 2nd argument of the INDEX function requires a positional reference
gt; gt; relative to the range referenced in the 1st argument AND your range does not
gt; gt; start at Row_1.
gt; gt; Consequently, we need to adjust the formula that calculates the positional
gt; gt; reference to compensate. In this case, I subtracted 2 from the row number.
gt; gt;
gt; gt; Try this:
gt; gt; =INDEX(D344,MAX((D344gt;0)*(ROW(D344)-2)),1)
gt; gt;
gt; gt; Note: For that array formula, hold down [Ctrl] and [Shift] when you press
gt; gt; [Enter], instead of just pressing [Enter].
gt; gt;
gt; gt; Does that help?
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP
gt; gt;
gt; gt;
gt; gt; quot;FishHeadquot; wrote:
gt; gt;
gt; gt; gt; Thank you for your time and solutions. I tried both and still do not get the
gt; gt; gt; desired result. Could very well be my poor programming skills. Nevertheless
gt; gt; gt; since your option #2 seems to get me close but not quite there. My exact
gt; gt; gt; function is:
gt; gt; gt;
gt; gt; gt; =INDEX(D344,MAX((D344gt;0)*ROW(D344)),1)
gt; gt; gt;
gt; gt; gt; Cells D3 through D14 have positive numbers. Everything after D14 (D15, D16,
gt; gt; gt; D17 etc.) have 0. I need for the function to give me the value from cell
gt; gt; gt; D14, but instead it is giving me the value from D5. Also for what it is
gt; gt; gt; worth, when I pull up the function arguments it gives me the appropriate
gt; gt; gt; quot;Arrayquot; values for D344, the Row_num of 14 which is correct and Column_num
gt; gt; gt; is 1 but the formula result = 0 which is inconcistent with what actually
gt; gt; gt; shows up in the function cell. Am I applying the logic correctly? Thanks
gt; gt; gt; again.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Here are 2 options:
gt; gt; gt; gt;
gt; gt; gt; gt; For values in A1:A10
gt; gt; gt; gt;
gt; gt; gt; gt; C1: =INDEX(A1:A10,SUMPRODUCT(MAX((A1:A10gt;0)*ROW(A1:A10 ))),1)
gt; gt; gt; gt;
gt; gt; gt; gt; Or...this array formula*:
gt; gt; gt; gt; C1: =INDEX(A1:A10,MAX((A1:A10gt;0)*ROW(A1:A10)),1)
gt; gt; gt; gt;
gt; gt; gt; gt; Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter].
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;FishHeadquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I would like to display the cell which holds the quot;lastquot; positive number in a
gt; gt; gt; gt; gt; column vector. (in other words...if the vector is a1:a3 with a1=2, a2=1,
gt; gt; gt; gt; gt; a3=0, then I would like to display the value in a2 (1).) It seems like the
gt; gt; gt; gt; gt; lookup function should work for this but I'm not sure how to specify quot;last
gt; gt; gt; gt; gt; positive numberquot; in the function? Thanks in advance for any ideas.

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.

quot;Ron Coderrequot; wrote:

gt; Just to make sure you aren't missing the most critical step in creating an
gt; ARRAY FORMULA...
gt;
gt; After creating/editing the formula did you:
gt;
gt; HOLD DOWN THE [CTRL] AND [SHIFT] KEYS WHEN YOU PRESSED [ENTER]
gt;
gt; You can't just press the [Enter] key to commit an array formula.
gt;
gt; If you did that properly, Excel will put braces { } around the formula.
gt; (You can't type them in yourself.)
gt;
gt; Does that help?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;FishHeadquot; wrote:
gt;
gt; gt; Thanks for the clarification. I obviously don't completely understand the
gt; gt; formulation yet. Nevertheless, I made your recommended changes and I think I
gt; gt; am quot;almostquot; there. The value that your recommended function returns is 22,
gt; gt; which is from cell D3. However when I open the function argument box it
gt; gt; shows the function result as 25 which is the desired result from cell D16.
gt; gt; And to test it further, I added test values to cells, D17, D18, D19 etc. and
gt; gt; the function result in the argument box changes dynamically with the correct
gt; gt; result. I don't understand why the argument box result which is my quot;desiredquot;
gt; gt; result, differs from what actually shows up in the function cell? The
gt; gt; function cell remains static with the value 22 (from D3) regardless of the
gt; gt; test values I enter in the array. Is this normal?
gt; gt;
gt; gt; Again, thanks a ton for the knowledge.
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; The 2nd argument of the INDEX function requires a positional reference
gt; gt; gt; relative to the range referenced in the 1st argument AND your range does not
gt; gt; gt; start at Row_1.
gt; gt; gt; Consequently, we need to adjust the formula that calculates the positional
gt; gt; gt; reference to compensate. In this case, I subtracted 2 from the row number.
gt; gt; gt;
gt; gt; gt; Try this:
gt; gt; gt; =INDEX(D344,MAX((D344gt;0)*(ROW(D344)-2)),1)
gt; gt; gt;
gt; gt; gt; Note: For that array formula, hold down [Ctrl] and [Shift] when you press
gt; gt; gt; [Enter], instead of just pressing [Enter].
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;FishHeadquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Thank you for your time and solutions. I tried both and still do not get the
gt; gt; gt; gt; desired result. Could very well be my poor programming skills. Nevertheless
gt; gt; gt; gt; since your option #2 seems to get me close but not quite there. My exact
gt; gt; gt; gt; function is:
gt; gt; gt; gt;
gt; gt; gt; gt; =INDEX(D344,MAX((D344gt;0)*ROW(D344)),1)
gt; gt; gt; gt;
gt; gt; gt; gt; Cells D3 through D14 have positive numbers. Everything after D14 (D15, D16,
gt; gt; gt; gt; D17 etc.) have 0. I need for the function to give me the value from cell
gt; gt; gt; gt; D14, but instead it is giving me the value from D5. Also for what it is
gt; gt; gt; gt; worth, when I pull up the function arguments it gives me the appropriate
gt; gt; gt; gt; quot;Arrayquot; values for D344, the Row_num of 14 which is correct and Column_num
gt; gt; gt; gt; is 1 but the formula result = 0 which is inconcistent with what actually
gt; gt; gt; gt; shows up in the function cell. Am I applying the logic correctly? Thanks
gt; gt; gt; gt; again.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Here are 2 options:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; For values in A1:A10
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; C1: =INDEX(A1:A10,SUMPRODUCT(MAX((A1:A10gt;0)*ROW(A1:A10 ))),1)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Or...this array formula*:
gt; gt; gt; gt; gt; C1: =INDEX(A1:A10,MAX((A1:A10gt;0)*ROW(A1:A10)),1)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter].
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; gt; ***********
gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; Ron
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; XL2002, WinXP
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;FishHeadquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I would like to display the cell which holds the quot;lastquot; positive number in a
gt; gt; gt; gt; gt; gt; column vector. (in other words...if the vector is a1:a3 with a1=2, a2=1,
gt; gt; gt; gt; gt; gt; a3=0, then I would like to display the value in a2 (1).) It seems like the
gt; gt; gt; gt; gt; gt; lookup function should work for this but I'm not sure how to specify quot;last
gt; gt; gt; gt; gt; gt; positive numberquot; in the function? Thanks in advance for any ideas.

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

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

XL2002, WinXPquot;FishHeadquot; wrote:

gt; You nailed it!!! I actually did follow your [ctrl] [shift] [enter]
gt; instructions earlier but only after I had already entered the formula. I
gt; re-entered it and now it works perfectly. Thanks so much! This process will
gt; greatly simplify a weekly reporting function of which I am responsible for.
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Just to make sure you aren't missing the most critical step in creating an
gt; gt; ARRAY FORMULA...
gt; gt;
gt; gt; After creating/editing the formula did you:
gt; gt;
gt; gt; HOLD DOWN THE [CTRL] AND [SHIFT] KEYS WHEN YOU PRESSED [ENTER]
gt; gt;
gt; gt; You can't just press the [Enter] key to commit an array formula.
gt; gt;
gt; gt; If you did that properly, Excel will put braces { } around the formula.
gt; gt; (You can't type them in yourself.)
gt; gt;
gt; gt; Does that help?
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP
gt; gt;
gt; gt;
gt; gt; quot;FishHeadquot; wrote:
gt; gt;
gt; gt; gt; Thanks for the clarification. I obviously don't completely understand the
gt; gt; gt; formulation yet. Nevertheless, I made your recommended changes and I think I
gt; gt; gt; am quot;almostquot; there. The value that your recommended function returns is 22,
gt; gt; gt; which is from cell D3. However when I open the function argument box it
gt; gt; gt; shows the function result as 25 which is the desired result from cell D16.
gt; gt; gt; And to test it further, I added test values to cells, D17, D18, D19 etc. and
gt; gt; gt; the function result in the argument box changes dynamically with the correct
gt; gt; gt; result. I don't understand why the argument box result which is my quot;desiredquot;
gt; gt; gt; result, differs from what actually shows up in the function cell? The
gt; gt; gt; function cell remains static with the value 22 (from D3) regardless of the
gt; gt; gt; test values I enter in the array. Is this normal?
gt; gt; gt;
gt; gt; gt; Again, thanks a ton for the knowledge.
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; The 2nd argument of the INDEX function requires a positional reference
gt; gt; gt; gt; relative to the range referenced in the 1st argument AND your range does not
gt; gt; gt; gt; start at Row_1.
gt; gt; gt; gt; Consequently, we need to adjust the formula that calculates the positional
gt; gt; gt; gt; reference to compensate. In this case, I subtracted 2 from the row number.
gt; gt; gt; gt;
gt; gt; gt; gt; Try this:
gt; gt; gt; gt; =INDEX(D344,MAX((D344gt;0)*(ROW(D344)-2)),1)
gt; gt; gt; gt;
gt; gt; gt; gt; Note: For that array formula, hold down [Ctrl] and [Shift] when you press
gt; gt; gt; gt; [Enter], instead of just pressing [Enter].
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;FishHeadquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thank you for your time and solutions. I tried both and still do not get the
gt; gt; gt; gt; gt; desired result. Could very well be my poor programming skills. Nevertheless
gt; gt; gt; gt; gt; since your option #2 seems to get me close but not quite there. My exact
gt; gt; gt; gt; gt; function is:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =INDEX(D344,MAX((D344gt;0)*ROW(D344)),1)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Cells D3 through D14 have positive numbers. Everything after D14 (D15, D16,
gt; gt; gt; gt; gt; D17 etc.) have 0. I need for the function to give me the value from cell
gt; gt; gt; gt; gt; D14, but instead it is giving me the value from D5. Also for what it is
gt; gt; gt; gt; gt; worth, when I pull up the function arguments it gives me the appropriate
gt; gt; gt; gt; gt; quot;Arrayquot; values for D344, the Row_num of 14 which is correct and Column_num
gt; gt; gt; gt; gt; is 1 but the formula result = 0 which is inconcistent with what actually
gt; gt; gt; gt; gt; shows up in the function cell. Am I applying the logic correctly? Thanks
gt; gt; gt; gt; gt; again.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Here are 2 options:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; For values in A1:A10
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; C1: =INDEX(A1:A10,SUMPRODUCT(MAX((A1:A10gt;0)*ROW(A1:A10 ))),1)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Or...this array formula*:
gt; gt; gt; gt; gt; gt; C1: =INDEX(A1:A10,MAX((A1:A10gt;0)*ROW(A1:A10)),1)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter].
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; gt; gt; ***********
gt; gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; gt; Ron
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; XL2002, WinXP
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;FishHeadquot; wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; I would like to display the cell which holds the quot;lastquot; positive number in a
gt; gt; gt; gt; gt; gt; gt; column vector. (in other words...if the vector is a1:a3 with a1=2, a2=1,
gt; gt; gt; gt; gt; gt; gt; a3=0, then I would like to display the value in a2 (1).) It seems like the
gt; gt; gt; gt; gt; gt; gt; lookup function should work for this but I'm not sure how to specify quot;last
gt; gt; gt; gt; gt; gt; gt; positive numberquot; in the function? Thanks in advance for any ideas.