+ Reply to Thread
Results 1 to 2 of 2

Sum To Work Out Targets

  1. #1
    Registered User
    Join Date
    02-08-2006
    Posts
    6

    Sum To Work Out Targets

    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

  2. #2
    Kassie
    Guest

    RE: Sum To Work Out Targets

    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

    "Mikey2005" wrote:

    >
    > 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: http://www.excelforum.com/member.php...o&userid=31316
    > View this thread: http://www.excelforum.com/showthread...hreadid=515756
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1