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]

Try below:

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))))

[#3]

Dear Angad,

It is already tested and failed.

Priyantha.

[#4]

Quick response highly appreciated.

[#5]

try this then:

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))))

[#6]

If the above does not work, then final try:

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(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))))

[#7]

Did not work.

[#8]

Dear Angad,

Final one also failed..

Can you explain the error i have made in my 1st attempt?

Priyantha.

[#9]

The error line is in bold below from your original exp:

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))))

1) in RANGESUM you need comma separated values

2) the brackets of the IF should be before the =

The best thing will be to test the expression by breaking it into smaller peices.

Thanks,

Singh

[#10]

Thkx.