# Calculating Monthly Change % with an IF statement?

#### hooknows

##### New Member
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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### NateO

##### Legend
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
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

##### Legend
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
CDEF
123
2244.35%
3
4
521-12.50%
6239.52%
7
8
9
10
11244.35%
12
13
14
15
1623-4.17%
Sheet7

?

#### hooknows

##### New Member
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!

You are welcome.

Replies
4
Views
128
Replies
3
Views
144
Replies
21
Views
544
Replies
0
Views
135
Replies
0
Views
175

1,186,364
Messages
5,957,427
Members
438,305
Latest member
farmerje

### 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.

### Which adblocker are you using?

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

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