IF Statement Does Not Copy on Rows Added Via Form

Carlyn F

I have an IF statement that calculates an expense amount field based on mileage entered:

IF(MileageField>0,MileageField*.585)

If there isn't a value in the MileageField, then the expense amount field is just whatever dollar amount is entered (all other types of expenses). These rows are populated by a form, which enters the new rows at the top of the sheet. The IF statement does not populate when a new row is added by a form. Is there a workaround here besides having to manually copy the IF statement to all new rows? Thanks for your help!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/322778#Comment_322778

When a column name is more than one word, or contains a number or special character, you need to put the column name in square brackets. Also, capitalize function names in formulas, and I put spaces after commas just for clarity's sake:

=IF(ISNUMBER(Mileage@row), Mileage@row *0.585, [Expense Not Mileage]@row)

Jeff Reisman

@Carlyn F

You can't have a formula field and also enter something manually into that same field. You'll also want to make your formula a column formula to make sure it shows up on new rows. You would then NOT be able to enter anything manually into that field on a form.

What it sounds like you need is another column for "OtherExpenses" that can be entered manually, and have the Expense Amount field be a formula like this:

=IF(ISNUMBER(MileageField@row), MileageField@row * 0.585, OtherExpenses@row)

In English, if there's a number in the MileageField, multiply it by 0.585, otherwise, populate this cell with whatever is in the OtherExpenses field on this row.

Once you are sure this formula does what you want, right click and choose Convert to Column Formula at the bottom.

On your form, users can enter numbers into the MileageField and/or into OtherExpenses, and behind the scenes the MileageField will always override the OtherExpenses field when it comes to populating the Expense Amount column.

Carlyn F

Oh my gosh this is a great solution! Thank you so much!!

Jeff Reisman

@Carlyn F Glad it helped you!

Carlyn F

@Jeff Reisman

Uh, so it is still a great solution, but I can't get my formula to work. Can you look at it please?

=if(ISNUMBER(Mileage@row),Mileage@row*585,Expense Not Mileage)

Not sure what I'm missing here, I've tried adding spaces and extra parentheses, but it is giving me the dreaded #UNPARESEABLE error. Can you see what I've got wrong?

Carlyn F

@Jeff Reisman

Shoot! You are going to be so happy that you tried to help me. I left off the @row specification at the end of my formula, although it still doesn't work when it is there. Here it is:

=if(ISNUMBER(Mileage@row),Mileage@row*585,Expense Not Mileage@row)

Jeff Reisman

https://community.smartsheet.com/discussion/comment/322778#Comment_322778

When a column name is more than one word, or contains a number or special character, you need to put the column name in square brackets. Also, capitalize function names in formulas, and I put spaces after commas just for clarity's sake:

=IF(ISNUMBER(Mileage@row), Mileage@row *0.585, [Expense Not Mileage]@row)

Carlyn F

I made the changes and it works perfectly! Thank you so much!!!