Question

Error in table when calculating Line Total

Updated November 17, 2016

asked on November 16, 2016

I have a simple table that calculates a Line Total from Quantity and Price. All fields are 'number' fields set to 2 decimal places.

When I add the Quantity, I immediately get the error "This field contains a calculation error" on the Line Total field. I am using this Forms 10.1 Calculation for the Line Total:

=INDEX(Table.Qty,ROW()) * INDEX(Table.Price,ROW())

According to a previous post, Forms 10.1 will take any blank, e.g. Price, as being 0. This should cause the Line Total to be 0 until a Price is entered.

Is there a better formula that I can use?

0 0

Answer

SELECTED ANSWER

replied on November 17, 2016

That will work if you don't mind the blank value being treated as 1 and not 0 in the PRODUCT formula. You'll notice that if you set Qty as 2 and leave the Price blank, then the Total will be 2.

Otherwise, if you want the blank value to be treated as 0, then you can use either of these formulas:

=PRODUCT(IF(INDEX(Table.Qty,ROW())="",0,INDEX(Table.Qty,ROW())),IF(INDEX(Table.Price,ROW())="",0,INDEX(Table.Price,ROW())))

=IF(INDEX(Table.Qty,ROW())="",0,INDEX(Table.Qty,ROW())) * IF(INDEX(Table.Price,ROW())="",0,INDEX(Table.Price,ROW()))

0 0

Replies

replied on November 16, 2016

I did not want to add a default because if the "mouse" to it they have to have extra keystrokes to delete the default first.

I take it from your answer that there is no other solution when using the "*" function.

I have now changed the formula to:

=PRODUCT( INDEX(Table.Qty,ROW()) , INDEX(Table.Price,ROW()) ) which does not give an error.

1 0

SELECTED ANSWER

replied on November 17, 2016

That will work if you don't mind the blank value being treated as 1 and not 0 in the PRODUCT formula. You'll notice that if you set Qty as 2 and leave the Price blank, then the Total will be 2.

Otherwise, if you want the blank value to be treated as 0, then you can use either of these formulas:

=PRODUCT(IF(INDEX(Table.Qty,ROW())="",0,INDEX(Table.Qty,ROW())),IF(INDEX(Table.Price,ROW())="",0,INDEX(Table.Price,ROW())))

=IF(INDEX(Table.Qty,ROW())="",0,INDEX(Table.Qty,ROW())) * IF(INDEX(Table.Price,ROW())="",0,INDEX(Table.Price,ROW()))

0 0

replied on November 17, 2016

I came to the same sort of conclusion myself and have used:

=IF(AND( INDEX(Table.Qty,ROW())>0,INDEX(Table.Price,ROW())>0),(PRODUCT( INDEX(Table.Qty,ROW()) , INDEX(Table.Price,ROW()))),0)

1 0

replied on November 17, 2016

Nice Peter!

Thanks for digging into that and sharing your findings.

Steve

0 0

replied on November 16, 2016

Is it sufficient to set 0 as the default value in the number fields? This way you won't have to modify the formula and the Total will remain at 0 until both columns have a non-zero value.

0 0

You are not allowed to follow up in this post.

Question

Question

Error in table when calculating Line Total

Answer

Replies

Sign in to reply to this post.