Expression Error.

[#1]

Dear Experts,

Please Help me to find the error in following Expression.

If(ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

   SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100,0)),

   SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100,0)),

   SUM(IF(RANGESUM(BRO_ORC+AGE_ORC+GRO_ORC=0) and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

  -SUM(CRE))) <0,

   ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

   SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100)),

   SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100)),

   SUM(IF(BRO_ORC=0 and AGE_ORC=0 and GRO_ORC=0 and ORC_COM>0,ALT(SUM(COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

  -SUM(CRE))))

[#2]

Finlay i tried the below one,

But still it is not working.

If(ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

   SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100,0)),

   SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100,0)),

   SUM(IF(RANGESUM(SUM(BRO_ORC),SUM(AGE_ORC),SUM(GRO_ORC))=0 and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

   -SUM(CRE))) <0,

   ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

   SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100)),

   SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100)),

   SUM(IF(RANGESUM(SUM(BRO_ORC),SUM(AGE_ORC),SUM(GRO_ORC))=0 and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

  -SUM(CRE)))).

Priyantha.

[#3]

Can you please break it into smaller pieces like first investigate value of below:

ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

   SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100,0)),

   SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100,0)),

   SUM(IF(RANGESUM(SUM(BRO_ORC),SUM(AGE_ORC),SUM(GRO_ORC))=0 and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

   -SUM(CRE)))

one by one remove the lines to see where you get values, and then add them again.

[#4]

OK..

[#5]

By remaining line by line it was observed the bold and underlined parts makes the error.

If(ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

   SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100,0)),

   SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100,0)),

   SUM(IF(RANGESUM(SUM(BRO_ORC),SUM(AGE_ORC),SUM(GRO_ORC))=0 and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

   -SUM(CRE))) <0,

   ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

   SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100)),

   SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100)),

  //SUM(IF(RANGESUM(SUM(BRO_ORC),SUM(AGE_ORC),SUM(GRO_ORC))=0 and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

  -SUM(CRE))))

Please anyone can suggest something new instead of mine.

Priyantha.

[#6]

Can you replace the bold line with below:

IF(RANGESUM(SUM(BRO_ORC),SUM(AGE_ORC),SUM(GRO_ORC))=0 and ORC_COM>0,SUM((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

[#7]

Dear Angad,

Even though i replace the part, status of the expression shows error in expression.

Thanks.

Priyantha.

[#8]

ohh sorry, you need to remove extra ) from right before comma.

IF(RANGESUM(SUM(BRO_ORC),SUM(AGE_ORC),SUM(GRO_ORC))=0 and ORC_COM>0,SUM((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0),

[#9]

Dear Angad,

Also failed. anyway thanks for the great help.

Priyantha.

[#10 · Accepted solution]

No Idea..........

Will try an other way

Thanks lot.

Priyantha.