You are viewing limited content. For full access, please sign in.

Question

Question

Using FX Function for Row, but calc unique for each row in a certain column

Forms
Updated September 7, 2020
asked on September 7, 2020 • Show version history

Hi,

Is there a way to use IF with PRODUCT if a Table has 5 rows and each row in a certain column is unique function, so the the No  of Items listed below will have a unique calculation for each row based on variables from the form. The table is set to 5 rows, not allowing user to add more rows or take away.

 

What I would like to achieve is on the "No of Items column" using an IF function to determine for example: if it is row 1 then do the following =PRODUCT(Value1,100) if it is row 2 then =PRODUCT(SUM(Value1,Value2),100) or if it is row 3 then =PRODUCT(Value2,100)

 

The values are number fields which is not in a table.

I would like to explore this first before resorting to JAVA Scripting Calculations.

Regards,

Gert

0 0

Answer

SELECTED ANSWER
replied on September 7, 2020

You can use nested IF function with Index and Row such as following:

IF(ROW()=1,PRODUCT(INDEX(Table1.B,ROW()),100),IF(ROW()=2,PRODUCT(INDEX(Table1.B,ROW()),INDEX(Table1.C,ROW()))，0）)

0 0
replied on September 7, 2020

Hi Xiuhong,

 

Thank you, this is working perfectly! Much appreciated.

0 0
replied on September 18, 2020 • Show version history

Hi @Xiuhong,

In  table 2, i have a column name No of Items, using IF, Row 1 is equal to Table 1.Number of entities,1 and then Table2 no of items row 5 should be the sum of table 2 no of items row 2,3 and 4. Below is the function i tried using, but it is not working. I have also tried to create separate fields to do the calculation in and out of the table, but it errors out and shows circular reference.

I have another question that goes with the initial question, =IF(ROW()=1,SUM(INDEX(Table1.Number_of_entities,1)),IF(ROW()=5,SUM(INDEX(Table2.No_of_items,2),INDEX(Table2.No_of_items,3),INDEX(Table2.No_of_items,4)),0))

 

Any thing else i could possibly try?

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...