Modify formula based on first non-negative number in a row

**DrSues02** · 08-08-2012, 08:47 PM

I'm trying to calculate CAGR for a row of 10 values using a standard formula: =RATE($K$1-$B$1,0,-B2,K2)

The first row has years, so K1 = 2011 and B1 = 2002, therefore the # of periods is 9.

This formula works fine if B2 is a positive number. However, it returns an error if B2 is negative, as the rate formula must start with a positive number. Is there some way to modify the above formula to start with the first non-negative value?

So if the row is:
-1, 2, 3 .... 10 then the formula would be =RATE($K$1-$C$1,0,-C2,K2). This ensures that both the # of periods & the starting period match up.

However, if the row is:
-1, -2, 3 ... 10, then the formula would be =RATE($K$1-$D$1,0,-D2,K2), basically, the formula would adjust over two columns so it starts with 3 as the beginning value.

Any suggestions are much appreciated.

Thanks.

**Ace_XL** · 08-09-2012, 03:04 AM

Perhaps ..

=RATE($K$1-INDEX($B$1:$I$1,MATCH(TRUE,INDEX(B2:I2>0,0),0)),0,INDEX(B2:I2,MATCH(TRUE,INDEX(B2:I2>0,0),0)),K2)

where Col B to Col I store your positive/negative values

---------- Post added at 11:04 AM ---------- Previous post was at 11:03 AM ----------

Perhaps ..

=RATE($K$1-INDEX($B$1:$I$1,MATCH(TRUE,INDEX(B2:I2>0,0),0)),0,INDEX(B2:I2,MATCH(TRUE,INDEX(B2:I2>0,0),0)),K2)

where Col B to Col I store your positive/negative values. This will work based on the first positive number

**DrSues02** · 08-09-2012, 11:55 AM

Thank you but I keep getting a NUM error using that formula (might be my fault, only 9 numbers in total).

Attaching a sample spreadsheet which should hopefully help clarify.

Sample.xlsx

**Ace_XL** · 08-09-2012, 03:23 PM

Tweaked the formula based on your row refernces...

=RATE($K2-INDEX($B2:$K2,MATCH(TRUE,INDEX(B3:K3>0,0),0)),0,-INDEX(B3:K3,MATCH(TRUE,INDEX(B3:K3>0,0),0)),K3)

See attached too..

**DrSues02** · 08-09-2012, 08:01 PM

Perfect thank you!

**Cutter** · 08-09-2012, 08:40 PM

@ DrSues02

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.