Broken ROUUNDUP or VLOOKUP formula

Morning!

I have a formula that seems to be working, through much brute force and little finesse. However, one row of the code seems to be broken, and I'm perplexed as to why.

I included a sample file, with the problem row highlighted in yellow. The results of this row should show the number "1" after the person is hired; however, I'm getting the "#N/A" message. The formulas above and below this row seem to work fine. I built the formula in cell D6 and simply copied/pasted down and across.

Any thoughts? Thank you very much, if you do.

This Formula seems to be over-evolved
Please tell me in words, what does it do? when do you want "1", when "-"
Regards
pascal

Hi paesu57,

You're right about the formula being over-evolved! In fact, I lost my way through its complication.

In words, I want the formula to do one of three things...

1. If the month prior to the current month is >0, and the Current Month Production value =0, then either paste the prior month's value
2. If the month prior to the current month is >0, and the Current Month Production value >0, then calculate a new value based on the current month's production and that position's hiring metric (e.g., 200 for Regional Sales Manager).
3. If the month prior to the current month is <>0, then look to see if that position has a hire date for the current month, from the Control sheet.

If none of these three criteria are >0, then show "0".

I made it more complicated with the "2013" and "2016" references because I didn't want a separate formula for the first month (e.g., January 2014).

Thanks for your help.

martindwilson,
Thanks for the simple fix. Although my formula is over-thought (and written), you saw through my clutter and found the missing ingredient. Thanks for that!