software

Afternoon,

For this example say the target of keyed deals is 100.

The targets are say 10p for each app keyed for the first 100 (Up to
100% of target)

From there up to 125% of target they get 25p per app keyed.... So if
they keyed 125% to target they would receive 100*0.10p and 25*0.25p.

So if they keyed 110% to target the would get 100*0.10p and 10*0.25p.

Over 125% up to 150% they get 0.75p per app keyed. so if they keyed 130
deals (130% to target) they would get 100*0.10p - 25*0.25p and 5*0.75p
towards their target...

And so it grows in stages.... How can i get excel to get this to sum
automatically...?

Thanks guys,

Mike--
Mikey2005
------------------------------------------------------------------------
Mikey2005's Profile: www.excelforum.com/member.php...oamp;userid=31316
View this thread: www.excelforum.com/showthread...hreadid=515756Hi Mikey

I would do the following:

Set up a list of targets and their returns, eg

Cells B4 to B8 the targets, viz 100, 125, 150, 175 and 200
Cells C4 to C8 the returns, viz 20p, 50p, 75p, 100p and 125p
I then used cell A10 to input the actual number of deals made, say 110(or
115,135,165 or 189)
In cell B10
=IF(A10lt;B4,A10*C4,IF(A10gt;=B4,B4*C4))
In Cell C10
=IF(A10gt;B4,IF(A10lt;=B5,(A10-B4)*C5,IF(A10gt;=B5,(B5-B4)*C5,0)))
In Cell D10
=IF(A10gt;B5,IF(A10lt;=B6(A10-B5)*C6,IF(A10gt;=B6,(B6-B5)*C6,0)),0)
In cell E10
=IF($A$10gt;$B4,IF($A$10lt;=$B7,($A$10-$B6)*$C76,IF($A$10gt;=$B7,($B7-$B6)*$C7,0)),0)
In Cell F10
=IF(A10gt;=B8(A10-B8)*C8,0)

As a last column, you can do an =SUM(B10:F10) or whatever you got at the
end, to give you the total.

You can copy the formulae down, if you make the addresses absolute, to cater
for any number of salesmen.

hth

quot;Mikey2005quot; wrote:

gt;
gt; Afternoon,
gt;
gt; For this example say the target of keyed deals is 100.
gt;
gt; The targets are say 10p for each app keyed for the first 100 (Up to
gt; 100% of target)
gt;
gt; From there up to 125% of target they get 25p per app keyed.... So if
gt; they keyed 125% to target they would receive 100*0.10p and 25*0.25p.
gt;
gt; So if they keyed 110% to target the would get 100*0.10p and 10*0.25p.
gt;
gt; Over 125% up to 150% they get 0.75p per app keyed. so if they keyed 130
gt; deals (130% to target) they would get 100*0.10p - 25*0.25p and 5*0.75p
gt; towards their target...
gt;
gt; And so it grows in stages.... How can i get excel to get this to sum
gt; automatically...?
gt;
gt; Thanks guys,
gt;
gt; Mike
gt;
gt;
gt; --
gt; Mikey2005
gt; ------------------------------------------------------------------------
gt; Mikey2005's Profile: www.excelforum.com/member.php...oamp;userid=31316
gt; View this thread: www.excelforum.com/showthread...hreadid=515756
gt;
gt;