Question

How to use Index and IF to calculate only first row of table

Forms

Updated March 31, 2020

asked on March 30, 2020

I have a fixed row table with four rows. I want to multiply any values in Row 1 by -1, otherwise if not in Row 1, don't multiply by -1. I'm thinking an IF statement with the INDEX function should work, but I'm not clear on the syntax.

Perhaps something like this:

=IF(INDEX(vProjectCost.Year_0,1),MULT(vProjectCost.Year_0,-1),MULT(vProjectCost.Year_0,1))

This formula doesn't appear to return anything, so I'd appreciate it if someone could take a look and let me know if I'm heading in the right direction or not.

Thanks.

1 0

Replies

replied on March 31, 2020

Totals would need to a separate table or fields as you can't do that in your ProjectCost Table itself.

Seeing your table, there is no formula that would provide the Minus value across row 1. Possibly this could be done with JS, which is also how I would change the values to RED.

You could make Cost and Saving as two separate tables and just hide the Table Name and Fields using CSS which would still give you a look such as this. I would do the same thing for the Totals. If you did Cost and Saving separately, it would be easy to create the totals with a formula.

Myself, changing the Cost to Red would negate the requirement to have those fields as Negative but I leave that up to you

2 0

replied on March 31, 2020

Hi Mike

Add a new Column to your Table, Call it ROWID, and in the Field, but the Formula =ROW()

When you look at your table you will see the ROWID column will change from 1 to 4 down through the table. You can then use this field in your other calculation and hide it with a field rule.

Your other formula would then look like

=IF(INDEX(yProjectCost.ROWID,ROW())=1,MULT(INDEX(vProjectCost.Year_0,ROW()),-1,MULT(INDEX(vProjectCost.Year_0,ROW()),1)

1 0

View 8 previous replies

replied on March 31, 2020

Hi Steve,

I'm getting an invalid syntax error with your formula. I did correct one typo "yProjectCost" but it's still invalid syntax.

=IF(INDEX(vProjectCost.ROWID,ROW())=1,MULT(INDEX(vProjectCost.Year_0,ROW()),-1,MULT(INDEX(vProjectCost.Year_0,ROW()),1)

0 0

replied on March 31, 2020 • Show version history

Not enough Brackets from what I can see, corrected below

=IF(INDEX(vProjectCost.ROWID,ROW())=1,MULT(INDEX(vProjectCost.Year_0,ROW()),-1),MULT(INDEX(vProjectCost.Year_0,ROW()),1))

Also, it important to know your field types

If you ROWID was a Single Line field instead of a number, your formula would be

=IF(INDEX(vProjectCost.ROWID,ROW())="1",MULT(INDEX(vProjectCost.Year_0,ROW()),-1),MULT(INDEX(vProjectCost.Year_0,ROW()),1))

0 0

replied on March 31, 2020

I am also assuming your calculation field is a Number or currency field

0 0

replied on March 31, 2020

All fields except ROWID are Currency; ROWID is a number.

0 0

replied on March 31, 2020

Fixing the brackets resolved the invalid syntax error; now I'm getting a circular reference.

0 0

replied on March 31, 2020

Is your calculation in the vProjectCost.Year_0 field? You cannot have a calculation use itself in the formula (same rules as excel).

What is the MULT Calculation supposed to provide you as an outcome that it needs to be done?

0 0

replied on March 31, 2020

Yes, the calculation is in the Year_0 column. Perhaps it needs to be in the ROWID column. The end result would be to have all columns in Row 1 multiply by -1 to get a negative number.

0 0

replied on March 31, 2020

How are the fields being populated? Lookup? Manual?

You may need to create another column, let's call it CalcYr where the formula would go. It would then refer the value in the Year_0 field as above to create the correct output.

0 0

replied on March 31, 2020

Manually populated fields. It's for a Capital Expenditure Request, so there are ten Year fields to project the cost of the capital expenditure over ten years plus the current year (Year_0 through Year_10). The costs go in row 1, and the projected savings, if any, go in rows 2-4.

0 0

replied on March 31, 2020 • Show version history

Why does Year 0 need to be a Minus (-) value?

We could add some JS to display ROW1 in Red, if it's just a visual you are looking for.

0 0

replied on March 31, 2020

The table needs to be able to show the Net of costs and savings. Costs would be a negative number and savings would be a positive number. I will probably add another row in table for the Net calculation unless it works better as a separate group of individual fields or another table.

It's not just Year_0 that will have negative numbers; all columns in Row 1 need to be multiplied by -1.

0 0

replied on March 30, 2020

Just tried to use ROW() to determine if it's Row 1, but this doesn't return a result either.

=IF(ROW()=1,MULT(vProjectCost.Year_0,-1),MULT(vProjectCost.Year_0,1))

0 0

replied on March 30, 2020

Doing some trial and error testing...

The following works:

=IF(ROW()=1,MULT(1,-1),MULT(1,1))

But when I replace the hard-coded parameter "1", with a reference to the column I want to multiply, I get a circular reference

=IF(ROW()=1,MULT(vProjectCost.Year_0,-1),MULT(1,1))

I'm guessing it has to do with the relative row/column reference for the column Year_0, but I'm still new at these functions.

0 0

replied on March 31, 2020

Ok, that makes sense. I'll work on separate tables for costs and savings. Thank you for your time.

0 0

You are not allowed to follow up in this post.

Question

Question

How to use Index and IF to calculate only first row of table

Replies

Sign in to reply to this post.