Price based on Incremental staggering quantity

[Charlie369]

Hello,

First post, so please forgive me if I do something wrong. I found this site (and the answer to my problem) on google, but the formula doesn't seem to work for me.

The original post is below, but I'll quote it here for ease:

"Hi, I need a formula that would compute the incremental price based on staggered pricing structure.


For example:

1-100 $5

101-500 $4

501-1000 $3

1001+ $2


Such that if you have say 200 units, the total would be (100*5) + (100*4) = $900

Likewise, if you had 600 units, the total would be (100*5) + (400*4) + (100*3) = $2,400"

The answer given that appeared to work was:

=SUMPRODUCT((B1>A6:A9)*(B1-A6:A9)*(C6:C9))

With various explanations, but I can't figure the &#62 part

Appreciate any help, this has been driving me nuts all day as I know I figured it out before.

Original Post:

https://chandoo.org/forum/threads/calculating-price-based-on-incremental-staggered-quantity.3995/

 

[r2c2]

@Charlie369 Welcome to Chandoo.org forums.

When we migrated to a new forum software (somewhere in the middle of 2014 or 15?), the migration step replaced > and < signs with HTML equivalent to prevent errors.

For your reference, &#62; stands for > sign. Hope that helps.

 

[Charlie369]

Thank you, and thank Kyle McGhee is he's still around for a beautiful solution. Wish I understood how it worked

Just found a problem with it, it doesn't pick up the lowest number on the range, for example if you were selling 1 unit. Tried changing it to => but no joy.

 

[NARAYANK991]

Hi ,

Can you upload your workbook with enough data in it ?

Narayan