If / and forumula

Hi I'm trying to get a formula that returns the following grades.

High Distinction (HD) 85-100

Distinction (D) 75-84

Credit (C) 65-74

Pass (P) 50-64

Fail (F) <50

I've tried this a few different ways but keep getting the #UNPARSEABLE message. Appreciate any help with trying to get this formula to work.

=IF(AND(Percentage1 < 50), "FAIL"), IF(Percentage1 > 50, Percentage1 < 65), "Pass", IF(Percentage1 > 64, Percentage1 < 75), "CREDIT", IF(Percentage1 > 74, Percentage1 < 85), "DISTINCTION", IF(Percentage1 > 84, Percentage1 < 101), "HIGH DISTINCTION")))))

Find more posts tagged with

Education

Formulas

Comments

Dartwohl

This may be as simple as a formatting issue. Looking at the following If/And example, can you try following bracket usage around your column name? =IF(AND([Value 1]39 > 75, [Value 2]39 > 75), "True", "False")

I'm not sure if this will work - just a suggestion!

Mike Wilday

Be aware that when using columns that are formatted to percentages you have to use decimal places to indicate a pecentage. I.E. =IF(Percentage1 < .5) .5 indicated 50% and 1 = 100%.

I also see a few other issues with your formula. Your first IF statement includes an AND but it doesn't seem to have 2 criterion.=IF(AND(Percentage1 < 50), "FAIL"), You aren't indicating two criterion here.

Also be aware that NESTED IF statements can't close until the end of the formula. HOWEVER your and statements should open and close within an IF.

IF(Percentage1 > 50, Percentage1 < 65), "Pass", -- the next part should have an AND statement here IF(AND(Percentage1 > 50, Percentage1 < 65), "Pass",

The others also have issues. Try this one:

=IF(Percentage1 < .5, "FAIL", IF(AND(Percentage1 > .5, Percentage1 < .65), "Pass", IF(AND(Percentage1 > .64, Percentage1 < .75), "CREDIT", IF(AND(Percentage1 > .74, Percentage1 < .85), "DISTINCTION", IF(AND(Percentage1 > .84, Percentage1 <= 1), "HIGH DISTINCTION")))))

Sherrie Love

Thanks to both of you. Mike that worked with a bit of an edit.

=IF(Percentage1 < 0.5, "FAIL", IF(AND(Percentage1 >= 0.5, Percentage1 <= 0.64), "PASS", IF(AND(Percentage1 >= 0.65, Percentage1 <= 0.74), "CREDIT", IF(AND(Percentage1 >= 0.75, Percentage1 <= 0.84), "DISTINCTION", IF(AND(Percentage1 >= 0.85, Percentage1 <= 1), "HIGH DISTINCTION")))))

Mike Wilday

Great, glad I could be of assistance.

Duza B

Mike, I had a similar issue and used the general structure you provided to get the formula to work for my use case. Thanks.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Issue with automation process reporting a date in a special column
In the Smartsheet sheet “CRS RO Master” we have programmed an automation called “Track Changes in dates, confirmation and LHG code” [PICTURE 1] This one should report a date of a change in the columns “CRS GO LIVE DATE” and “PMS Go LIVE DATE” in the column “. CRS Check date changed”. [PICTURE 2] The update of the “Go live…