Nested IF , logical operator (AND)

Hi All,

I am trying to build a formula with IF, AND conditions but i am unsuccessful, can any one help me to sort out this please.

Here is the forumula

=IF(AND([Position at Practice]227 = "Receptionist", [Current MECA Step]227 = "Step 1"), "19.63",

IF ([Position at Practice]227 = “Receptionist”, [Current MECA Step]227 = "Step 2"), ”20.97”,

IF ([Position at Practice]227 = “Receptionist”, [Current MECA Step]227 = "Step 3"), ”21.98”,

IF ([Position at Practice]227 = “Receptionist”, [Current MECA Step]227 = "Step 4"), ”22.65”,

IF ([Position at Practice]227 = “Registered Nurse”, [Current MECA Step]227 = "Step 1"), ”25.00”,

IF ([Position at Practice]227 = “Registered Nurse”, [Current MECA Step]227 = "Step 2"), ”26.36”,

IF ([Position at Practice]227 = “Registered Nurse”, [Current MECA Step]227 = "Step 3"), ”28.54”,

IF ([Position at Practice]227 = “Registered Nurse”, [Current MECA Step]227 = "Step 4"), ”30.36”,

IF ([Position at Practice]227 = “Registered Nurse”, [Current MECA Step]227 = "Step 5"), ”33.43”,

IF ([Position at Practice]227 = “Enrolled Nurse”, [Current MECA Step]227 = "Step 1"), ”22.24”,

IF ([Position at Practice]227 = “Enrolled Nurse”, [Current MECA Step]227 = "Step 2"), ”23.59”,

IF ([Position at Practice]227 = “Enrolled Nurse”, [Current MECA Step]227 = "Step 3"), ”25.17”,”0”)

I am getting Unparseable error.

Thanks in advance.

Find more posts tagged with

Healthcare

Formulas

Comments

Andrée Starå

Hi,

Try this.

=IF(AND([Position at Practice]@row = "Receptionist"; [Current MECA Step]@row = "Step 1"); "19.63"; IF(AND([Position at Practice]@row = "Receptionist"; [Current MECA Step]@row = "Step 2"); "20.97"; IF(AND([Position at Practice]@row = "Receptionist"; [Current MECA Step]@row = "Step 3"); "21.98"; IF(AND([Position at Practice]@row = "Receptionist"; [Current MECA Step]@row = "Step 4"); "22.65"; IF(AND([Position at Practice]@row = "Registered Nurse"; [Current MECA Step]@row = "Step 1"); "25.00"; IF(AND([Position at Practice]@row = "Registered Nurse"; [Current MECA Step]@row = "Step 2"); "26.36"; IF(AND([Position at Practice]@row = "Registered Nurse"; [Current MECA Step]@row = "Step 3"); "28.54"; IF(AND([Position at Practice]@row = "Registered Nurse"; [Current MECA Step]@row = "Step 4"); "30.36"; IF(AND([Position at Practice]@row = "Registered Nurse"; [Current MECA Step]@row = "Step 5"); "33.43"; IF(AND([Position at Practice]@row = "Enrolled Nurse"; [Current MECA Step]@row = "Step 1"); "22.24"; IF(AND([Position at Practice]@row = "Enrolled Nurse"; [Current MECA Step]@row = "Step 2"); "23.59"; IF(AND([Position at Practice]@row = "Enrolled Nurse"; [Current MECA Step]@row = "Step 3"); "25.17"; "0"))))))))))))

The same version but with the below changes for your and others convenience.

=IF(AND([Position at Practice]@row = "Receptionist", [Current MECA Step]@row = "Step 1"), "19.63", IF(AND([Position at Practice]@row = "Receptionist", [Current MECA Step]@row = "Step 2"), "20.97", IF(AND([Position at Practice]@row = "Receptionist", [Current MECA Step]@row = "Step 3"), "21.98", IF(AND([Position at Practice]@row = "Receptionist", [Current MECA Step]@row = "Step 4"), "22.65", IF(AND([Position at Practice]@row = "Registered Nurse", [Current MECA Step]@row = "Step 1"), "25.00", IF(AND([Position at Practice]@row = "Registered Nurse", [Current MECA Step]@row = "Step 2"), "26.36", IF(AND([Position at Practice]@row = "Registered Nurse", [Current MECA Step]@row = "Step 3"), "28.54", IF(AND([Position at Practice]@row = "Registered Nurse", [Current MECA Step]@row = "Step 4"), "30.36", IF(AND([Position at Practice]@row = "Registered Nurse", [Current MECA Step]@row = "Step 5"), "33.43", IF(AND([Position at Practice]@row = "Enrolled Nurse", [Current MECA Step]@row = "Step 1"), "22.24", IF(AND([Position at Practice]@row = "Enrolled Nurse", [Current MECA Step]@row = "Step 2"), "23.59", IF(AND([Position at Practice]@row = "Enrolled Nurse", [Current MECA Step]@row = "Step 3"), "25.17", "0"))))))))))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

I hope this helps you!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

I feel like it might be easier to build a table and use an INDEX/MATCH to pull the number. It would allow for much easier updates in the future and provide for less chance of a typo in a super long nested IF statement. The below formula would go in the Number column of the main sheet (second screenshot) and references a table on a separate sheet (first screenshot).

=INDEX({Table Numbers}, MATCH(Position@row, {Table Position}, 0), VALUE(RIGHT(Step@row)))

{Table Numbers} is a cross sheet reference that encompasses all of the Step columns in the table.

{Table Position} is a cross sheet reference that covers the Position column of the table.

Andrée Starå

Paul,

Nice and I agree, but my experience is that most users prefer the long formulas against the more advanced functions and especially if they are new to Smartsheet.

Great solution regardless!

Have a fantastic weekend!

Best,

Andrée

Paul Newcome

True, but sometimes you just gotta go ahead and throw them in the deep end. HAHAHA!!!

Edit: We won't throw you in without also tossing in a line for you...

Andrée Starå

The edit

Sign in to join the conversation!

Quick Links

Trending discussions

Break Up Multi-Entry Row
I'm using a form entry (into an Intake Sheet) in which someone can select multiple locations for the same Initiative. I'm looking to copy the row to a different Master sheet, but would like to have the same row copied multiple times, once per location selected. Intake Sheet: Desired Result in Master Sheet: Basically, I…
Set contents of two columns into 1
Hi - trying to figure out a formula that combines two columns into one long column, maintaining contents in multiple rows. Noting that the number of items in each column will change as these are each using the index/distinct to summarize a separate sheet. For example: Thanks for any help!
Unparsable formula; Sheet summary SUMIF formula
Can anyone help why this formula is coming up as unparseable? I'm creating a sheet summary, where it totals the Estimated GP (Est. GP) column if the Status column has the "Quoted" drop-down option is selected. Formula i've used: =SUMIF(Status:Status,"Quoted",Est. GP:Est. GP) Thanks a lot!