Formula to tell me when Row of Numbers Turn Pos (2002)

Topic

New Reply

I have a financial calculation that adds annual totals together. For example, in row 62 I would have the following:
2005 2006 2007 2008 2009
a -500, b -900, c +200, d +1000, e +900.

Row 63 would have cumulative totals:

2005 2006 2007 2008 2009
-500, -1400, -1200, -200, +700

Each one of these cells represents a year. Consequently, in a 65 I would like to have a formula that showed me this the cumulative total turned positive after 4 years. Is this possible?

Reply | Quote

Replies

Not sure exactly what you are after but how about in A65:

=e64>=0

it will give a TRUE (truned possitive) or a FALSE (is negative)

If you are looking for something different could you elaborate?
Steve

Reply | Quote

Yr 1 Yr 2 Yr 3 Yr 4
(1,230) (2,480) (3,730) 500
a 62 b62 c62 d62
I basically like to look at row 62 and tell me what year this thing turns positive (beginning in row a). So if it turns positive in year 4 (row D) then my formula in a65 would tell me 4. Or if it turned positive in year 5 (row E), it would tell me 5…

Reply | Quote

Your cells seem to shift around slightly – in the first post the cumulative totals were in row 63, now in row 62. Assuming they are in row 62, the following array formula (confirm with Ctrl+Shift+Enter) will return the number of the first column with a positive total:

=MATCH(TRUE,A62:E62>0,0)

It’ll return 4 if the first positive total is in column D (the 4th column), or #N/A if there is no positive total. If there are no other values in row 62, you can also use

=MATCH(TRUE,62:62>0,0)

Reply | Quote

Thanks – PERFECT.

Reply | Quote