Excel formulas to Smartsheet formulas.

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

Answers

  • Paul Newcome
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

  • Paul Newcome
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!