Complex Excel formula to Smartsheet
I have a complex formula in Excel that I am tying to duplicate in smartsheet. Does anyone have an idea on how to make the following work. Its basically going through a series of conditions until it pull the correct answer.
=IF(ISBLANK(AG7),IF(ISBLANK(AC7),IF(ISBLANK(X7),IF(ISBLANK(T7),IF(ISBLANK(P7),IF(ISBLANK(K7),IF(ISBLANK(G7),"Not Started",(G7+(I7+N7+R7+V7+AA7+AE7+AI7)/5*7)),(K7+(N7+R7+V7+AA7+AE7+AI7)/5*7)),(P7+(R7+V7+AA7+AE7+AI7)/5*7)),(T7+(V7+AA7+AE7+AI7)/5*7)),(X7+(AA7+AE7+AI7)/5*7)),(AC7+(AE7+AI7)/5*7)),(AG7+(AI7/5*7)))
I made it work through the bolded text. After that I came up empty.
Thanks,
Answers

Hi @Luis Perez
I have to be honest and say that I actually don't understand Excel formulas at all! Is there any way you could post your current Smartsheet sheet & the formula you've sorted out so far, explaining what you want the outcome to be? (But please block out any sensitive data in the sheet! Maybe create a copy with dummy data?)
From what I can tell in the first bit, up to the "Not Started", you want to look across a row to see if all the cells are blank, and if they are, return "Not Started".
I would do this with a COUNT formula to count if the complete range is empty:
=IF(COUNT(AG@row:G@row) = 0, "Not Started"
Note that the range is from the cell in this row from the first column (AG@row) across the row to the cell in the row of the last column (G@row). To make it a range, I have the colon between them (AG@row:G@row). This presumes all your columns are together in one row.
Then I actually don't know what you're looking to do with the rest of the formula. Are you checking the last column, and if it's not blank, adding it to the rest of the row?
So,
IF(G@row <> "", SUM(G@row:AI@row)/5*7
<> "" says "not blank", although, if the G cell was blank, it wouldn't be included in the SUM function, so I'm not sure you need the IF statement.
Try this:
=IF(COUNT(AG@row:G@row) = 0, "Not Started", SUM(G@row:AI@row)/5*7)
I could be totally wrong with what you're trying to do, which is where a screen capture of your sheet and a more detailed explanation would be very useful!!
Thanks,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 379 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives