Excel formulas to Smartsheet formulas.

Sidonia Posteuca

The following formulas did not transfer when importing an excel sheet into Smartsheet. They appear as #INVALID or #UNPARSEABLE. Are the following Excel formulas able to convert to Smartsheet formulas? If so what are those Smartsheet formulas?

=IF(ISERR(ROUND(SUM(K12/G12),2)),0,ROUND(SUM(K12/G12),2))

=SUM(G12:G13)

=SUM(HrstoComp)

=IF(LtoComp=0,0,LtoComp/G24)

=SUM(G24+G14)

=IF(HrsProjPrev=0,G30+HrsProjPrev,G30-HrsProjPrev)

=IF(LProjPrev=0,K30+LProjPrev,K30-LProjPrev)

=D7-Summary!F64

=Summary!H64-Worksheet!D7

=V13+MPrev

Paul Newcome

You will need to start by making sure column references are correct. If your Smartsheet does not contain a column "K", then any excel formula referencing that particular column name will fail.

Next, the ISERR in excel is ISERROR in Smartsheet. That looks like the only function that is off.

You also need to follow the proper steps for creating cross sheet references. Referencing another sheet in Smartsheet is VERY different from in excel. HERE is an article that explains how to reference another sheet in Smartsheet.

Sidonia Posteuca

Thank you. This helps. It's just tedious having to replicate the formulas in Smartsheet.

Paul Newcome

Happy to help! 👍️

I agree. It can become very tedious very quickly. There are a few "shortcuts" that can help the process along, but it is still a process.

mrkuramoto

I know that the question was regarding another subject but I would like to leave a tip related to the formula using ISERROR.

There is formula in both Excel and Smartsheet called IFERROR and it is very simple: =IFERROR(X;Y)

X = in case NO error............ Y = in case of error

example: =IFERROR("Q" + INT((MONTH(Start@row) + 2) / 3) + " - " + YEAR(Start@row), "")

This avoid to duplicate the whole formula to cover both situations.