# Macro for Nested IF - Too Many Arguments

1. ## Macro for Nested IF - Too Many Arguments

Good evening,

I'm trying a nested if function but getting the error message that I have too many arguments. The formula is as follows

=IFERROR(IF(AND(H2>=1, H2<=7), B2*150, C2*110, D2*90, E2*75,F2*60, G2*60, IF(H2=8, B2*170, C2*125, D2*100, E2*85,F2*65,G2*60, IF(H2=9, B2*180, C2*135, D2*110,E2*90, F2*70,G2*60, IF(H2=10, B2*190, C2*145, D2*115, E2*95,F2*75,G2*60, IF(AND(H2>=11,H2<=20), B2*210, C2*160, D2*125, E2*105,F2*85,G2*60, IF(AND(H2>=21, H2<=22), B2*230, C2*175, D2*140, E2*115,F2*90,G2*60, IF(AND(H2>=23, H2<=24),B2*255, C2*190, D2*155, E2*125,F2*100,G2*60, IF(AND(H2>=25, H2<=26), B2*275, C2*205, D2*165, E2*135,F2*110, G2*60, IF(AND(H2>=27, H2<=28), B2*300, C2*225, D2*180, E2*150,F2*120,G2*60, IF(AND(H2>=29, H2<=30), B2*320, C2*240, D2*190, E2*160,F2*130, G2*60, IF(AND(H2>=31, H2<=32), B2*345, C2*260, D2*205, E2*170,F2*140, G2*60, IF(AND(H2>=33, H2<=34), B2*370, C2*275, D2*220, E2*185,F2*150, G2*60, IF(AND(H2>=35, H2<=36), B2*390, C2*290, D2*235, E2*195,F2*155, G2*60, IF(AND(H2>=37, H2<=38), B2*460, C2*345, D2*275, E2*230,F2*185, G2*60, IF(AND(H2>=39, H2<=50), B2*485, C2*365, D2*290, E2*240,F2*195, G2*60),0)

What are my alternatives to this if function. I'm assuming a Macro VBA is my best bet. Any assistance would be great appreciated  Register To Reply

2. ## Re: Macro for Nested IF - Too Many Arguments

The structure of an IF function is =IF(Condition, result if True, result if False) and a simple nested IF function is =IF(Condition 1, result if True, IF(Condition 2, result if True, result if False)). You can build on that ... =IF(Condition 1, result if True, IF(Condition 2, result if True, IF(Condition 3, result if True, result if False))) and so on.

That isn't what you have ... even with the AND.  Register To Reply

3. ## Re: Macro for Nested IF - Too Many Arguments

This
Formula:  `Please Login or Register  to view this content.`
part constitutes six (6) results where there should only be one (1).  Register To Reply

4. ## Re: Macro for Nested IF - Too Many Arguments

What are my alternatives?  Register To Reply

5. ## Re: Macro for Nested IF - Too Many Arguments

No idea. What are you trying to do?

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.  Register To Reply

6. ## Re: Macro for Nested IF - Too Many Arguments

I'm trying to determine commissions based on the picture snapshot of comp doc except the last column is HSD Only instead of Previous Period Lead Connect. I was using the if statements because the multipliers change based on total connects. There is a before and after in the 'Sample Commissions' document  Register To Reply

7. ## Re: Macro for Nested IF - Too Many Arguments

I see my mistake. My statement should actually show as follows but I'm still receiving the too many arguments error.

=IFERROR(IF(AND(H2>=1, H2<=7), ((B2*150)+ (C2*110)+ (D2*90)+( E2*75)+(F2*60)+(G2*60)), IF(H2=8, ((B2*170)+(C2*125)+(D2*100)+(E2*85)+(F2*65)+(G2*60)), IF(H2=9, ((B2*180)+(C2*135)+ (D2*110)+(E2*90)+(F2*70)+(G2*60)), IF(H2=10, ((B2*190)+ (C2*145)+ (D2*115)+ (E2*95) +(F2*75)+(G2*60)), IF(AND(H2>=11,H2<=20), ((B2*210)+ (C2*160)+ (D2*125)+ (E2*105)+(F2*85)+(G2*60)), IF(AND(H2>=21, H2<=22), ((B2*230)+ (C2*175)+ (D2*140)+(E2*115)+(F2*90)+(G2*60)), IF(AND(H2>=23, H2<=24),((B2*255)+ (C2*190)+ (D2*155)+ (E2*125)+(F2*100)+(G2*60)), IF(AND(H2>=25, H2<=26), ((B2*275)+ (C2*205)+ (D2*165)+ (E2*135)+(F2*110)+ (G2*60)), IF(AND(H2>=27, H2<=28), ((B2*300)+ (C2*225)+ (D2*180)+ (E2*150)+(F2*120)+(G2*60)), IF(AND(H2>=29, H2<=30), ((B2*320)+ (C2*240)+ (D2*190)+ (E2*160)+(F2*130)+ (G2*60)), IF(AND(H2>=31, H2<=32), ((B2*345)+ (C2*260)+ (D2*205)+ (E2*170)+(F2*140)+ (G2*60)), IF(AND(H2>=33, H2<=34), ((B2*370)+ (C2*275)+(D2*220)+(E2*185)+(F2*150)+ (G2*60)), IF(AND(H2>=35, H2<=36), ((B2*390)+ (C2*290)+ (D2*235)+ (E2*195)+(F2*155)+(G2*60)), IF(AND(H2>=37, H2<=38), ((B2*460)+(C2*345)+(D2*275)+(E2*230)+(F2*185)+ (G2*60)), IF(AND(H2>=39, H2<=50), ((B2*485)+ (C2*365)+(D2*290)+(E2*240)+(F2*195)+(G2*60))),0)  Register To Reply

8. ## Re: Macro for Nested IF - Too Many Arguments

Try:

Formula:  `Please Login or Register  to view this content.`  Register To Reply

9. ## Re: Macro for Nested IF - Too Many Arguments

Note, you have an excess of brackets. When you multiply two values, it takes priority over summing. So, you don't need brackets round a multiplication. And you can also lose the brackets round the sum itself. The formula can be significantly reduced. I've edited the first few lines:

Formula:  `Please Login or Register  to view this content.`  Register To Reply

10. ## Re: Macro for Nested IF - Too Many Arguments

New quick method:

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.  Register To Reply

11. ## Re: Macro for Nested IF - Too Many Arguments

Out of interest, this is the formula with all excess brackets removed.

Formula:  `Please Login or Register  to view this content.`  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 