Formula Help
 

I need help with a formula. Here is my scenario, A1:A10 =
the sum of b1:b10 * c1. I need cell A11 to reflect only
the value of the last entry in A1:A10 greater than 0. I
don't want a sum of all. If A1:A5 all contain number and
A6:a10 are 0, can I get A11 to tell me what A5's value
is? Thank you in advance for your help!

Formula Help
 

A11: =INDEX(A1:A10,MATCH(0,A1:A10,0)-1)

If there aren't any zeroes in the cells A1:A10, the formula will result in
#N/A.

Troy

"Mike M" wrote in message
...
I need help with a formula. Here is my scenario, A1:A10 =
the sum of b1:b10 * c1. I need cell A11 to reflect only
the value of the last entry in A1:A10 greater than 0. I
don't want a sum of all. If A1:A5 all contain number and
A6:a10 are 0, can I get A11 to tell me what A5's value
is? Thank you in advance for your help!

Formula Help
 

Troy's formula is excellent for the situation of numbers greater than zero
all in a group at the start. If the zeroes will be intermixed, then you can
use this formula
=INDEX(A1:A10,MATCH(9.99999999999999E+307,IF(A1:A1 00,A1:A10,""),1),1)

Entered with Ctrl+Shift+enter rather than just enter since it is an array
formula.

--
Regards,
Tom Ogilvy

TroyW wrote in message
...
A11: =INDEX(A1:A10,MATCH(0,A1:A10,0)-1)

If there aren't any zeroes in the cells A1:A10, the formula will result in
#N/A.

Troy

"Mike M" wrote in message
...
I need help with a formula. Here is my scenario, A1:A10 =
the sum of b1:b10 * c1. I need cell A11 to reflect only
the value of the last entry in A1:A10 greater than 0. I
don't want a sum of all. If A1:A5 all contain number and
A6:a10 are 0, can I get A11 to tell me what A5's value
is? Thank you in advance for your help!

Formula Help
 

Thanks Tom
-----Original Message-----
Troy's formula is excellent for the situation of numbers
greater than zero
all in a group at the start. If the zeroes will be
intermixed, then you can
use this formula
=INDEX(A1:A10,MATCH(9.99999999999999E+307,IF
(A1:A100,A1:A10,""),1),1)

Entered with Ctrl+Shift+enter rather than just enter
since it is an array
formula.

--
Regards,
Tom Ogilvy

TroyW wrote in message
...
A11: =INDEX(A1:A10,MATCH(0,A1:A10,0)-1)

If there aren't any zeroes in the cells A1:A10, the
formula will result in
#N/A.

Troy

"Mike M" wrote in message
...
I need help with a formula. Here is my scenario,
A1:A10 =
the sum of b1:b10 * c1. I need cell A11 to reflect
only
the value of the last entry in A1:A10 greater than
0. I
don't want a sum of all. If A1:A5 all contain number
and
A6:a10 are 0, can I get A11 to tell me what A5's
value
is? Thank you in advance for your help!




.

Formula Help
 

Tom,

I think my formula will work with positive and negative numbers (it does in
my limited testing). There can also be multiple zeroes in the list. I'm
assuming that the OP wants the number that is one cell above the FIRST
occurrence of a zero (and that the list will have a bunch of zeroes at the
bottom, e.g. "A6:a10 are 0").

After looking at your formula I see we are solving two slightly different
problems. Thanks.

Troy

"Tom Ogilvy" wrote in message
...
Troy's formula is excellent for the situation of numbers greater than zero
all in a group at the start. If the zeroes will be intermixed, then you
can
use this formula
=INDEX(A1:A10,MATCH(9.99999999999999E+307,IF(A1:A1 00,A1:A10,""),1),1)

Entered with Ctrl+Shift+enter rather than just enter since it is an array
formula.

--
Regards,
Tom Ogilvy

TroyW wrote in message
...
A11: =INDEX(A1:A10,MATCH(0,A1:A10,0)-1)

If there aren't any zeroes in the cells A1:A10, the formula will result
in
#N/A.

Troy

"Mike M" wrote in message
...
I need help with a formula. Here is my scenario, A1:A10 =
the sum of b1:b10 * c1. I need cell A11 to reflect only
the value of the last entry in A1:A10 greater than 0. I
don't want a sum of all. If A1:A5 all contain number and
A6:a10 are 0, can I get A11 to tell me what A5's value
is? Thank you in advance for your help!

Formula Help
 

After looking at your formula I see we are solving two slightly different
problems.

I think that is what I said Troy. I used the word positive when I should
have used the word non-zero. Poor choice of words. There was no intent to
say it wouldn't work with negative numbers. Sorry for any confusion on that
point.

--
Regards,
Tom Ogilvy

TroyW wrote in message
...
Tom,

I think my formula will work with positive and negative numbers (it does
in
my limited testing). There can also be multiple zeroes in the list. I'm
assuming that the OP wants the number that is one cell above the FIRST
occurrence of a zero (and that the list will have a bunch of zeroes at the
bottom, e.g. "A6:a10 are 0").

After looking at your formula I see we are solving two slightly different
problems. Thanks.

Troy

"Tom Ogilvy" wrote in message
...
Troy's formula is excellent for the situation of numbers greater than
zero
all in a group at the start. If the zeroes will be intermixed, then you
can
use this formula
=INDEX(A1:A10,MATCH(9.99999999999999E+307,IF(A1:A1 00,A1:A10,""),1),1)

Entered with Ctrl+Shift+enter rather than just enter since it is an
array
formula.

--
Regards,
Tom Ogilvy

TroyW wrote in message
...
A11: =INDEX(A1:A10,MATCH(0,A1:A10,0)-1)

If there aren't any zeroes in the cells A1:A10, the formula will
result
in
#N/A.

Troy

"Mike M" wrote in message
...
I need help with a formula. Here is my scenario, A1:A10 =
the sum of b1:b10 * c1. I need cell A11 to reflect only
the value of the last entry in A1:A10 greater than 0. I
don't want a sum of all. If A1:A5 all contain number and
A6:a10 are 0, can I get A11 to tell me what A5's value
is? Thank you in advance for your help!