Too many arguments? Argh

[sdp4462]

Hi,

I'm new to this forum, but not new to Excel. I do have a problem that I have never encountered before: "Too many arguments entered." My complaints about MS user interface aside, I have spent hours trying to sort out this following equation, but I am about to give up, and thought I'd get someone else's eyes on this.

Basically, I'm to see if a number is 50 or more, if not then it should computer a fairly straightforward little function. If it is, then I validate it fits two criteria, first that the month (denoted by a number, not a word) is equal to or greater than two different cells. If it is, then I check the difference between those cells, and if it between several different sets of numbers e.g. Set 1(0,1,2), Set 2(3,4,5,6), then it runs an equation that pulls data from other cells and does a validation to 1) see if the cell before it had data in it, or if it was simply a "0", and 2) if it had data in the cell before it, it would then multiply that previous number by another cell, (in this case, a cell that indicated a 2% increase over the previous cell.

Hope that gives someone a bit of a challenge. Also, if someone could comment on some great "debugging" tricks, I would love to hear them. I find it challenging to follow the breakdown or "programming flow" of these long equations when an error crops up.

Thanks!




The equation:

=IF($D$271>=50,IF(OR(G$41<$B275,G$41<$B$272),0,IF(AND(G$41-$B$272>=0,G$41-$B$272<=2),IF(F275=0,(($D$271*0.1)*$C275),(F275*(1+BX275))),IF(AND(G$41-$B$272>=3,G$41-$B$272<=5),IF(F275>=($D$271*0.1),(($D$271*0.25)*$C275),((($D$271*0.25)+F275*(1+BX275))-F275)),IF(AND(G$41-$B$272>=6,G$41-$B$272<=11),IF(F275>=($D$271*.25),(($D$271*0.5)*$C275)+(F275-($D$271*.25)),((($D$271*.5)+F275*(1+BX275))-F275)),IF(AND(G$41-$B$272>=12,G$41-$B$272<=17),IF(F275>=($D$271*.5),(($D$271*0.75)*$C275)+(F275-($D$271*.5)),((($D$271*.5)+(F275*(1+BX275))-F275)),IF(AND(G$41-$B$272>=18,G$41-$B$272<=10000000),IF(F275=0,(($D$271*1)*$C275),(F275*(1+BX275))),9999999999)))))),IF(OR(G$41<$B275,G$41<$B$272),0,IF(F275=0,$D$271*$C275,F275*(1+BX275))))

[NBVC]

Although I am not sure of all your logic, I got this formula to work by adjusting parentheses... I will leave it to you to test for accuracy:

Please Login or Register  to view this content.

[sdp4462]

Thanks so much for your assistance. The logic was a little weird, though you would probably need to see the whole spreadsheet to fully comprehend. I made some changes anyway to clean it up. I figured the issue was probably something simple, but for some reason, I wasn't making it happen.

I do have a semi-related question for you though...

As a consultant, I go into businesses and turn them around or get them started. Having done this many times, I like to develop processes for overcoming obstacles (including the one outlined in this thread). What techniques can I use to troubleshoot issues like these in the future? I tried to use the formula editor, but I found the user interface nearly impossible to follow with such a lengthy equation. Any thoughts are much appreciated!

[MarvinP]

Hi,

When this happens to me, I break apart peices of the larger formula into cells by themselves. Many times I'll have 4 or 5 cells in a row that work individually. Then I gather them up into a single cell. Without confirming each part of a large formula it is hard to see where the problem is.