Sum To Work Out Targets

M

Mikey2005

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
 
K

Kassie

Hi 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(A10<B4,A10*C4,IF(A10>=B4,B4*C4))
In Cell C10
=IF(A10>B4,IF(A10<=B5,(A10-B4)*C5,IF(A10>=B5,(B5-B4)*C5,0)))
In Cell D10
=IF(A10>B5,IF(A10<=B6(A10-B5)*C6,IF(A10>=B6,(B6-B5)*C6,0)),0)
In cell E10
=IF($A$10>$B4,IF($A$10<=$B7,($A$10-$B6)*$C76,IF($A$10>=$B7,($B7-$B6)*$C7,0)),0)
In Cell F10
=IF(A10>=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
 
Top