If nested formulas, get "too many arguments" error

I'm building a formula so that it will calculate royalties to be paid to an investor as follows: i) Royalties are capped at 5 million (will not pay royalties after revenue exceeds 5 million); ii) Royalty on 4th and 5th million is 2%, on 3rd Million is 4%, on 2nd million is 6%, on 1st million is 8% (i.e. on the 1st Million of Revenue). The Revenue value calculated by the spreadsheet is on cell $G$13.

The formula that is getting me the "TOO MANY ARGUMENTS" error message is:

=IF($G$13>5000000,((2000000*0.02)+(1000000*0.04)+(1000000*0.06)+(1000000*0.08)),IF($G$13>3000000,(($G$13-3000000)*0.02)+(1000000*0.04)+(1000000*0.06)+(1000000*0.08)),IF($G$13>2000000,(($G$13-2000000)*0.04)+(1000000*0.06)+(1000000*0.08)),IF($G$13<1000000,(($G$13-1000000)*0.06)+(1000000*0.08)))

Please help me out! I really need a solution as soon as possible.

Thanks!

Hi.

Welcome to the forum. Take a look to these.

http://office.microsoft.com/en-us/ex...005209118.aspx

http://en.kioskea.net/faq/26886-nest...excel-function

welcome to the forum, jpelayo. there are a few issues here.
1) don't need so many unnecessary brackets. like our normal maths, Excel will calculate the multiplication before the addition
2) although Excel 2003 only allows 7 Nested IFs, you didn't exceed it. you merely closed your brackets at the wrong places
3) you are missing the calculation for 1M to 2M. you have >5M, >3M, >2M, <1M. is there never such a scenario?
4) at the last scenario of <1M, you stated you want the calculation as ($G$13-1000000)*0.06+1000000*0.08. if it's less than 1M, wouldn't G13-1M be a negative? maybe that's what you want. just confirming
5) in all equations, you have 1M * 8%. since it's common, you can place it 1 time outside the equation
6) your last equation for <1M is multiplying 6% & not the 2% you mentioned in the opening post

this is your equation that will work after amendment of 1 & 2:

Formula:

Please Login or Register  to view this content.

the rest needs to be clarified by you

Edit: i think this might help you:
http://mcgimpsey.com/excel/variablerate.html

and maybe give us what you hope to see if G13 is the following:
500K
1.5M
2.5M
3.5M
4.5M
6M

Please Login or Register  to view this content.

1) I dont find your file in your question.

2) another approach.

Make a table of all data and use VLookup to find those data.

That is also an solution if you get to the limited 7 if statements in the excel 2003 version.

Since you didn't add an workbook to work with, I can't show it to you.

If you still want to use brackets to distinguish between the various calculation being done, you can write your formula like this........

Please Login or Register  to view this content.