Alternative to SUMPRODUCT?

[cjrhoads]

Hello,

Yesterday DoneyOte was nice enough to help me with a formula, which worked, but now the problem is that a third party system I use won't recognize the calculation.

Is there another way to do this? Maybe through a VLOOKUP (since the rates change)? Here is the formula:

=SUMPRODUCT(--(CEILING(Purchase!B3,1000)>={0,250000,500000,1000000,5000000,15000000}),CEILING(Purchase!B3,1000)-{0,250000,500000,1000000,5000000,15000000},{3.89,-0.58,-0.53,-0.57,-0.37,-0.26})/1000

Here is the table:

Column A
Price
For the first $250k
Over $250k and up to $500k, add
Over $500k and up to $1M, add
Over $1M and up to $5M, add
Over $5M and up to $15M, add
Over $15M, add

Column B
Rate per $1k
$3.89
$3.31
$2.78
$2.21
$1.84
$1.58

This is what I need the formula to calculate:

Take the VALUE (price from a separate field), round up to the next $1k, and multiply the first $250k by $3.89, then multiply the next $250k to $500k by $3.31 and add it to the previous tier, and so forth. It also needs to round in increments of $1k (meaning, as long as a $1k mark is passed the number rounds up, so $1 would round to $1k)

Rather than referencing the table, the previous formula took the highest rate and subtracted the difference to the next tier. Again, I'd prefer to use a VLOOKUP if possible as I know our system recognizes this.

Any help? DonkeyOte was nice enough to figure this out with my terrible logic previously and I hope I'm explaining this much better this time around. I at least feel like I'm mastering this more!