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:

Please Login or Register  to view this content.

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!

[martindwilson]

third party system?

[NBVC]

One option.. dependent on your systems capabilities is to break it right down...

So if your list of values is in A2:B7 with your lowerbounds starting from 0 in A2... then maybe:

Please Login or Register  to view this content.

[cjrhoads]

Sorry for the delayed response and thank you for your input NBVC.

I am attaching my table as a sample so I can better understand. I am wondering how I should create the reference table? I obviously need to change it from text for this to work.

[cjrhoads]

And it looks like this formula should work. For some reason SUMPRODUCT wasn't supported.

martindwilson, I would love to share what this is for but I can't get too specific. In a nutshell though, this spreadsheet feeds into another system to further massage the data.

[NBVC]

Sorry for the delayed response and thank you for your input NBVC.

I am attaching my table as a sample so I can better understand. I am wondering how I should create the reference table? I obviously need to change it from text for this to work.

As per attached. Do not use Merged cells for numeric cells or cells involved in formulas.. they will cause you problems.

[cjrhoads]

NBVC,

I hate to even ask, but can you revise the formula with the updated reference table? I'm having trouble implementing it and referencing the correct item.

[cjrhoads]

This is how I revised my formula (with the proper fields):

Please Login or Register  to view this content.

B17 = the value (or price)

It kicks out a completely wrong #.

[NBVC]

Here you go.

[cjrhoads]

Hey NBVC,

Thanks again for all of your help but same issue.

The formula is performing the first calculation, but it's not adding the others.

For instance, a value of 400,000 kicks out 972.50. This takes care of the first 250,000 of the value, but we need to add that 972.50 to 150,000 x the next tier and so forth.

Does this make sense?

[NBVC]

Did you see my attachment?

Go through the formula using Tools|Formula Auditing and then step through it by clicking Evaluate.. you will see how the formula works.

Let me know where it goes wrong.

For 400,000 in Purchase!B3, I get 3.47 (k) returned.

[cjrhoads]

Also, any reason you go from 501 to 1000001 in your reference table?

I would think it should go:

0
250.01
500.01
1000.01
5000.01
15000.01

Am I wrong?

[NBVC]

Forgot to multiply by 1000 on the first couple.

Also, changed the formula to:

Please Login or Register  to view this content.

Try that.

[cjrhoads]

Had to mess with it a bit longer but I got it. Thank you so much NBVC!

Update: the formula doesn't work for a VALUE over 500,000. Please see the following posts for an explanation.

[cjrhoads]

After moving on and testing the final spreadsheet, I've realized that this doesn't work either.

The formula only works up to 500,000. Try anything over that, such as 600,000, and do the math:

250 @ 3.89 = 972.50
250 @ 3.31 = 827.50
100 @ 2.78 = 278.00
Real Total = 2,078

I'm getting 2,131. I can't tell exactly where it's going wrong or how it's calculating this?

[cjrhoads]

Here is the tweaked formula:

Please Login or Register  to view this content.

[cjrhoads]

And if it helps, the formula I used was:

Please Login or Register  to view this content.

Sorry I realize this was a redundant post. I thought I wrote this already, but didn't see it because it was on the 2nd page!

[cjrhoads]

Bump for much needed advice. Sorry, but I'm on a tight deadline and this has been dragging on a bit. Just when I think I've gotten it....

[NBVC]

With the last sample I posted does this work?

Please Login or Register  to view this content.

you will have to adjust to your new references.

[cjrhoads]

Still nothing. I actually get the same results as the previous formula. Still anything over 500,000 doesn't add at the proper tier.

I have cleaned the spreadsheet and attached it for your reference. Included is the SUMPRODUCT formula that works - I've been using it as a cross-check.

[cjrhoads]

As you can see, anything under 500k matches. Once you put in 501,000 - they're off. I know it's not calculating at the right tier because if you look at the 'Calculations' sheet, that 1000 is calculated at $3.31, which is B30, and it should be calculating at $2.78, which is B31.

Strange, because it definitely references B31 in the formula...

[NBVC]

Ok you had some places where you didn't reference the Calculations tab, and I had some places where I used colons instead of minus signs...

Try:

Please Login or Register  to view this content.

[cjrhoads]

It works perfectly. Thank you so much (again)!

[NBVC]

Great... and sorry for the confusion

[cjrhoads]

Great... and sorry for the confusion

No worries - it seems I left out a few references myself. Must be from looking at the same syntax over and over. I'm grateful for the help.

[NBVC]

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

[cjrhoads]

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

I'm sorry for this oversight. I was anxious to get past my issue and hadn't read the rules.

I had also posted this question at: http://www.thecodecage.com/forumz/me...umproduct.html.

Again, I apologize for the oversight.