Combine IF formulas in one cell

pyennaco

Hello,

I'm trying to create a %Complete column based on a drop down list column called "Status". For example, I'm looking to combine these formulas into one cell, then copy and paste to all other cells in the same column. I'm very new at this and cannot figure it out. If there is a better way top accomplish what I'm after, I'm open to other ideas!

In other words, when I select "Testing" in my Status column, I'm looking for the %Complete cell in the same row to auto-populate an "85%".

=IF(Status11 = "Done", "100%")

=IF(Status11 = "Testing", "85%")

=IF(Status11 = "Approval", "70%")

=IF(Status11 = "In Progress", "50%")

=IF(Status11 = "To Do", "10%")

=IF(Status11 = "Backlog", "0%")

I understand each row wouldn't be "Status11", but the next cell down would be "Status12", so on and so forth. I know that much.

Thanks,

Phil

Jim Hook

Hi Phil,

I'm not 100% sure I understand what you're trying to do but my first thought is that you can use a nested IF() function to combine multiple IF() formulas into one. The general idea of a nested IF function is as follows assuming the data type you're after is in cell "A1", for example:

=IF($A$1= "Done", B2,IF($A$1 = "Testing",B3,IF($A$1 = "Approval", B4, IF(......))))

Basically a nested IF looks at the first condition being true. If it is, then it leaves the value in the next argument in the cell. If not, it goes to the next IF function etc.. Keeping the dollar signs with the A1 cell reference keeps it from changing if you copy the formula and paste it down into additional rows.

There is a Smartsheet tutorial on nested IF() functions that might help also.

pyennaco

Hi Jim,

I referenced the tutorial on nested IF functions before I posted this, but referenced it again and was able to accomplish what I was after. I was probably forgetting something minor, but I have it working now.

Thanks very much for your response and help.

Cheers,

Phil