Complex Excel formula to Smartsheet

Luis Perez

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,

Genevieve P.

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