Month is filtered and previous month are zeroed

J

joetejas

New Member
Joined
Feb 5, 2019
Messages
17
Hello,
I have columns Jan-Dec 2019 with a IF formula already in the cells. I need to figure out how to enter a formula that can zero out the previous month when selected, but keep the rest of the years data.
For example when I filter to Mar, the current data stays and Jan&Feb will equal to zero.
The month is filtered from another tab.

R82HDmSa5gLPgAAAABJRU5ErkJggg==



thanks in advance
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
I feel like this sounds like Pivot Table work. choose what variables you want to see and the others disappear.
 
Upvote 0
Example to what I want it to look like. sorry my pic will not paste, bold is rows,columns
[TABLE="width: 320"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]
A
1 Mar[/TD]
[TD] B
[/TD]
[TD] C
[/TD]
[TD] D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]2 Avg Rate 19
[/TD]
[TD] Jan 19
[/TD]
[TD]Feb 19 [/TD]
[TD]Mar 19 [/TD]
[TD]Apr 19[/TD]
[/TR]
[TR]
[TD]3 $2.92
[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]########[/TD]
[TD="align: right"]($74,231)[/TD]
[/TR]
[TR]
[TD]4$0.49
[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD="align: right"]$0[/TD]
[/TR]
[TR]
[TD]5$2.96
[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]($44,092)[/TD]
[TD="align: right"]($62,158)[/TD]
[/TR]
</tbody>[/TABLE]

Formula that exists in cell B3.
=IF($I4="Other - Explain in Comments",((AD4-$AC4)/$AC4)*P4,IF(AP4>0,((P4*AP4*((AD4-$BB4)/$BB4))+(((VLOOKUP($C4,'Market Input'!$G:$AL,BD$1,FALSE)-$BB4)/$BB4)*P4*(1-AP4))),((VLOOKUP($C4,'Market Input'!$G:$AL,BD$1,FALSE)-$BB4)/$BB4)*P4))

The Formula is similar throughout all the cells.

A1, is the month selected from the other tab. In the current sheet it reads ='Market Input'!J1 and does not select, just a reference for column A.
So for every month I would like to read-out but the previous month is zero value. Basically a rolling forecast, but can be select in another tab.
 
Last edited:
Upvote 0
Ok so if your original question was that you wanted all the months except the one that was selected and that would appear in Cell A1 below to show Zero, then you should just add another IF statement to check the month in front of the formula you wrote below.

Like this: =IF(a1=b2,IF($I4="Other - Explain in Comments",((AD4-$AC4)/$AC4)*P4,IF(AP4>0,((P4*AP4*((AD4-$BB4)/$BB4))+(((VLOOKUP($C4,'Market Input'!$G:$AL,BD$1,FALSE)-$BB4)/$BB4)*P4*(1-AP4))),((VLOOKUP($C4,'Market Input'!$G:$AL,BD$1,FALSE)-$BB4)/$BB4)*P4)),0)

joetejas said:
Example to what I want it to look like. sorry my pic will not paste, bold is rows,columns
[TABLE="width: 320"]
<tbody>[TR]
[TD]
A
1 Mar[/TD]
[TD] B
[/TD]
[TD] C
[/TD]
[TD] D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]2 Avg Rate 19[/TD]
[TD] Jan 19 [/TD]
[TD]Feb 19[/TD]
[TD]Mar 19[/TD]
[TD]Apr 19[/TD]
[/TR]
[TR]
[TD]3 $2.92[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]########[/TD]
[TD="align: right"]($74,231)[/TD]
[/TR]
[TR]
[TD]4$0.49[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD="align: right"]$0[/TD]
[/TR]
[TR]
[TD]5$2.96[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]($44,092)[/TD]
[TD="align: right"]($62,158)[/TD]
[/TR]
</tbody>[/TABLE]

Formula that exists in cell B3.
=IF($I4="Other - Explain in Comments",((AD4-$AC4)/$AC4)*P4,IF(AP4>0,((P4*AP4*((AD4-$BB4)/$BB4))+(((VLOOKUP($C4,'Market Input'!$G:$AL,BD$1,FALSE)-$BB4)/$BB4)*P4*(1-AP4))),((VLOOKUP($C4,'Market Input'!$G:$AL,BD$1,FALSE)-$BB4)/$BB4)*P4))

The Formula is similar throughout all the cells.

A1, is the month selected from the other tab. In the current sheet it reads ='Market Input'!J1 and does not select, just a reference for column A.
So for every month I would like to read-out but the previous month is zero value. Basically a rolling forecast, but can be select in another tab.
Click to expand...
 
Upvote 0
Excellent! I had to adjust a few things to make it work. I want to keep values in the forward months.
Do I have to place another IF statement in to keep the values on the forward months, March, APR, May through Dec?

For example Jan and Feb equal 0.
But the remaining columns read values as below

<colgroup><col style="mso-width-source:userset;mso-width-alt:3072;mso-outline-level: 1;width:63pt" width="84"> <col style="mso-width-source:userset;mso-width-alt:3072;mso-outline-level: 1;width:63pt" width="84"> <col style="mso-width-source:userset;mso-width-alt:3072;mso-outline-level: 1;width:63pt" width="84"> <col style="mso-width-source:userset;mso-width-alt:3072;mso-outline-level: 1;width:63pt" width="84"> <col style="mso-width-source:userset;mso-width-alt:3072;mso-outline-level: 1;width:63pt" width="84"> <col style="mso-width-source:userset;mso-width-alt:3328;mso-outline-level: 1;width:68pt" width="91"> <col style="mso-width-source:userset;mso-width-alt:3072;mso-outline-level: 1;width:63pt" width="84"> <col style="mso-width-source:userset;mso-width-alt:3072;mso-outline-level: 1;width:63pt" width="84"> <col style="mso-width-source:userset;mso-width-alt:3035;mso-outline-level: 1;width:62pt" width="83"> <col style="mso-width-source:userset;mso-width-alt:3218;mso-outline-level: 1;width:66pt" width="88"> </colgroup><tbody>
[TD="class: xl37733, width: 84"]Mar
[/TD]
[TD="class: xl37733, width: 84"]Apr[/TD]
[TD="class: xl37733, width: 84"]May[/TD]
[TD="class: xl37733, width: 84"]Jun
[/TD]
[TD="class: xl37733, width: 84"]Jul[/TD]
[TD="class: xl37733, width: 91"]Aug[/TD]
[TD="class: xl37733, width: 84"]Sep[/TD]
[TD="class: xl37733, width: 84"]Oct[/TD]
[TD="class: xl37733, width: 83"]Nov[/TD]
[TD="class: xl37733, width: 88"]Dec[/TD]

[TD="class: xl37732"]($104,409)[/TD]
[TD="class: xl37732"]($74,231)[/TD]
[TD="class: xl37732"]($75,549)[/TD]
[TD="class: xl37732"]($86,588)[/TD]
[TD="class: xl37732"]($71,643)[/TD]
[TD="class: xl37732"]($61,592)[/TD]
[TD="class: xl37732"]($80,771)[/TD]
[TD="class: xl37732"]($76,167)[/TD]
[TD="class: xl37732"]($73,308)[/TD]
[TD="class: xl37732"]($100,569)[/TD]

</tbody>



Like this: =IF(a1=b2,IF($I4="Other - Explain in Comments",((AD4-$AC4)/$AC4)*P4,IF(AP4>0,((P4*AP4*((AD4-$BB4)/$BB4))+(((VLOOKUP($C4,'Market Input'!$G:$AL,BD$1,FALSE)-$BB4)/$BB4)*P4*(1-AP4))),((VLOOKUP($C4,'Market Input'!$G:$AL,BD$1,FALSE)-$BB4)/$BB4)*P4)),0)[/QUOTE]
 
Upvote 0
It's not the most efficient way and i'm sure others on here would have something much simpler but i would just create a lookup table for the months and use a Vlookup nested in the IF statement to see if the month is greater than the one selected.

would look like this:
[TABLE="width: 128"]
<colgroup><col style="width: 64px" span="2"></colgroup><tbody>[TR]
[TD="class: xl5403, width: 64"]Jan[/TD]
[TD="width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl5403"]Feb[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl5403"]Mar[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl5403"]Apr[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl5403"]May[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl5403"]Jun[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl5403"]Jul[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl5403"]Aug[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl5403"]Sep[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl5403"]Oct[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl5403"]Nov[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl5403"]Dec[/TD]
[TD="align: right"]12

[/TD]
[/TR]
</tbody>[/TABLE]

joetejas said:
Excellent! I had to adjust a few things to make it work. I want to keep values in the forward months.
Do I have to place another IF statement in to keep the values on the forward months, March, APR, May through Dec?

For example Jan and Feb equal 0.
But the remaining columns read values as below

<tbody>
[TD="class: xl37733"]Mar[/TD]
[TD="class: xl37733, width: 84"]Apr[/TD]
[TD="class: xl37733, width: 84"]May[/TD]
[TD="class: xl37733, width: 84"]Jun[/TD]
[TD="class: xl37733, width: 84"]Jul[/TD]
[TD="class: xl37733, width: 91"]Aug[/TD]
[TD="class: xl37733, width: 84"]Sep[/TD]
[TD="class: xl37733, width: 84"]Oct[/TD]
[TD="class: xl37733, width: 83"]Nov[/TD]
[TD="class: xl37733, width: 88"]Dec[/TD]

[TD="class: xl37732"]($104,409)[/TD]
[TD="class: xl37732"]($74,231)[/TD]
[TD="class: xl37732"]($75,549)[/TD]
[TD="class: xl37732"]($86,588)[/TD]
[TD="class: xl37732"]($71,643)[/TD]
[TD="class: xl37732"]($61,592)[/TD]
[TD="class: xl37732"]($80,771)[/TD]
[TD="class: xl37732"]($76,167)[/TD]
[TD="class: xl37732"]($73,308)[/TD]
[TD="class: xl37732"]($100,569)[/TD]

</tbody>



Like this: =IF(a1=b2,IF($I4="Other - Explain in Comments",((AD4-$AC4)/$AC4)*P4,IF(AP4>0,((P4*AP4*((AD4-$BB4)/$BB4))+(((VLOOKUP($C4,'Market Input'!$G:$AL,BD$1,FALSE)-$BB4)/$BB4)*P4*(1-AP4))),((VLOOKUP($C4,'Market Input'!$G:$AL,BD$1,FALSE)-$BB4)/$BB4)*P4)),0)
Click to expand...
[/QUOTE]
 
Upvote 0
I have tried the VLookup and tried to find a way the selector could months outside the selector I could reference.
VLookup, I am having a problem nesting in the new IF statement.
 
Upvote 0
Show me the formula you're using and what the range is for the table of the months.

joetejas said:
I have tried the VLookup and tried to find a way the selector could months outside the selector I could reference.
VLookup, I am having a problem nesting in the new IF statement.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Depreciation Schedule with Partial Months
Replies
1
Views
82
footoo
F
B
  • Question Question
calculate monthly value
Replies
0
Views
237
BrutalLogiC
B
S
  • Solved
When Filter is applied, Excel jumps down multiple rows to (Blanks). How can Filter ignore blanks?
Replies
3
Views
284
Peter_SSs
Peter_SSs
R
  • Question Question
Auto populate data from master worksheet to cooresponding .monthly spreadsheet
Replies
2
Views
341
arun_eagertolearn
A
D
  • Solved
Could you please help me to write a macro for this situation?
Replies
6
Views
286
jolivanes
jolivanes

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

Share this page

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
Back
Top