0

Hi I tried add a column via calculation, and it seems to not be correct because I keep getting a syntax error.. Can anyone tell me why or where I went wrong?

This is a dropdown box when a certain CIRCUIT TYPE is selected it would out put a calculation...

56, 56SW, 56FR are using the same calculation DDS IDSL, ISDN TR, ISDN are using the same calculation HDSL, HDSL2, HDSL4 are using the same calculation

Thank you... Here is the calculation below...

=IF([Circuit Type]="ISDN",(([F1 Makeup (26)]+[F1 bridge tap (26)]+[F2 Makeup (26)]+[F2 bridge tap (26)])*2.81)+(([F1 Makeup (24)]+[F1 bridge tap (24)]+[F2 Makeup (24)]+[F2 bridge tap (24)])*2.03)+(([F1 Makeup (22)]+[F1 bridge tap (22)]+[F2 Makeup (22)]+[F2 bridge tap (22)])*1.42)+(([F1 Makeup (19)]+[F1 bridge tap (19)]+[F2 Makeup (19)]+[F2 bridge tap (19)])*0.84), IF([Circuit Type]="DDS IDSL",(([F1 Makeup (26)]+[F1 bridge tap (26)]+[F2 Makeup (26)]+[F2 bridge tap (26)])*2.81)+(([F1 Makeup (24)]+[F1 bridge tap (24)]+[F2 Makeup (24)]+[F2 bridge tap (24)])*2.03)+(([F1 Makeup (22)]+[F1 bridge tap (22)]+[F2 Makeup (22)]+[F2 bridge tap (22)])*1.42)+(([F1 Makeup (19)]+[F1 bridge tap (19)]+[F2 Makeup (19)]+[F2 bridge tap (19)])*0.84), IF([Circuit Type]="ISDN T.R.",(([F1 Makeup (26)]+[F1 bridge tap (26)]+[F2 Makeup (26)]+[F2 bridge tap (26)])*2.81)+(([F1 Makeup (24)]+[F1 bridge tap (24)]+[F2 Makeup (24)]+[F2 bridge tap (24)])*2.03)+(([F1 Makeup (22)]+[F1 bridge tap (22)]+[F2 Makeup (22)]+[F2 bridge tap (22)])*1.42)+(([F1 Makeup (19)]+[F1 bridge tap (19)]+[F2 Makeup (19)]+[F2 bridge tap (19)])*0.84), IF([Circuit Type]="56",(([F1 Makeup (26)]+[F1 bridge tap (26)]+[F2 Makeup (26)]+[F2 bridge tap (26)])*2.45)+(([F1 Makeup (24)]+[F1 bridge tap (24)]+[F2 Makeup (24)]+[F2 bridge tap (24)])*1.85)+(([F1 Makeup (22)]+[F1 bridge tap (22)]+[F2 Makeup (22)]+[F2 bridge tap (22)])*1.3)+(([F1 Makeup (19)]+[F1 bridge tap (19)]+[F2 Makeup (19)]+[F2 bridge tap (19)])*0.77), IF([Circuit Type]="56SW",(([F1 Makeup (26)]+[F1 bridge tap (26)]+[F2 Makeup (26)]+[F2 bridge tap (26)])*2.45)+(([F1 Makeup (24)]+[F1 bridge tap (24)]+[F2 Makeup (24)]+[F2 bridge tap (24)])*1.85)+(([F1 Makeup (22)]+[F1 bridge tap (22)]+[F2 Makeup (22)]+[F2 bridge tap (22)])*1.3)+(([F1 Makeup (19)]+[F1 bridge tap (19)]+[F2 Makeup (19)]+[F2 bridge tap (19)])*0.77), IF([Circuit Type]="56FR",(([F1 Makeup (26)]+[F1 bridge tap (26)]+[F2 Makeup (26)]+[F2 bridge tap (26)])*2.45)+(([F1 Makeup (24)]+[F1 bridge tap (24)]+[F2 Makeup (24)]+[F2 bridge tap (24)])*1.85)+(([F1 Makeup (22)]+[F1 bridge tap (22)]+[F2 Makeup (22)]+[F2 bridge tap (22)])*1.3)+(([F1 Makeup (19)]+[F1 bridge tap (19)]+[F2 Makeup (19)]+[F2 bridge tap (19)])*0.77), IF([Circuit Type]="19.2",(([F1 Makeup (26)]+[F1 bridge tap (26)]+[F2 Makeup (26)]+[F2 bridge tap (26)])*1.73)+(([F1 Makeup (24)]+[F1 bridge tap (24)]+[F2 Makeup (24)]+[F2 bridge tap (24)])*1.31)+(([F1 Makeup (22)]+[F1 bridge tap (22)]+[F2 Makeup (22)]+[F2 bridge tap (22)])*0.96)+(([F1 Makeup (19)]+[F1 bridge tap (19)]+[F2 Makeup (19)]+[F2 bridge tap (19)])*0.59),IF([Circuit Type]="DDS T.R.",(([F1 Makeup (26)]+[F1 bridge tap (26)]+[F2 Makeup (26)]+[F2 bridge tap (26)])*1.91)+(([F1 Makeup (24)]+[F1 bridge tap (24)]+[F2 Makeup (24)]+[F2 bridge tap (24)])*1.46)+(([F1 Makeup (22)]+[F1 bridge tap (22)]+[F2 Makeup (22)]+[F2 bridge tap (22)])*1.08)+(([F1 Makeup (19)]+[F1 bridge tap (19)]+[F2 Makeup (19)]+[F2 bridge tap (19)])*0.64),IF([Circuit Type]="HDSL",(([F1 Makeup (26)]+[F2 Makeup (26)])*3.88+(([F1 Makeup (24)]+[F2 Makeup (24)])*2.84)+(([F1 Makeup (22)]+[F2 Makeup (22)])*2.18)+(([F1 Makeup (19)]+[F2 Makeup (19)])*1.5)+(([Num of F1 BTs]+[Num of F2 BTs])*3)+[Num of F1 Guage changes (HDSL)]+[Num of F2 Guage changes (HDSL)]), IF([Circuit Type]="HDSL2",(([F1 Makeup (26)]+[F2 Makeup (26)])*3.88+(([F1 Makeup (24)]+[F2 Makeup (24)])*2.84)+(([F1 Makeup (22)]+[F2 Makeup (22)])*2.18)+(([F1 Makeup (19)]+[F2 Makeup (19)])*1.5)+(([Num of F1 BTs]+[Num of F2 BTs])*3)+[Num of F1 Guage changes (HDSL)]+[Num of F2 Guage changes (HDSL)]), IF([Circuit Type]="HDSL4",(([F1 Makeup (26)]+[F2 Makeup (26)])*3.88+(([F1 Makeup (24)]+[F2 Makeup (24)])*2.84)+(([F1 Makeup (22)]+[F2 Makeup (22)])*2.18)+(([F1 Makeup (19)]+[F2 Makeup (19)])*1.5)+(([Num of F1 BTs]+[Num of F2 BTs])*3)+[Num of F1 Guage changes (HDSL)]+[Num of F2 Guage changes (HDSL)]),0)))))))))))

Improve this question
4
  • 1
    I think you can troubleshoot this formula on MS Excel...
    – agzertuche
    Dec 18, 2014 at 23:22
  • 1
    Can you give us some background? Lists and columns involved and what you are trying to do? This looks like a nightmare. It may be best to create an event receiver or workflow to calculate all this on ItemEdited.
    – Mike
    Dec 19, 2014 at 1:11
  • I thought the boundary was 7 nested If statements, yours has 11.
    – Eric Alexander
    Dec 19, 2014 at 13:26
  • Hi Mike, I created a dropdown box, which contains 56, 56SW, 56FR, DDS IDSL, ISDN TR, ISDN, HDSL, HDSL2, HDSL4.. User gets to select one, and once selected it would do a calculation and spit it out on the bottom of the page I have... How would I create a event receiver
    – Matt Ludwig
    Dec 19, 2014 at 14:06

1 Answer 1

Reset to default
2

In my experience, this level of complexity is when it's time to try something other than a single calculated column. You can nest calculated columns to do some subtotaling, you can make custom columns that do complex calculations in server side code, or you could have a powershell script come through and calculate the column. Any function with 11 closing parentheses at the end needs to be broken out.

Improve this answer
2
  • So how would I go about it? I had the formula working before but then I added these calculations in bold... because in the drop down it will have those chooses... but each field in the lines below have the same calculation for example. Choices 56, 56SW, 56FR are using the same calculation Choices DDS IDSL, ISDN TR, ISDN are using the same calculation choices HDSL, HDSL2, HDSL4 are using the same calculation
    – Matt Ludwig
    Dec 19, 2014 at 14:03
  • CONT... What would be the best way to go about that.... Is there a way to have the user choose a one of those and then perform the calculation? So if user chooses HDSL2 it will do the HDSL calculation, same thing if user chooses HDSL4 instead it will do the HDSL calculation since HDSL, HDSL2, AND HDSL4 are the same calculations...
    – Matt Ludwig
    Dec 19, 2014 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.