Calculating Monthly Change % with an IF statement?

hooknows

New Member
Joined
Feb 2, 2004
Messages
36
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!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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

New Member
Joined
Feb 2, 2004
Messages
36
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.
 

hooknows

New Member
Joined
Feb 2, 2004
Messages
36
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,882
Messages
5,766,896
Members
425,383
Latest member
IllDo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top