Bracketing Issue

[ozbeachbum]

Hi all,
I have a formula I need to change and when I do it says that I have entered to many arguments. I believe it is a bracketing issue.
Original Formula:
=IF([02.xlsx]SAD!V5=0,0,
(([02.xlsx]SAD!V5-[02.xlsx]SAD!W5))-((([02.xlsx]SAD!I5+[02.xlsx]SAD!N5+[02.xlsx]SAD!O5+[02.xlsx]SAD!R5)+(B10*B11))))
Adjusted Formula:
=IF([02.xlsx]SAD!V5=0,0,
IF([02.xlsx]SAD!B10=0,
(([02.xlsx]SAD!V5-[02.xlsx]SAD!W5))-((([02.xlsx]SAD!I5+[02.xlsx]SAD!N5+[02.xlsx]SAD!O5+[02.xlsx]SAD!R5)+(B10*B11)))),
IF([02.xlsx]SAD!B10>0,
(([02.xlsx]SAD!V5-[02.xlsx]SAD!W5))-((([02.xlsx]SAD!I5+[02.xlsx]SAD!N5+[02.xlsx]SAD!O5+[02.xlsx]SAD!R5)+([02.xlsx]SAD!V5*B11))))),0)
I have spent a lot of time doing a bracket check and doing some adjustments to no avail.
I would have attached mini sheets, but I have just upgraded and the mini sheet won't paste, so trying to work that issue out also.
As always any assistance will be greatly appreciated.
Cheers,
Dave.

 

[valleyHemenway]

Dave,
I have used Microsoft Word before and copy/pasted my formula into there. Then I use tabbing for each "level" of parenthetical that I have created in my formula. Then I make those big and colored based on the "level" of indention. It seems to help me. I did that with yours and I believe that you basically have an extra ",0" in your main IF statement. It appears that your main IF has [02.xlsx]SAD!V5=0 and if that is TRUE then 0, otherwise do this long chain of events. However, at the end there is another ,0) which doesn't fit because you already have stated both your TRUE and FALSE statements for your main IF statement. I believe if you simply take off the ,0) from the end of your formula it should work....OR remove the ,0 from the start of the IF statement. Either way the extra ,0 is what I believe is causing the issue.

 

[Alex Blakenburg]

Since you have MS 2021, see if something like the below simplifies it for you but check it still does what you need.
Unless I am misreading it there is a base calculation adding and subtraction cells that is common to the B10 = 0 and B10 > 0 calculations.
In addition to @valleyHemenway's suggestion of using Word to help display your formula in more readable manner, you can also use Alt+Enter (new line) and white space in the formula bar to make the code easier to read.

=LET(baseCalc, [02.xlsx]SAD!V5 - [02.xlsx]SAD!W5 - [02.xlsx]SAD!I5 + [02.xlsx]SAD!N5 + [02.xlsx]SAD!O5 + [02.xlsx]SAD!R5,
             IF( OR( [02.xlsx]SAD!V5=0, [02.xlsx]SAD!B10<0, [02.xlsx]SAD!B10=""), 0,
                     IF([02.xlsx]SAD!B10=0, B10*B11, [02.xlsx]SAD!V5*B11)
                    + baseCalc
                 )
          )

 

[ozbeachbum]

Dave,
I have used Microsoft Word before and copy/pasted my formula into there. Then I use tabbing for each "level" of parenthetical that I have created in my formula. Then I make those big and colored based on the "level" of indention. It seems to help me. I did that with yours and I believe that you basically have an extra ",0" in your main IF statement. It appears that your main IF has [02.xlsx]SAD!V5=0 and if that is TRUE then 0, otherwise do this long chain of events. However, at the end there is another ,0) which doesn't fit because you already have stated both your TRUE and FALSE statements for your main IF statement. I believe if you simply take off the ,0) from the end of your formula it should work....OR remove the ,0 from the start of the IF statement. Either way the extra ,0 is what I believe is causing the issue.

Welcome to the forum.
Thank you your suggestion worked, the formula did require some extra bracketing, and Excel indicated the requirement.
Thanks for the suggestion re Word, I have been using the suggestion from Alex for sometime which I find helpful and sometimes I copy the formula and paste it into a blank cell and remove the equals sign and then Bold & Colour sections that way I have it in the sheet I am working on.
Once again thanks very much.
Cheers,
Dave.

 

[ozbeachbum]

Since you have MS 2021, see if something like the below simplifies it for you but check it still does what you need.
Unless I am misreading it there is a base calculation adding and subtraction cells that is common to the B10 = 0 and B10 > 0 calculations.
In addition to @valleyHemenway's suggestion of using Word to help display your formula in more readable manner, you can also use Alt+Enter (new line) and white space in the formula bar to make the code easier to read.

=LET(baseCalc, [02.xlsx]SAD!V5 - [02.xlsx]SAD!W5 - [02.xlsx]SAD!I5 + [02.xlsx]SAD!N5 + [02.xlsx]SAD!O5 + [02.xlsx]SAD!R5,
             IF( OR( [02.xlsx]SAD!V5=0, [02.xlsx]SAD!B10<0, [02.xlsx]SAD!B10=""), 0,
                     IF([02.xlsx]SAD!B10=0, B10*B11, [02.xlsx]SAD!V5*B11)
                    + baseCalc
                 )
          )

Hi Alex,
I have been using your suggestion re Alt + Enter for sometime and find it very helpful.
I have not used the LET function previously.
The simplicity of your formula is great however it did not return the correct result.
I will go and learn more about the LET function and play around with it and come back to you if I need advice if that is OK.
Thanks for your impute.
Cheers,
Dave.

 

[Alex Blakenburg]

No problem, happy to work through the formula with you if you need me to.
I am assuming the conditions using B10 in workbook "02.xlsx" and the calculation using B10 from the activeworkbook is intentional.

Note: If you have any users who don't have MS 365 or MS 2021 or later, then LET won't work for you.