+ Reply to Thread
Results 1 to 7 of 7

Can someone please explain this array formula in this sumproduct function - Tiered pricing

  1. #1
    Forum Contributor
    Join Date
    09-05-2007
    Posts
    148

    Can someone please explain this array formula in this sumproduct function - Tiered pricing

    Hi, I saw this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    It's basically calculating this:
    1,000,000 at 1.00% = 10,000
    4,000,000 at 0.70% = 28,000
    3,000,000 at 0.60% = 18,000

    I don't understand though what the -- is there for.

    Also, could someone take me through the steps of what it's actually doing? I get the broad logic, but just want a bit of clarity as to how it is working.

    Any explanation would be great.

    Thanks

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Can someone please explain this array formula in this sumproduct function - Tiered pri

    The -- is called a Double Unary, and it converts boolean TRUE/FALSE to numerical 1/0 values.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Can someone please explain this array formula in this sumproduct function - Tiered pri

    John McGimpsey explains it very well on his website, here:

    http://www.mcgimpsey.com/excel/variablerate.html

    There is also an explanation of using the double unary minus (--) here:

    http://www.mcgimpsey.com/excel/formulae/doubleneg.html

    Hope this helps.

    Pete

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

    Re: Can someone please explain this array formula in this sumproduct function - Tiered pri

    The Double-Unary (--) forces a conversion
    of TRUE/FALSE values to numbers.
    --TRUE = 1
    --FALSE = 0

    so...--{TRUE, TRUE, FALSE}...becomes {1,1,0}

    You could also multiply boolean values to the same effect:
    1*TRUE = 1
    1*FALSE = 0

    ...Or add 0
    TRUE + 0 = 1
    FALSE + 0 = 0

    But, the Dbl-Minus indicates to knowledgable users that
    a numeric conversion is intended, versus a calculation.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  5. #5
    Valued Forum Contributor
    Join Date
    12-25-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    506

    Re: Can someone please explain this array formula in this sumproduct function - Tiered pri

    Hi thedon_1,

    minus-minus formula converts each TRUE value to 1 and FALSE value to 0

    Let's try breakdown the formula, with assumption that A1=2000000...
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Thus when we SUMPRODUCT, it will sum up the arrays

    Formula: copy to clipboard
    Please Login or Register  to view this content.




    _______________________________________________________________________________________________________________________________
    1. Click on the * Add Reputation if you think someone helped you
    2. Mark your thread as SOLVED when question is resolved

    Alvin

  6. #6
    Forum Contributor
    Join Date
    09-05-2007
    Posts
    148

    Re: Can someone please explain this array formula in this sumproduct function - Tiered pri

    Excellent, thanks guys, i've learnt something new and now know how the formula works.

  7. #7
    Valued Forum Contributor
    Join Date
    12-25-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    506

    Re: Can someone please explain this array formula in this sumproduct function - Tiered pri

    You're welcome, and hope you enjoy the process



    _______________________________________________________________________________________________________________________________
    1. Click on the * Add Reputation if you think someone helped you
    2. Mark your thread as SOLVED when question is resolved

    Alvin

+ 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. HELP required - cannot create formula to auto calculate tiered pricing...
    By dcj1606 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-18-2013, 05:16 PM
  2. Tiered Pricing Model
    By waltheaj in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-12-2013, 03:10 PM
  3. [SOLVED] Tiered Pricing
    By unpuertomex in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-27-2012, 09:32 PM
  4. Tiered pricing
    By jimbojones82 in forum Excel General
    Replies: 1
    Last Post: 07-24-2012, 09:25 AM
  5. SUMPRODUCT to accurately calculate tiered sequential pricing.
    By JBeaucaire in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-08-2008, 05:32 PM

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