Forecasting (IF Function) ....Help

[politicalangel]

Hi,
Does anyone have an idea how I could go about the below scenerio

i have an assumption page.. of which I want to be able to input a predicted termination/closure .. by doing so ( entering the year, month and employee #).. a formula that I can use in all my data tables ( be it sales, commission, SGA, Tax etc) and locate the terminated employee based on what I entered in my assumption tab, and zero out any amounts forecasted going forward as of the terminated month.
Attached is an example of what am trying to get to.. The numbers of the data are hard coded, however in reality are formulas based, so I would be adding onto these formulas that make up the amounts on this example.

Thanks...

 

[Somendra Misra]

Hi politicalangel,

See the attached file (output Section).

Changes made, for the assumption tab the Month I had entered as date say for CY14 -Apr, I had inserted 1/4/2014 and custom format it as mmm (month) the same is done for Row 42.

Regards,

 

[Luke M]

With your example, put this formula in G24:
=IF(OR(SUMPRODUCT(($B$6:$B$11=G$22)*($C$6:$C$11=G$23)*($D$6:$D$11=$F24))>0,F24="-"),"-",G6)

Copy to all the other output cells.

 

[politicalangel]

Thanks guys,... both worked ... you guys are great!!