Creating ranges for multipliers

[curtjer]

I am attempting to create a formula for a project. The multipliers I use will be based off of # of units used. So lets say I have 500,000 units. The first 100,000 would be multiplied by 9.3, 100,000.01 - 250,000 would be multiplied by 9.5 and 250,000.01 - infinite be multiplied by 9.7. I know I can created different cells, but wanted to see if this could be done with one formula. I tried using sumif but that doesnt appear correct. Any help would be appreciated. Thank you.

[Cheeky Charlie]

Possible but not a good idea - it's very hard to edit formulae like this even if you manage to get them working.

[curtjer]

The ranges will stay the same forever, there will only be 3 different price points based off of the number of units.

[mdbct]

Replace CELL with the number you are comparing.

Please Login or Register  to view this content.

[Cheeky Charlie]

this wouldn't work - it's "first x amount * y" + "second x amount * z"... it's like income tax - you need to take off the bits you've accounted for before applying the next multiplier - not sensible to do in one formula!

[mdbct]

I misinterpreted the original post.

It can be done in one formula or with the use of a table that contains the max values and the multipliers.

Single formula vesrion:

Please Login or Register  to view this content.

Version with table of values and multiplier:

Please Login or Register  to view this content.

Where
A1 = number of units
B1 = 1st max value (100,000) and C1 = multiplier (9.3)
B2 = 2nd max value (250,000) and C2 = multiplier (9.5)
C3 = last multiplier (9.7)

[shg]

Or similarly, =9.3*A1 + 0.2*(A1>100000)*(A1-100000) + 0.2*(A1>250000)*(A1-250000)

Or =SUM({9.3,0.2,0.2} * (A1>{0,100000,250000}) * (A1-{0,100000,250000}))

Or, if the breakpoints and incremental multipliers are in a table like this ...

Please Login or Register  to view this content.

... then =SUMPRODUCT((A1>A2:A4) * (A1-A2:A4) * B2:B4)

[Cheeky Charlie]

Or similarly, =9.3*A1 + 0.2*(A1>100000)*(A1-100000) + 0.2*(A1>250000)*(A1-250000)

That's clever

[curtjer]

I misinterpreted the original post.

It can be done in one formula or with the use of a table that contains the max values and the multipliers.

Single formula vesrion:

Please Login or Register  to view this content.

Version with table of values and multiplier:

Please Login or Register  to view this content.

Where
A1 = number of units
B1 = 1st max value (100,000) and C1 = multiplier (9.3)
B2 = 2nd max value (250,000) and C2 = multiplier (9.5)
C3 = last multiplier (9.7)

You Rock! The single formula version works perfectly, thank you!!!

[Danib1227]

I think i need the same formula but more extended out i have 7 multipliers. Is this something someone could help me with

[Danib1227]

IF number is from 0-9.99 multiply by 5/ 10-49.99 by 3.5/ 50-99.99 by 3/ 100-299.99 by 2.5/ 300-499.99 by 2/ 500-999.99 by 1.75/ 1000-infinity by 1.5.

[Danib1227]

It would be for our cost then the multiplier would be sell price. I appreciate any help. Thanx