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

Hi, I saw this formula

Formula:

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

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

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:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Thus when we SUMPRODUCT, it will sum up the arrays

Formula:

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

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

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