Formula Help

  • Thread starter Thread starter Mike M
  • Start date Start date
M

Mike M

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!
 
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
 
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:A10>0,A1:A10,""),1),1)

Entered with Ctrl+Shift+enter rather than just enter since it is an array
formula.
 
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:A10>0,A1:A10,""),1),1)

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

--
Regards,
Tom Ogilvy




.
Click to expand...
 
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
 
After looking at your formula I see we are solving two slightly different
problems.
Click to expand...

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.
 
Back
Top