Too many arguments - better formula available?

[cjrhoads]

DonkeyOte and NBVC have been nice enough to help me through the roadblocks on my spreadsheet thus far. Now that I've completed it, one last requirement was dropped on me causing the spreadsheet to be altered again.

I am attaching a stripped down sample spreadsheet. The formula in question is in cell L37 on the 'Purchase' sheet.

It is:

Please Login or Register  to view this content.

Everything about this formula works great, except now I have another variable to add:
- if Selection X = Montgomery, only the first 250,000 of the price is calculated at the 3.45 rate in cell D17 on the 'Rates' sheet. Any amount of the price that exceeds this needs to be calculated at the rate of 5, then added to the previous calculation.

I am currently using a VLOOKUP to pull the appropriate rate. No other selections have a tiered rate calculation other than this one.

What's the best way to go about this? I cannot use SUMPRODUCT either as it does not work with other systems we dump into.

I also tried to spell out the scenario through IF statements, but I got a too many arguments error. There are also 4 other conditions which would have to be spelled out if done through an =IF(AND formula.

Any suggestions?

[cjrhoads]

Anyone? Any help is appreciated.

[NBVC]

Is it?

Please Login or Register  to view this content.

added part in red.

Note: I guess you meant "P" instead of "Montgomery" for you sample? If not, change the "P" in the Red part.

[NBVC]

Where in the formula is that supposed to take place?

[NBVC]

I am heading to bed... so I restored my earlier post, where I inserted the section in Red.

It doesn't seem to be in the right place though.. unless you mean when H11 is not A or B or C ???....

but it gives you an idea of the construct... you need to take that red part and put it within the formula where it needs to be evaluated...

[cjrhoads]

Ahhh, I can't get that to work either.

I'm attaching the actual spreadsheet. Forget trying to clean it at this point. I'll remove it once we can get it figured out.

And sorry for the wrong info previously, but the tier is actually over $500k, not $250k.

You can reference the Tax Rates table to see what I'm talking about.

I hope this helps because my brain is hurting!

[cjrhoads]

Have a good evening and thanks again for your help.

The string actually goes in the beginning of the formula as the first true IF statement, then it will be succeeded by the remaining statements.

[NBVC]

Still a bit confusing....

Maybe...

Please Login or Register  to view this content.

[cjrhoads]

Still can't get that to work. Why don't we forget all the crazy nonsense in the formula and just come up with a string that works standalone.

This is what we need:

The Price needs to be rounded up to the next $500, then multiplied by this:

Under $500k - $3.45 per $500
Over $500k - $3.45 per $500 up to $500k, plus any excess amount at $5 per $500

I can rework the string to fit it in, but these calculations that multiply part of a value by one number and another part by a different number kill me.

[cjrhoads]

I think this works for just the string, although I don't understand the logic:

Please Login or Register  to view this content.

We had put /1000, not 500

[cjrhoads]

Now where is the appropriate place to put the CEILING(B5,500)?

[NBVC]

Like this:

Please Login or Register  to view this content.

[cjrhoads]

I completely reworked the formula and came up with this:

Please Login or Register  to view this content.

Might not be pretty, but it works and it's easier to update in the future. I have one more (hopefully just one) coming that I'll put into a new thread...