+ Reply to Thread
Results 1 to 4 of 4

Banding numbers associated trigger points

  1. #1
    Registered User
    Join Date
    11-15-2010
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Banding numbers associated trigger points

    Hi there.

    We have a customer rebate in place with various levels of refund based on the quantity purchased during the year. I have used a sumproduct formula to calculate this before however someone has arranged a new deal which has confused me a bit.

    The customer used to have the following set up -

    0-999 - £1.00 per unit rebate.
    1000-1999 - £2.00 per unit rebate.
    2000-2999 - £3.00 per unit rebate.

    So if they bought 2501 units they would get a rebate of (1000*1)+(1000*2)+(501*3). However the customer has trigger points so rather than the above it is now -

    0-999 - £1.00 per unit rebate.
    1000-1999 - £2.00 per unit rebate.
    2000-2999 - £4.00 per unit rebate if 2500 bought.

    So now it would look like this - (1000*1)+(1000*2)+(501*4). However if they only bought 2499 units it would be (1000*1)+(1000*2)+(499*2).

    Any ideas on how to programme this please?

    Thanks.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Banding numbers associated trigger points

    ill have none send me £1 please.....isn't that 1-999?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Banding numbers associated trigger points

    Assuming your rebates are accurate...try this regular formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    EDITED TO INCLUDE THIS:
    I just noticed your second scenario with rebates of 1, 2, and 4.
    This regular formula accommodates that.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that the final array is not the rebate amounts...they are the incremental rebate amounts.

    Did I catch all of the rules...or are there more?

    Is that something you can work with?
    Last edited by Ron Coderre; 03-04-2014 at 02:07 PM.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Banding numbers associated trigger points

    Maybe:
    =IF(A1<1000,A1,IF(A1<2000,A1*2,IF(A1<2500,3000+(A1-2000)*2,3000+(A1-2000)*4)))
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. need an audible alarm when real-time securities data reaches certain trigger points
    By okcsteve in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-14-2013, 01:54 PM
  2. AP / AR Trigger for negitive numbers
    By Campbound in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-21-2013, 04:14 PM
  3. Adding points delaminated numbers in cells
    By JamesDriver in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-29-2012, 10:31 PM
  4. sort by numbers with points
    By ahmad123 in forum Excel General
    Replies: 2
    Last Post: 05-24-2011, 04:27 PM
  5. [SOLVED] Sorting numbers with multiple decimal points?
    By Jonathan in forum Excel General
    Replies: 11
    Last Post: 05-17-2005, 06:06 PM

Tags for this Thread

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