Multiple IF Statements

Genevieve P.

Glad I could help!

dapistar

how do I average multiple IFs?

=AVG(IF(Kabasalan10 = "Completed", 4, IF(Kabasalan10 = "ongoing", 3, IF(Kabasalan10 = "started / Delayed", 2, IF(Kabasalan10 = "Not Started", 1)))), IF(Kabasalan11 = "Completed", 4, IF(Kabasalan11 = "ongoing", 3, IF(Kabasalan11 = "started / Delayed", 2, IF(Kabasalan11 = "Not Started", 1))))),IF(Kabasalan12 = "Completed", 4, IF(Kabasalan12 = "ongoing", 3, IF(Kabasalan12 = "started / Delayed", 2, IF(Kabasalan12 = "Not Started", 1))))

i can add two cells, but if i add a third one, it becomes #unparseable

thanks

Genevieve P.

Hi @dapistar

Can you explain exactly what it is you're looking to do? I see three rows referenced here. Are you wanting to assign a value to each status, then average across just 3 rows?

If you want to average across the whole sheet, what I would personally do is set up a helper column that assigns the numerical value per row, like so:

=IF(Kabasalan@row = "Completed", 4, IF(Kabasalan@row = "ongoing", 3, IF(Kabasalan@row = "started / Delayed", 2, IF(Kabasalan@row = "Not Started", 1))))

Then you can AVG that column in a new formula.

Cheers,

Genevieve

Lauren P

Hello, I'm trying to do a variation of a status formula but getting #UNPARSEABLE error message.

I have four columns: Status, Start Date, End Date, Percentage Complete

I am trying to add the correct status from this LOV: TBD (meaning not started), Completed, Delayed, In Progress

It's the delayed status that I'm having trouble with. The formula someone shared above worked flawlessly for not started, completed and in progress; but when I try to add the End Date as part of the expression is when I start to see the error.

Here is my formula without issue but before trying to account for going past the due date:

Here is problem formula after trying to add going past the due date while not being at 100% complete - get #UNPARSEABLE error:

I think the highlighted end parenthesis is my culprit, but I also tried it without and get different error #INCORRECT ARGUMENT SET:

Any idea what I'm doing wrong?

Mike Wilday

You need to remove that highlighted parenthesis and put it after the AND statement closes. Before the ,"Delayed" bit. You have to close the AND statement.

Mike Wilday

This formula should do the trick for you.

=If([Percentage Complete]@row = 0, "TBD", IF([Percentage Complete]@row = 1, "Completed", IF(AND([End Date]@row < Today(), [Percentage Complete]@row <>1), "Delayed", "In Progress")))

Lauren P

Ty @Mike Wilday that worked perfectly!

h_inda

Hello,

Could someone help me,

I am trying to have "denied" automatically chosen in my dropdown list in my STATUS column when another column (MINIMUM REQUIREMENTS UNMET) is checked. If MINIMUM REQUIREMENTS UNMET is unchecked, I want my other options ("Accepted", "Not Reviewed", "Reviewed) to be able to be manually checked.

This gave me an error message:

=IF([Minimum Requirement not met]@row = 1, "Denied", IF([Minimum Requirement not met]@row = 0, "Accepted", "Not Reviewed", "Reviewed"))

Any suggestion?

Genevieve P.

Hi @h_inda

You can either have a formula in the cell to automatically output an answer, or you can manually adjust the dropdown list. If there's a formula in a cell and then you change the value it will clear out the formula.

I would suggest using an automated workflow to update the cell data instead! Here's more information:

https://help.smartsheet.com/articles/2482299-change-cell-value-in-an-automated-workflow

Cheers,

Genevieve

dylanchaney

Hi, I have a question regarding a nested IF conditional that counts the next Friday after a date. What I'm attempting to do is to leave the cell blank if the captured date is blank.

CONTEXT:

My form has a column for manual input ([Actual Date of Publishing]) that I translate into the Friday after that date ([End of Week by Publishing Date]).

My current successful formula to translate the date is the below nested IF conditional:

=IF(WEEKDAY([Actual Date of Publishing]@row) = 1, [Actual Date of Publishing]@row + 5, IF(WEEKDAY([Actual Date of Publishing]@row) = 2, [Actual Date of Publishing]@row + 4, IF(WEEKDAY([Actual Date of Publishing]@row) = 3, [Actual Date of Publishing]@row + 3, IF(WEEKDAY([Actual Date of Publishing]@row) = 4, [Actual Date of Publishing]@row + 2, IF(WEEKDAY([Actual Date of Publishing]@row) = 5, [Actual Date of Publishing]@row + 1, IF(WEEKDAY([Actual Date of Publishing]@row) = 6, [Actual Date of Publishing]@row, IF(WEEKDAY([Actual Date of Publishing]@row) = 7, [Actual Date of Publishing]@row + 6)))))))

This effectively captures the Friday dates I need (see rows 3, 4, 6-10), provided there's value in [Actual Date of Publishing]@row. Where I'm getting an #INCORRECT ARGUMENT SET error is when I try to capture the blank [Actual Publishing Date] cell with:

=IF(ISBLANK([Actual Date of Publishing]@row, "", IF(WEEKDAY([Actual Date of Publishing]@row) = 1...

How can I capture that [Actual Date of Publishing]@row is BLANK and translate that to a BLANK cell in [End of Week by Publishing Date]?

Genevieve P.

Hi @dylanchaney

You're very close!

If you want to use ISBLANK, you'll just need to ensure you're closing off the function before moving on to your next statement:

=IF(ISBLANK([Actual Date of Publishing]@row) < this closing parentheses was missing.

I personally prefer to use = "" instead of ISBLANK so as to reduce parentheses.

Try this at the front:

=IF([Actual Date of Publishing]@row = "", "",

Cheers,

Genevieve

dylanchaney

@Genevieve P. thank you! I see how this could work and where I missed my closing parentheses. I was informed that I could solve this issue too by taking my entire formula, excluding the ISBLANK part, and wrapping it in an IFERROR. I realized my issue with the IFERROR before was that I was using it in place of my first IF. Thank you!

ainsley

Hello :)

I am trying to nest a bunch (19 if I'm counting correctly) of IF statements, and I keep getting the Unparseable message. I've been through this thread multiple times trying to figure out where I'm going wrong. I want it to return an account number based on a selected country. Here is what I've got right now:

=IF(Country@row = "Finland", "ACLAFI240", 

IF(OR(Country@row = "Ukraine", Country@row = "Poland", Country@row = "Russia", Country@row = "Italy"), "SPALUK021", 

IF(OR(Country@row = "Spain", Country@row = "France", Country@row = "Portugal"), "AURASP301", 

IF(Country@row = "Chile", "BELLCH012", 

IF(Country@row = "Guatemala", "BELLCH178", 

IF(Country@row = "Colombia", "BELLCO929", 

IF(OR(Country@row = "Switzerland", Country@row = "Austria"), "ECOMSW430", 

IF(OR(Country@row = "UK", Country@row = "Caribbean"), "HARPUK053", 

IF(Country@row = "Australia", "HIGHAU009", 

IF(Country@row = "GCC", "LEADGC971", 

IF(Country@row = "India", "LEADIN414", 

IF(Country@row = "Turkey", "NEOMTU810", 

IF(OR(Country@row = "Germany", Country@row = "Denmark", Country@row = "Sweden", Country@row = "Belgium", Country@row = "Netherlands", Country@row = "Norway"), "NOSCDE414", 

IF(OR(Country@row = "Vietnam", Country@row = "Indonesia", Country@row = "Malaysia"), "REDOID144", 

IF(OR(Country@row = "Greece", Country@row = "Cyprus"), "RELICY024", 

IF(Country@row = "Canada", "RGRPCA622", 

IF(Country@row = "Mexico", "SISTME800", 

IF(Country@row = "South Africa", "STERSA196", 

IF(Country@row = "China", "WPICCN150", )))))))))))))))))))

I'm fairly new to this and only actually discovered the IF(OR bit today. Any assistance would be much appreciated. I'm at the point where my eyes are starting to cross!

Thank you!

Genevieve P.

Hi @ainsley

For being new you've done a fantastic job!

The error is because there's an extra comma at the very end of your formula.

IF(Country@row = "China", "WPICCN150", )))))))))))))))))))

Since this is the end, you want to close off that statement instead of having a comma:

IF(Country@row = "China", "WPICCN150")))))))))))))))))))

Let us know if that did the trick 🙂

ainsley

Hi @Genevieve P. - it worked! Thank you so much. It's amazing that such a small thing can throw it all off 😆 Thanks again!