Finding and Adding

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

Hi,

Have a problem hopefully I can get some help with. I need a formula to find
the last positive number in a row and then add that and all the numbers that
come after it.

Example:

-10 100 -10 -20 60 -10 -20

I need the formula to locate the 60 and then add 60+(-10)+(-20)

Thanks in advance!

Rich
 
Hi Rich,

Assume your data is in A1:A7
In A2 enter this formula:
=IF(A1>0,0,A1)
In B2, enter this formula:
=IF(B1>0,0,A2+B1)
copy this formula to the right as far as you need

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
|
| Have a problem hopefully I can get some help with. I need a formula to find
| the last positive number in a row and then add that and all the numbers that
| come after it.
|
| Example:
|
| -10 100 -10 -20 60 -10 -20
|
| I need the formula to locate the 60 and then add 60+(-10)+(-20)
|
| Thanks in advance!
|
| Rich
 
Although not perfect given there could be all positive numbers something on
these lines would give you the result :

=SUM(OFFSET($A$1,MATCH(2,1/(--(A1:A7>0))),,ROW(A7))) array entered
(ctrl+shift+enter)
 
Back
Top