Maybe I'm not looking at this right, but I don't think it can be done as
stated, at least not with nested IF() statements.

My first question is: What do we do if F2 is exactly zero? Condition 1 says
what to do if F2 0 while condition 4 tells what to do if F2 < 0
(conditional based yet another formula).

We may get better results if you kind of laid it out as if it were nested if
statements and see if you run into any dead ends.

An IF() statement has 3 parts: a condition to test, what to do if the test
is true, what to do if the test is false. Either of the what-to-do's can be
another IF() statement with the same constraints. Up to 7 nested IF()
statements.

Just using the first two conditions, we run into this problem:

=IF(F20, F2*D2,IF(AND((F2*D2)E2,((F1+F2)*D2)0),(D2*(F1+F2 ))+E2,E2))
IF F2 0, (true) F2 * D2,
false (F2 is either 0 or < 0) but <0 is spoken for down in condition 4 but
it would also get handled here and this second condition is a dead end for us
because it does include both a true and false result option, so there's no
where to go to nest another IF() statement it looks like this when broken out:
IF F2*D2 < E2 AND ((F1+F2)*D2)0, (true) (D2*(F1+F2))+E2, (false) E2

So there we are with no way left to get to condition 3, much less condition 4.
Basic problem seems to be that there are two things to do if F2 < 0 - those
are defined as Condition 2 and Condition 4. Physics says (or used to say)
that no two objects can occupy the same space at the same time. Computer
logic says that no single condition create two solutions - and with
conditions 2 and 4 you have 2 separate conditions to be fulfilled when F2 < 0.

But maybe a wiser head than me will come along and figure different.

Perhaps if you restructured your conditions. Try phrasing them this way:

If (condition 1) then do this, else if (condition 2), do this, else if
(condition 3), do this, else if (condition 4), do this, else if none of the 4
conditions are met, do this!

You may even have to break it down to more than just 4 conditions, or even
resequence them. We'll try to help if we can get a set of definitions that
we can work with. Although it may not be a single cell solvable thing.

"derekblain" wrote:

Hello - I'm trying to create a formula that will enable me to do the
following:

All to occur in cell G2

Condition 1.
If F2 is 0 then multiply F2 by D2.

Condition 2 in the same cell.
If the product of F2 multiplied by D2 is E2 AND the sum of F2 and F1
multiplied by D2 is 0, then add the product of D2 multiplied by the sum of
F1 and F2 to E2, otherwise input E2

Condition 3 in the same cell.
If the product of F2 multiplied D2 is < E2 BUT the sum of F2 and F1
multiplied by D2 is 0, then input the product of D2 multiplied by the sum
of F1 and F2.

Condition 4 in the same cell.
If F2 < 0, and the sum of sum of F2 and F1 multiplied by D2 is < 0, then 0.

This is the best way I could think of the write out my intention. I think if
I can get the above scenario into a one cell formula that should resolve my
pain.

Thanks in advance.

Derek

JLatham - thanks for the assistance.

I hear your points - as a degreed english major (yawn) it has been a bit
tough for me to even get into words what I'm trying to accomplish
mathematically. Let's see if this helps:

I'm working on a model that will allow me to do the following:
1. Pay monthly bonuses for 2006 financial performance over 2005 monthly
actuals at a
fixed % rate.
2. There is a monthly cap which is reached when the 2006 monthly actual
reaches the 2006 monthly budget.
3. If the manager exceeds his 2006 budget in a given month, he cannot earn
more than the monthly cap unless in a prior month he wasn't paid his full
monthly potential AND the year to date performance exceeds the year to date
target.
4. All year-to-date performance is 'pooled' year-to-date so that I can
determine if the payee is due more than his 'cap' because he's now made up
the prior month overperformance.
4. A positive 'pool' can help the manager to regain any bonuses that were
missed in prior months. A negative pool will not have an impact on the
current month potential.

EXAMPLE
Month 1 Budget = $1M
Month 1 2005 = $750K
Month 1 2006 Actual = $650K
The manager would not qualify for a bonus becasue he didn't reach his 2005 actuals (the target).

Month 2 Budget = $1M
Month 2 2005 = $700K
Month 2 2006 = Actual $1.05M
The manager would earn a fixed percentage rate (I'm using 3.14%) times the amount over 2005 up to the budget. Therefore $300K (1M less 700K) x 3.14%. Now, since the manager was previously $100K under his 2005 target in month 1, the over budget performance of $50K from Month 2 would pool with the shortage of budget -$100K from month 1 reducing the ytd shortage to only -$50K. If the manager beats budget by another
$50K in month 3 he can recapture his lost incentive from month 1 in addition
to his month 3 capped amount.

Where I'm having an issue is that my current spreadsheet (which I can email
anyone if interested ;-) seems to be be paying out more than it should on
months where my manager has exceeded budget but is still under his target for
the year. This is because it is multiplying the full monthly overperformance
amount by my fixed percentage and not recognizing I have a monthly cap.

Hope that helps...

Thanks again,
Derek

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200608/1

Apologies - somehow my example didn't come out right (I've previewed it this
time)

Here goes again.

EXAMPLE
Month 1 Budget $1M
Month 1 2005 $750K
Month 1 2006 Actual $650K
The manager would not qualify for a bonus because he didn't reach his 2005
actual (the target).

Month 2 Budget $1M
Month 2 2005 $700K
Month 2 2006 Actual $1.05M
The manager would earn a fixed percentage rate (I'm using 3.14%) times the
amount over 2005 up to the budget. Therefore $300K (1M less 700K) x 3.14%.

Since the manager was previously $100K under his 2005 target in month 1, the
over budget performance of $50K from Month 2 would pool with the shortage of
budget -$100K from month one reducing the ytd shortage to only -$50K.

If the manager beats budget by another $50K in month 3 he can recapture his
lost incentive from month 1 in addition to his month 3 capped amount.

Where I'm having an issue is that my current spreadsheet (which I can email
anyone if interested ;-) seems to be be paying out more than it should on
months where my manager has exceeded budget but is still under his target for
the year. This is because it is multiplying the full monthly overperformance
amount by my fixed percentage and not recognizing I have a monthly cap.

Thanks.

--
Message posted via http://www.officekb.com

Why not post the file here, it can appear as a zipped attachment.

If a person is under the previous years performance for the first 2
month, say $50000 on month 1, $100000 on momth 2 and they exceed the
budget by $125000, in month 3, what would you wish to happen?
Pay the bonus on $125000 as previously you were $150000 behind?

Or something else

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=569657

This may be a start, I have only put the bonus as an amount, not
multiplied it by a fixed percentage. A slight isssue is If I have
exceeded performance and budget in month 1 but then failed in month 2,
can I get bonus in Month 2 if my year to date performance is still an
improvement on the previous year, in this example I have assumed you
do

Regards

Dav


+-------------------------------------------------------------------+
|Filename: Bonus.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5167 |
+-------------------------------------------------------------------+

--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=569657

Good Evening Da and thanks for the comments.

I have opened your file and made some modifications and notes using my
actual data.

In answer to your two previous posts: If a person is under the previous
year in Month 1 by $50K, under Month 2 by $100K, but over budget in
Month 3 by $125K, they would get their Month 3 capped bonus. They
could only earn more than their cap if their ytd performance was over
'05 by such a number that when multiplied by 3.14% (my rate) the
product was greater than their monthly cap. In that case they'd earn
the difference. There is a caveat, but you'll see that in the
spreadsheet - it has to do with the fact that there are monthly caps
which sum up to YTD caps.

Regarding your 2nd post: If you have exceeded Month 1, but failed
Month 2, you could still earn a bonus *if* your ytd performance
multiplied by 3.14% was greater than what you had earned in Month 1.
You would still cap however at the combined total of the Month 1 and
Month 2 caps.

At the end of the day, I want to pay the employee for hitting monthly
targets. Their ytd performance has no punitive impact on their monthly
performance. However, I give them the option to make up for prior month
'misses' if the ytd perf is such that they are still hitting ytd
targets. They can recapture some of what they would have earned in the
months when they rec'd nothing or accrue it for future months when they
might miss a monthly target.

I appreciate your help.

Here goes the post...

By the way, while I've added the real data and monthy bonus info, I
didn't overwrite your bonus data because I assume you may drop my data
into your existing formulas.

Thanks again,
Derek


+-------------------------------------------------------------------+
|Filename: Bonus_v2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5172 |
+-------------------------------------------------------------------+

--
derekblain
------------------------------------------------------------------------
derekblain's Profile: http://www.excelforum.com/member.php...o&userid=37171
View this thread: http://www.excelforum.com/showthread...hreadid=569657

I think this is what you want

Regards

Dav


+-------------------------------------------------------------------+
|Filename: Bonus_v2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5173 |
+-------------------------------------------------------------------+

--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=569657