Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

How can I replicate a MS Excel 'IF' statement in a form

Level 2

Hello

I would like to replicate a microsoft Excel 'if' statement in a Livecycle form.

The excel statement is: =IF(AG36=1,"5",IF(AG36>0.97,4,IF(AG36>0.84,"3",IF(AG36>0.3,"2","1"))))

Which is based around %. The 1 in this statement equals 100%, then the 0.97 would be 97% and so on...

AG36 is a cell in Excel, in my form the related field is called: Table1.Row9.Cell5

Would I use straight forward FormCalc or is this a JavaScript solution?

The form is saved as Adobe 7 Dynamic. I use LC8.

1 Accepted Solution

Level 2

I have solved this issue; The formular required in LiveCycle to replicate a MS Excel formular of: =IF(AG36=1,"5",IF(AG36>0.97,4,IF(AG36>0.84,"3",IF(AG36>0.3,"2","1"))))

was: (Note 'Objectives_subform.Table1.Row9.Cell5' is equivelant to 'AG36' in Excel)

IF(Objectives_subform.Table1.Row9.Cell5 >= 100, "5", IF(Objectives_subform.Table1.Row9.Cell5 >= 97, "4", IF(Objectives_subform.Table1.Row9.Cell5 >= 84, "3", IF(Objectives_subform.Table1.Row9.Cell5 >= 30, "2", IF(Objectives_subform.Table1.Row9.Cell5 <= 29, "1")))))

Chuffed now, might have the rest of today off now!

Jamie

2 Replies

Level 2

Hello again, I have an update to this question...

In LiveCycle, if I use Formcalc and set the field (numeric) to Calculate and enter this formular;

IF(Objectives_subform.Table1.Row9.Cell5

>= 100, "5",)

I can achieve part of my goal. However I want to use four IF statements in the same formular and LC doesn't seem to like it so far, here's what I tried:

IF(Objectives_subform.Table1.Row9.Cell5 >= 100, "5") IF(Objectives_subform.Table1.Row9.Cell5 >= 97, "4") IF(Objectives_subform.Table1.Row9.Cell5 >= 84, "3")

I currently get a synax error. Does anyone know how I would link several IF statements correctly?

Many thanks