Month is filtered and previous month are zeroed

[joetejas]

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.

thanks in advance

 

[LouisT]

I feel like this sounds like Pivot Table work. choose what variables you want to see and the others disappear.

 

[joetejas]

I did also, however the pivottable is not an option.

 

[LouisT]

Can you give me a sample of the data so that i can understand what it looks like and what you'd like it to look like?

I did also, however the pivottable is not an option.

 

[joetejas]

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.

 

[LouisT]

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)

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.

 

[joetejas]

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]

 

[LouisT]

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]

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)

[/QUOTE]

 

[joetejas]

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.

 

[LouisT]

Show me the formula you're using and what the range is for the table of the months.

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.