Whats wrong with my formula

[ksuthy]

Hello,

anyone want to take a stab at what is wrong with my formula?

=if(and([@inches]=0,[@[Length feet]]=6),6,if(and([@inches]=0,[@[Length feet]]=8),8,if(and([@inches]=0,[@[Length feet]]=10),10,if(and([@inches]=0,[@[Length feet]]=12),12,if(and([@inches]=0,[@[Length feet]]=14),14,if(and([@inches]=0,[@[Length feet]]=16),16,if(and([@inches]=0,[@[Length feet]]=18),18,if(and(([@inches]=0,[@[Length feet]]=20),20,if(and([@inches]=0,[@[Length feet]]=22),22,if((and([@inches]=0,[@[Length feet]]=24),24,IF(SUM([@[Length feet]]+([@inches]/12))<6),6,IF((AND(SUM([@[Length feet]]+([@inches]/12))<8,[@[Length feet]]+([@inches]/12)>6)),8,IF(AND(SUM([@[Length feet]]+([@inches]/12))<10,[@[Length feet]]+([@inches]/12)>8),10,IF(AND(SUM([@[Length feet]]+([@inches]/12))<12,[@[Length feet]]+([@inches]/12)>10),12,IF(AND(SUM([@[Length feet]]+([@inches]/12))<14,[@[Length feet]]+([@inches]/12)>12),14,IF(AND(SUM([@[Length feet]]+([@inches]/12))<16,[@[Length feet]]+([@inches]/12)>14),16,IF(AND(SUM([@[Length feet]]+([@inches]/12))<18,[@[Length feet]]+([@inches]/12)>16),18,IF(AND(SUM([@[Length feet]]+([@inches]/12))<20,[@[Length feet]]+([@inches]/12)>18),20,IF(AND(SUM([@[Length feet]]+([@inches]/12))<22,[@[Length feet]]+([@inches]/12)>20),22,FALSE))))))))))))))))))))

 

[6StringJazzer]

Why do you think there's something wrong with it?

What do you want it to do?

What is it doing instead?

 

[ksuthy]

Why do you think there's something wrong with it?

What do you want it to do?

What is it doing instead?

Thanks for asking,

I am missing a parentheses somewhere - i keep getting an error message.

 

[sandy666]

you did not lose the brackets, they are in a wrong places

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

(​

[/td][td=bgcolor:#E2EFDA]

66​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

)​

[/td][td]

66​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

+​

[/td][td=bgcolor:#E2EFDA]

17​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

,​

[/td][td]

56​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

/​

[/td][td=bgcolor:#E2EFDA]

17​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

<​

[/td][td]

9​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

=​

[/td][td=bgcolor:#E2EFDA]

21​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

>​

[/td][td]

8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

@​

[/td][td=bgcolor:#E2EFDA]

54​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

[​

[/td][td]

81​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

]​

[/td][td=bgcolor:#E2EFDA]

81​

[/td][/tr]
[/table]

 

[Michael M]

Bit hard to evaluate when you are using Named Ranges !
If you highlight the formula in the formula bar each parens is a different colour, that should give a start as to where the one is missing

 

[ksuthy]

Thank you! i don't understand the chart

 

[sandy666]

example :
,if((and([@inches]=0,[@[Length feet]]=24),24,.........
after that the rest of the "formula" is a logical test

and why

SUM([@[Length feet]]+([@inches]/12))
it looks like SUM SUM instead of SUM
SUM([@[Length feet]],[@inches]/12)

and many more errors like above

use intellisense to check this "string"

 

[ksuthy]

omg OK I solved it-its easiest for me because things are highlighted- thanks for the input.

here is the formula corrected :
=IF(AND([@inches]=0,[@[Length feet]]=6),6,IF(AND([@inches]=0,[@[Length feet]]=8),8,IF(AND([@inches]=0,[@[Length feet]]=10),10,IF(AND([@inches]=0,[@[Length feet]]=12),12,IF(AND([@inches]=0,[@[Length feet]]=14),14,IF(AND([@inches]=0,[@[Length feet]]=16),16,IF(AND([@inches]=0,[@[Length feet]]=18),18,IF(AND([@inches]=0,[@[Length feet]]=20),20,IF(AND([@inches]=0,[@[Length feet]]=22),22,IF(AND([@inches]=0,[@[Length feet]]=24),24,IF(SUM([@[Length feet]]+([@inches]/12))<6,6,IF(AND(SUM([@[Length feet]]+([@inches]/12))<8,SUM([@[Length feet]]+([@inches]/12)>6)),8,IF(AND(SUM([@[Length feet]]+([@inches]/12))<10,SUM([@[Length feet]]+([@inches]/12)>8)),10,IF(AND(SUM([@[Length feet]]+([@inches]/12))<12,SUM([@[Length feet]]+([@inches]/12)>10)),12,IF(AND(SUM([@[Length feet]]+([@inches]/12))<14,SUM([@[Length feet]]+([@inches]/12)>12)),14,IF(AND(SUM([@[Length feet]]+([@inches]/12))<16,SUM([@[Length feet]]+([@inches]/12)>14)),16,IF(AND(SUM([@[Length feet]]+([@inches]/12))<18,SUM([@[Length feet]]+([@inches]/12)>16)),18,IF(AND(SUM([@[Length feet]]+([@inches]/12))<20,SUM([@[Length feet]]+([@inches]/12)>18)),20,IF(AND(SUM([@[Length feet]]+([@inches]/12))<22,SUM([@[Length feet]]+([@inches]/12)>20)),22,FALSE)))))))))))))))))))


I used this for a l

 

[ksuthy]

yep that was the problem- thank you!

 

[sandy666]

but I still see SUM SUM

incorrect: SUM([@[Length feet]]+([@inches]/12)>20))
correct: SUM([@[Length feet]],[@inches]/12)>20,
or ([@[Length feet]]+[@inches]/12)>20,

SUM() ->>> syntax