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
-
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.
-
Thank you. This helps. It's just tedious having to replicate the formulas in Smartsheet.
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!