software

I need to design a model to calculate rebate refunds and in doing so I have
run into some problems. Basically here is what I am trying to do...

For 100mg - $5
For 100mg 200mg - $15
For 100mg 200mg 300mg - $40

The incentive only applies when you order all products together. Cannot
order 100mg 300mg and get money back. Or 200mg 300mg. Must be 100mg,
100mg 200mg, or 100mg 200mg 300mg for incentive to apply.

First example is easy:
A B C D E
1] Item | Item Qty | Eligible Units | Rebate $ |Total Return
2] 100mg | 218 | 4 | $ 5 | $ 20
3] 200mg | 214 | 1 | $ 15 | $ 15
4] 300mg | 213 | 213 | $40 | $ 8520

In cell quot;C2quot; =IF(B2-B3lt;0,0,B2-B3)
In cell quot;C3quot; =IF(B3-B4lt;0,0,B3-B4)
In cell quot;C4quot; =B4

So here you get $40 * 213 units, $15* 1 unit, $5 * 4 units. Easy to do with
some very basic if statements. Everything works out fine in this example.

The problem I am having is when I get to a problem like below
Second example:
A B C D
E
Item | Item Qty | Eligible Units | Rebate $ |Total Return
5] 100mg | 100 | ? | $ 5 | ?
6] 200mg | 105 | ? | $ 15 | ?
7] 300mg | 94 | 94 | $40 | $3760

Here the values should be $40 *94 units = $3760, $15*6 units = $90, $5*0
units = $0. These are the values I need to find, but am having trouble with
if statements. I remember in one of classes back in college we had a problem
like this, just can't remember how it was done.

I need a model that will be able to generate the calculations based on the
incentive criteria. In the second example the initial if statements will not
apply because you only get the rebate refund if you have one of each dosage
ordered. Can anyone figure out an if statement that will be able to figure
out my calculated values like in example 2? Remember you can only receive $40
if 100mg 200mg 300mg are ordered, $15 if 100mg 200mg ordered, and $5 if
just 100mg ordered. Please someone help me out here. I am going crazy.


In the second example you have shown, I would have thought that the $5
would apply to 6 units (100-94), and that the $15 would apply to 5
units (105-100).

Am I missing something - further examples might help!

Regards
Mike--
Mikeopolo
------------------------------------------------------------------------
Mikeopolo's Profile: www.excelforum.com/member.php...oamp;userid=18570
View this thread: www.excelforum.com/showthread...hreadid=527325
Is this what you want? See attachment.IntricateFool Wrote:
gt; I need to design a model to calculate rebate refunds and in doing so I
gt; have
gt; run into some problems. Basically here is what I am trying to do...
gt;
gt; For 100mg - $5
gt; For 100mg 200mg - $15
gt; For 100mg 200mg 300mg - $40
gt;
gt; The incentive only applies when you order all products together.
gt; Cannot
gt; order 100mg 300mg and get money back. Or 200mg 300mg. Must be 100mg,
gt; 100mg 200mg, or 100mg 200mg 300mg for incentive to apply.
gt;
gt; First example is easy:
gt; A B C D E
gt; 1] Item | Item Qty | Eligible Units | Rebate $ |Total Return
gt; 2] 100mg | 218 | 4 | $ 5 | $
gt; 20
gt; 3] 200mg | 214 | 1 | $ 15 | $
gt; 15
gt; 4] 300mg | 213 | 213 | $40 | $ 8520
gt;
gt; In cell quot;C2quot; =IF(B2-B3lt;0,0,B2-B3)
gt; In cell quot;C3quot; =IF(B3-B4lt;0,0,B3-B4)
gt; In cell quot;C4quot; =B4
gt;
gt; So here you get $40 * 213 units, $15* 1 unit, $5 * 4 units. Easy to do
gt; with
gt; some very basic if statements. Everything works out fine in this
gt; example.
gt;
gt; The problem I am having is when I get to a problem like below
gt; Second example:
gt; A B C D
gt; E
gt; Item | Item Qty | Eligible Units | Rebate $ |Total Return
gt; 5] 100mg | 100 | ? | $ 5 |
gt; ?
gt; 6] 200mg | 105 | ? | $ 15 |
gt; ?
gt; 7] 300mg | 94 | 94 | $40 |
gt; $3760
gt;
gt; Here the values should be $40 *94 units = $3760, $15*6 units = $90,
gt; $5*0
gt; units = $0. These are the values I need to find, but am having trouble
gt; with
gt; if statements. I remember in one of classes back in college we had a
gt; problem
gt; like this, just can't remember how it was done.
gt;
gt; I need a model that will be able to generate the calculations based on
gt; the
gt; incentive criteria. In the second example the initial if statements
gt; will not
gt; apply because you only get the rebate refund if you have one of each
gt; dosage
gt; ordered. Can anyone figure out an if statement that will be able to
gt; figure
gt; out my calculated values like in example 2? Remember you can only
gt; receive $40
gt; if 100mg 200mg 300mg are ordered, $15 if 100mg 200mg ordered, and $5
gt; if
gt; just 100mg ordered. Please someone help me out here. I am going crazy. -------------------------------------------------------------------
|Filename: incentive.zip |
|Download: www.excelforum.com/attachment.php?postid=4536 |
-------------------------------------------------------------------

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: www.excelforum.com/member.php...foamp;userid=7094
View this thread: www.excelforum.com/showthread...hreadid=527325