Calculating Monthly Change % with an IF statement?

[hooknows]

Hi,

I have 3 columns of data.
Column A is the "Date" in 1/12/1984 format
Column B is the "Account Balance" that corresponds to each Date in Column A
Column C is an If formula that returns the Account Balance in Column B if the Date in Column A is month end. - This column just gives me the month end balances.

I would like to add a Column D that will calculate the monthly change in account balances when there is data in Column C. My problem here is that I cannot just use = (B-A)/A because there are cells interspersed in Column C that do not contain data. I could manually look to see if there is data in column C and just input the formula for each cell, but there are many months of data, so it would be very tedius. Is there a way to accomplish this with a formula?

Thanks!

 

[NateO]

Place the following in D2 and copy down:

=IF(ISNUMBER(C2),C2/INDEX($C$1:C1,MATCH(9.9999999999999E+307,$C$1:C1))-1,"")

 

[hooknows]

Thanks for the quick reply. I tried the formula and it's not quite returning the right calculation. It is doing something right though because it is returning a number for the monthly % change, but for each instance, it is returning 0.00%, which is incorrect. I don't understand this formula enough to troubleshoot where the fix should be. Thanks again.

 

[NateO]

Returns 0. Hmmm, that seems like a difficult number to mathematically arrive at, assuming the data isn't flat. Who knows? Does your data look like:

Book1
	C	D	E	F
1	23
2	24	4.35%
3
4
5	21	-12.50%
6	23	9.52%
7
8
9
10
11	24	4.35%
12
13
14
15
16	23	-4.17%
Sheet7

?

 

[hooknows]

Bingo! That did the trick! I must have just mistyped the formula the first time because I'm getting the right answers now. Thank you very much Nate!

 

[NateO]

You are welcome.