formula to apply % increase in specific month and calculate month over month change

[CAHIGHAM]

Hi there,

i am looking for a formula to enter into cells (JAN-DEC) for each row that calculates a % increase and is tied to the effective month column.

Is the only solution to do multiple IF statements for each cell? Any help is appreciated. Thanks.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Core
[/TD]
[TD]Increase
[/TD]
[TD]Eff. Month
[/TD]
[TD]JAN
[/TD]
[TD]FEB
[/TD]
[TD]MAR
[/TD]
[TD]APR
[/TD]
[TD]MAY
[/TD]
[TD]JUN
[/TD]
[TD]JUL
[/TD]
[TD]AUG
[/TD]
[TD]SEP
[/TD]
[TD]OCT
[/TD]
[TD]NOV
[/TD]
[TD]DEC
[/TD]
[TD]FY TOTAL
[/TD]
[/TR]
[TR]
[TD]10000
[/TD]
[TD]3%
[/TD]
[TD]MAR
[/TD]
[TD]10000
[/TD]
[TD]10000
[/TD]
[TD]10300
[/TD]
[TD]10300
[/TD]
[TD]10300
[/TD]
[TD]10300
[/TD]
[TD]10300
[/TD]
[TD]10300
[/TD]
[TD]10300
[/TD]
[TD]10300
[/TD]
[TD]10300
[/TD]
[TD]10300
[/TD]
[TD]123000
[/TD]
[/TR]
[TR]
[TD]10000
[/TD]
[TD]5%
[/TD]
[TD]SEP
[/TD]
[TD]10000
[/TD]
[TD]10000
[/TD]
[TD]10000
[/TD]
[TD]10000
[/TD]
[TD]10000
[/TD]
[TD]10000
[/TD]
[TD]10000
[/TD]
[TD]10000
[/TD]
[TD]10500
[/TD]
[TD]10500
[/TD]
[TD]10500
[/TD]
[TD]10500
[/TD]
[TD]122000
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ajamess]

Under January (D2 i'm guessing type):

=a2 * (1 + if(c2= "Jan", b2,0))

and fill down.

Then for February to December, type under Feb (I'm assuming E2)

=d2* (1 + if($c2=E$1,$B2,0))

and fill down and over

 

[CAHIGHAM]

Thanks for your quick reply - I am still having trouble and the formula isn't working for me.

It may be how I am calculating the core revenue - in the examples above, 10,000 is a per week number. I have the accounting weeks above the months in my spreadsheet (Jan=5 | Feb=4 | Mar=4) and so on. January is working fine, but Feb through Dec is not. The weeks are row 1, column descriptions are row 2, and data is row 3. Ignore FSC column. I am using a drop down list for column D (Eff. Month) to toggle the month that the increase would take effect. Note: an increase in March should continue at that adjusted amt (3%) for all of the following months. Below is a better example of my spreadsheet - hope you can help. Thanks in advance.

[TABLE="width: 1605"]
<colgroup><col><col><col><col><col span="13"></colgroup><tbody>[TR]
[TD="colspan: 2"]Weekly[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]Core[/TD]
[TD]FSC[/TD]
[TD]Increase %[/TD]
[TD]Eff. Month[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[TD]FY18 TOTAL[/TD]
[/TR]
[TR]
[TD] 60,000 [/TD]
[TD] - [/TD]
[TD]3.0%[/TD]
[TD]May [/TD]
[TD] 300,000 [/TD]
[TD] 240,000 [/TD]
[TD] 240,000 [/TD]
[TD] 300,000 [/TD]
[TD] 240,000 [/TD]
[TD] 240,000 [/TD]
[TD] 300,000 [/TD]
[TD] 240,000 [/TD]
[TD] 240,000 [/TD]
[TD] 300,000 [/TD]
[TD] 240,000 [/TD]
[TD] 240,000 [/TD]
[TD] 3,120,000 [/TD]
[/TR]
</tbody>[/TABLE]

 

[CAHIGHAM]

Thanks for your quick reply. Jan worked well but Feb onward didn't. It may be how I am calculating the core revenue. Below is a better outline of my worksheet. I really appreciate your help.

Row 1: the working weeks in each month for the accounting period.
Row 2: the column headers
Row 3: data

Ignore column B: FSC

Column C: data entered as a % increase

Column D: I am using a drop down list to be able to toggle the months.

I need the formula to take the weekly amount (60K below) X the weeks in the month. If there is an increase in May of 3%, the months of Jan-Apr should be 60K X # of weeks, and May-Dec would be 60K X 3% X number of weeks.



[TABLE="width: 1605"]
<colgroup><col><col><col><col><col span="13"></colgroup><tbody>[TR]
[TD="colspan: 2"]Weekly[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]Core[/TD]
[TD]FSC[/TD]
[TD]Increase %[/TD]
[TD]Eff. Month[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[TD]FY18 TOTAL[/TD]
[/TR]
[TR]
[TD] 60,000 [/TD]
[TD] - [/TD]
[TD]3.0%[/TD]
[TD]May [/TD]
[TD] 300,000 [/TD]
[TD] 240,000 [/TD]
[TD] 240,000 [/TD]
[TD] 300,000 [/TD]
[TD] 240,000 [/TD]
[TD] 240,000 [/TD]
[TD] 300,000 [/TD]
[TD] 240,000 [/TD]
[TD] 240,000 [/TD]
[TD] 300,000 [/TD]
[TD] 240,000 [/TD]
[TD] 240,000 [/TD]
[TD] 3,120,000
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ajamess]

Didn't mention the weeks.

Try For February to December type under Feb (Now I'm assuming F3):

 = E3/E$1 * (1 + if($D3 = F$2, $C3, 0)) * F$1

 

[CAHIGHAM]

Awesome - huge help thanks!

One problem fixed and another created.

When toggling the months in column D, every single month works except for May and June. I have formatted the text in row 2 and column D to be "general" as well as the list I have for all of the months. Any idea why this might be happening? I also checked that calculation is set to automatic.

 

[ajamess]

Usually for me it is a space at the end of one of the values in the list, or in the reference ones in row 2. Try setting effective month to May/(And then June), and in an off cell try to do, "=D3=i2" (and then "=D3=J2" when you pick June) to make sure it is realizing those are the same. If not you'll have to play with your formatting and values to make sure they are the same.

 

[CAHIGHAM]

Awesome! Thank you so much