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

 

[joetejas]

Highlighted in red is what was added today.
=IF(BD3='Market Input'!$J$1,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)

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
20.00 21.00 22.00 23.00 24.00 25.00 26.00 27.00 28.00 29.00 30.00 31.00

these values are already there for referencing other formulas.
Again when the selector selects a month have that month and future month present data in the cells.
and previous month(s) read zero

 

[LouisT]

Ok, so it's hard to follow what ranges and references you're using in your formula. Here's a sample of what i did to show how the formula works:

[TABLE="width: 691"]
<tbody>[TR]
[TD][TABLE="width: 691"]
<tbody>[TR]
[TD]A1[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Validation Test[/TD]
[TD][/TD]
[TD][/TD]
[TD]Test data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Mar[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Feb[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Mar[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]Apr[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]May[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jun[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jul[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]Aug[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sep[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]Oct[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]Nov[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]Dec[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula is: =IF(E3<Vlookup($b$3,$d$3:$E$14,2,false),0,Sum($i$3:$i$14))<vlookup($b$3,$d$3:$e$14,2,false),0,sum($i$3:$i$14))

Highlighted in red is what was added today.
=IF(BD3='Market Input'!$J$1,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)

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
20.00 21.00 22.00 23.00 24.00 25.00 26.00 27.00 28.00 29.00 30.00 31.00

these values are already there for referencing other formulas.
Again when the selector selects a month have that month and future month present data in the cells.
and previous month(s) read zero

</vlookup($b$3,$d$3:$e$14,2,false),0,sum($i$3:$i$14))

 

[joetejas]

This is extremely hard to explain.
The selector is for Mar. The data stays put and Jan/Feb are 0. it almost seems like I can either add a =IF(BD3='Market Input'!$J$1,(AND.... keeping the APR-DEC data static.
[TABLE="width: 336"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr
[/TD]
[/TR]
[TR]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]($108,945)[/TD]
[TD]($79,364)
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 263"]
<colgroup><col width="118"><col width="4"><col width="5"><col width="76"><col width="60"></colgroup><tbody>[TR]
[TD="class: xl37732, width: 118"]Baseline Month
[/TD]
[TD="class: xl37734, width: 4"]
[/TD]
[TD="class: xl37733, width: 5"]
[/TD]
[TD="class: xl37734, width: 76"]
[/TD]
[TD="class: xl37735, width: 60"]Mar
[/TD]
[/TR]
</tbody>[/TABLE]
Selector above, data validation is only months.
I also tried playing with the selector to see if it could keep the current month while populating other data so the formula knows what to keep when selected.

=IF(BD3='Market Input'!$J$1,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)