Conditional formatting and Formulas...

Hello everyone, new here!

Trying to design a tracker for my reps monthly bonus'.

I have about everything they way I want it, I am just having trouble with conditional formatting and formulas.

Can I make a formula that does something (includes/excludes any given cell in the formulas calculations) based on another cells conditional format?
For instance, (if the photo below uploads), the bonus will accumulate, but not payout until the rep reaches 102% (STARTER/TARGET) of 2013 YTD business (column N).
The way I currently have it set up, i would like the EARNED column (Column Q) to accumulate all the bonuses from Column C, until the PAYOUT column turns GREEN, which is done by conditional formatting to being equal/greater than 102%. The problem is, once that turns green and the accumulated bonus amount is paid out, the next month it needs to restart and continue compounding again until the PAYOUT is green again, whether that is monthly, or if they have a slower month and it drops the YTD % GROWTH below 102%, it will just sit there until that number gets back to/above 102%...

Make sense?

Any ideas?



picture of excel file.jpg

How does this look? I have made the mockup below it in the same file, hope that gives the idea...

It sounds like I cant do what I want

Re: how the bonus works.

If the YTD number is not 102% of PYTD, which is 2% growth, the sales person does not get the bonus if it is earned. So if YTD is at 98% at the end of March, but they earned bonuses in Jan, Feb & March, the bonus will not be released until there is 2% growth in the YTD number. This sales person for example, did not get to/above 2% growth until June, but earned bonuses every month up to that. But if he has a bad month in July and doesnt meet target and that number drops down below 102%, his July bonus would be banked again until that number is back up over 102%...

There is also another kicker/qualifier, they must keep the same margin %, no margin loss, so that is reflected in column I. The green ones are ahead, the red are below. If they fall below their PYA margin percentage, they do not earn that bonus for that month.

So no way to make the payout section reflect appropriately and add/subtract based on those previous qualifiers and payouts?