Average for both columns

RobNY2

I have a dropdown columns with several choices and each choice represents % complete. The idea is to get the Circuit % complete for 1st and 2nd circuits columns but also It has ‘N/A” that will not count. It only count the column that doesn’t have it.

This is what I tried:

=AVG(IF([1st Circuit Status]@row = "Assigned to Local Carrier", 0.1), IF([1st Circuit Status]@row = "Circuit Delivery", 1),IF([1st Circuit Status]@row = "Circuit in Construction Phase", .8),IF([2nd Circuit Status]@row = "Assigned to Local Carrier", 0.1),IF([2nd Circuit Status]@row = "Circuit Delivery", 1),IF([2nd Circuit Status]@row = "Circuit in Construction Phase", .8)

If you could help me, I really appreciate it!

Rob

Genevieve P.

Hi @RobNY2

If I'm understanding you correctly, you have one specific value that you want to assign as a percentage per choice, and there are two columns where these values could appear. In each row you want to AVG the two values from those two cells for a final percentage.

If this is the case, I believe you're missing a comma from the first formula. This is the structure:

=SUM(first value, second value) / 2

However notice that when you swap from doing an IF statement for the 1st Circuit Status there's no comma. It should be:

=SUM(IF([1st Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([1st Circuit Status]@row = "Circuit Delivery", 1) + IF([1st Circuit Status]@row = "Circuit in Construction Phase", 0.8), IF([2nd Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([2nd Circuit Status]@row = "Circuit Delivery", 1) + IF([2nd Circuit Status]@row = "Circuit in Construction Phase", 0.8)) / 2

Since you have a much longer list, what I would suggest doing is two Nested IF statements (assuming that you'll only ever have one value per cell).

This way you can group together all the same values that have the same output with an IF(OR statement, like so:

IF(OR([1st Circuit Status]@row= "Contract in Process", [1st Circuit Status]@row= "Waiting for Quote", [1st Circuit Status]@row= "Waiting for Order Validation to complete", [1st Circuit Status]@row= "Cancelled", [1st Circuit Status]@row= "Requirement Review", [1st Circuit Status]@row= "Not Started", [1st Circuit Status]@row= "Out of Scope", [1st Circuit Status]@row= "Reassigned to another Carrier", [1st Circuit Status]@row= "Need to reassign to another Carrier", [1st Circuit Status]@row= "Addendum 37"), 0,

For a full formula, you could do something like this:

=SUM(

IF(OR([1st Circuit Status]@row= "Contract in Process", [1st Circuit Status]@row= "Waiting for Quote", [1st Circuit Status]@row= "Waiting for Order Validation to complete", [1st Circuit Status]@row= "Cancelled", [1st Circuit Status]@row= "Requirement Review", [1st Circuit Status]@row= "Not Started", [1st Circuit Status]@row= "Out of Scope", [1st Circuit Status]@row= "Reassigned to another Carrier", [1st Circuit Status]@row= "Need to reassign to another Carrier", [1st Circuit Status]@row= "Addendum 37"), 0, IF([1st Circuit Status]@row= "Started", .01, IF([1st Circuit Status]@row= "Waiting to assign to local carrier", .05, IF([1st Circuit Status]@row= "Circuit Order in process", .04, IF([1st Circuit Status]@row= "Assigned to Local Carrier", .10, IF([1st Circuit Status]@row= "Circuit in Design Phase", .6, IF([1st Circuit Status]@row= "Circuit Site Survey in Progress", .65, IF([1st Circuit Status]@row= "Circuit Site Survey Complete",.70, IF([1st Circuit Status]@row= "LCON Waiting Carrier to provide Design", .71, IF([1st Circuit Status]@row= "On Hold", .50, IF([1st Circuit Status]@row= "Waiting for LCON to Approve Site Survey", .73, IF([1st Circuit Status]@row= "Circuit in Construction Phase", .8, IF([1st Circuit Status]@row= "Circuit installed waiting carrier to deliver", .99, IF(OR([1st Circuit Status]@row= "Circuit Delivered", [1st Circuit Status]@row= "Use local Current Circuits"), 1),

IF(OR([2nd Circuit Status]@row= "Contract in Process", [2nd Circuit Status]@row= "Waiting for Quote", [2nd Circuit Status]@row= "Waiting for Order Validation to complete", [2nd Circuit Status]@row= "Cancelled", [2nd Circuit Status]@row= "Requirement Review", [2nd Circuit Status]@row= "Not Started", [2nd Circuit Status]@row= "Out of Scope", [2nd Circuit Status]@row= "Reassigned to another Carrier", [2nd Circuit Status]@row= "Need to reassign to another Carrier", [2nd Circuit Status]@row= "Addendum 37"), 0, IF([2nd Circuit Status]@row= "Started", .01, IF([2nd Circuit Status]@row= "Waiting to assign to local carrier", .05, IF([2nd Circuit Status]@row= "Circuit Order in process", .04, IF([2nd Circuit Status]@row= "Assigned to Local Carrier", .10, IF([2nd Circuit Status]@row= "Circuit in Design Phase", .6, IF([2nd Circuit Status]@row= "Circuit Site Survey in Progress", .65, IF([2nd Circuit Status]@row= "Circuit Site Survey Complete",.70, IF([2nd Circuit Status]@row= "LCON Waiting Carrier to provide Design", .71, IF([2nd Circuit Status]@row= "On Hold", .50, IF([2nd Circuit Status]@row= "Waiting for LCON to Approve Site Survey", .73, IF([2nd Circuit Status]@row= "Circuit in Construction Phase", .8, IF([2nd Circuit Status]@row= "Circuit installed waiting carrier to deliver", .99, IF(OR([2nd Circuit Status]@row= "Circuit Delivered", [2nd Circuit Status]@row= "Use local Current Circuits"), 1)

/2

As a final note, make sure that any time you have a quotation mark in a formula it's straight up and down like so: "

I notice that some of your quotes are curvy (” versus ") which would result in an error in Smartsheet.

Cheers,

Genevieve

Leibel S

@RobNY2

See below option:

=SUM(IF([1st Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([1st Circuit Status]@row = "Circuit Delivery", 1) + IF([1st Circuit Status]@row = "Circuit in Construction Phase", 0.8) + IF([2nd Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([2nd Circuit Status]@row = "Circuit Delivery", 1) + IF([2nd Circuit Status]@row = "Circuit in Construction Phase", 0.8)) / 2

RobNY2

Leibel

I am getting UNPARSEABLE.

The 1st formula below is what you gave me and it works:

=SUM(IF([1st Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([1st Circuit Status]@row = "Circuit Delivery", 1) + IF([1st Circuit Status]@row = "Circuit in Construction Phase", 0.8) + IF([2nd Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([2nd Circuit Status]@row = "Circuit Delivery", 1) + IF([2nd Circuit Status]@row = "Circuit in Construction Phase", 0.8)) / 2

The 2nd formula below is what you gave me I just changed what is between " " and removed some spaces. It didn't work but your formula above without the spaces also worked, so the problem it is not removing the spaces:

=SUM(IF([1st Circuit Status]@row="Assigned to Local Carrier",0.1)+IF([1st Circuit Status]@row="Circuit Delivery",1)+IF([1st Circuit Status]@row ="Circuit in Construction Phase",0.8)+IF([2nd Circuit Status]@row="Assigned to Local Carrier",0.1)+IF([2nd Circuit Status]@row ="Circuit Delivery",1)+IF([2nd Circuit Status]@row="Circuit in Construction Phase",0.8))/2

This 3rd formula below is what I really need to insert in the cell. I removed the spaces because it is a big formula. Basically I copied what it is for "1st Circuit Status" and at the end for the formula "1st Circuit Status" I added the same formula but I changed to "2nd Circuit Status" and it didn't work:

=SUM(IF([1st Circuit Status]@row=“Contract in Process”,0)+IF([1st Circuit Status]@row=“Waiting for Quote”,0)+IF([1st Circuit Status]@row=“Waiting for Order Validation to complete”,0)+IF([1st Circuit Status]@row=“Cancelled”,0)+IF([1st Circuit Status]@row=“Requirement Review”,0)+IF([1st Circuit Status]@row=“Not Started”,0)+IF([1st Circuit Status]@row=“Out of Scope”,0)+IF([1st Circuit Status]@row=“Started”,.01)+IF([1st Circuit Status]@row=“Waiting to assign to local carrier”,.05)+IF([1st Circuit Status]@row=“Circuit Order in process”,.04)+IF([1st Circuit Status]@row=“ Reassigned to another Carrier”,0)+IF([1st Circuit Status]@row=“Assigned to Local Carrier”,.10)+IF([1st Circuit Status]@row=“Circuit in Design Phase”,.6)+IF([1st Circuit Status]@row=“Circuit Site Survey in Progress”,.65)+IF([1st Circuit Status]@row=“Circuit Site Survey Complete”,.70)+IF([1st Circuit Status]@row=“LCON Waiting Carrier to provide Design”,.71)+IF([1st Circuit Status]@row=“On Hold”,.50)+IF([1st Circuit Status]@row=“Waiting for LCON to Approve Site Survey”,.73)+IF([1st Circuit Status]@row=“Circuit in Construction Phase”,.8)+IF([1st Circuit Status]@row=“Circuit installed waiting carrier to deliver”,.99)+IF([1st Circuit Status]@row=“Circuit Delivered”,1)+IF([1st Circuit Status]@row=“Use local Current Circuits”,1)+IF([1st Circuit Status]@row=“Need to reassign to another Carrier”,0)+IF([1st Circuit Status]@row=“Addendum 37”,0)+IF([2nd Circuit Status]@row=“Contract in Process”,0)+IF([2nd Circuit Status]@row=“Waiting for Quote”,0)+IF([2nd Circuit Status]@row=“Waiting for Order Validation to complete”,0)+IF([2nd Circuit Status]@row=“Cancelled”,0)+IF([2nd Circuit Status]@row=“Requirement Review”,0)+IF([2nd Circuit Status]@row=“Not Started”,0)+IF([2nd Circuit Status]@row=“Out of Scope”,0)+IF([2nd Circuit Status]@row=“Started”,.01)+IF([2nd Circuit Status]@row=“Waiting to assign to local carrier”,.05)+IF([2nd Circuit Status]@row=“Circuit Order in process”,.04)+IF([2nd Circuit Status]@row=“ Reassigned to another Carrier”,0)+IF([2nd Circuit Status]@row=“Assigned to Local Carrier”,.10)+IF([2nd Circuit Status]@row=“Circuit in Design Phase”,.6)+IF([2nd Circuit Status]@row=“Circuit Site Survey in Progress”,.65)+IF([2nd Circuit Status]@row=“Circuit Site Survey Complete”,.70)+IF([2nd Circuit Status]@row=“LCON Waiting Carrier to provide Design”,.71)+IF([2nd Circuit Status]@row=“On Hold”,.50)+IF([2nd Circuit Status]@row=“Waiting for LCON to Approve Site Survey”,.73)+IF([2nd Circuit Status]@row=“Circuit in Construction Phase”,.8)+IF([2nd Circuit Status]@row=“Circuit installed waiting carrier to deliver”,.99)+IF([2nd Circuit Status]@row=“Circuit Delivered”,1)+IF([2nd Circuit Status]@row=“Use local Current Circuits”,1)+IF([2nd Circuit Status]@row=“Need to reassign to another Carrier”,0)+IF([2nd Circuit Status]@row=“Addendum 37”,0))/2

Below are the choices that I have in the dropdown menu that I need to assign the %

Rob

😓



Genevieve P.

Hi @RobNY2

If I'm understanding you correctly, you have one specific value that you want to assign as a percentage per choice, and there are two columns where these values could appear. In each row you want to AVG the two values from those two cells for a final percentage.

If this is the case, I believe you're missing a comma from the first formula. This is the structure:

=SUM(first value, second value) / 2

However notice that when you swap from doing an IF statement for the 1st Circuit Status there's no comma. It should be:

=SUM(IF([1st Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([1st Circuit Status]@row = "Circuit Delivery", 1) + IF([1st Circuit Status]@row = "Circuit in Construction Phase", 0.8), IF([2nd Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([2nd Circuit Status]@row = "Circuit Delivery", 1) + IF([2nd Circuit Status]@row = "Circuit in Construction Phase", 0.8)) / 2

Since you have a much longer list, what I would suggest doing is two Nested IF statements (assuming that you'll only ever have one value per cell).

This way you can group together all the same values that have the same output with an IF(OR statement, like so:

IF(OR([1st Circuit Status]@row= "Contract in Process", [1st Circuit Status]@row= "Waiting for Quote", [1st Circuit Status]@row= "Waiting for Order Validation to complete", [1st Circuit Status]@row= "Cancelled", [1st Circuit Status]@row= "Requirement Review", [1st Circuit Status]@row= "Not Started", [1st Circuit Status]@row= "Out of Scope", [1st Circuit Status]@row= "Reassigned to another Carrier", [1st Circuit Status]@row= "Need to reassign to another Carrier", [1st Circuit Status]@row= "Addendum 37"), 0,

For a full formula, you could do something like this:

=SUM(

IF(OR([1st Circuit Status]@row= "Contract in Process", [1st Circuit Status]@row= "Waiting for Quote", [1st Circuit Status]@row= "Waiting for Order Validation to complete", [1st Circuit Status]@row= "Cancelled", [1st Circuit Status]@row= "Requirement Review", [1st Circuit Status]@row= "Not Started", [1st Circuit Status]@row= "Out of Scope", [1st Circuit Status]@row= "Reassigned to another Carrier", [1st Circuit Status]@row= "Need to reassign to another Carrier", [1st Circuit Status]@row= "Addendum 37"), 0, IF([1st Circuit Status]@row= "Started", .01, IF([1st Circuit Status]@row= "Waiting to assign to local carrier", .05, IF([1st Circuit Status]@row= "Circuit Order in process", .04, IF([1st Circuit Status]@row= "Assigned to Local Carrier", .10, IF([1st Circuit Status]@row= "Circuit in Design Phase", .6, IF([1st Circuit Status]@row= "Circuit Site Survey in Progress", .65, IF([1st Circuit Status]@row= "Circuit Site Survey Complete",.70, IF([1st Circuit Status]@row= "LCON Waiting Carrier to provide Design", .71, IF([1st Circuit Status]@row= "On Hold", .50, IF([1st Circuit Status]@row= "Waiting for LCON to Approve Site Survey", .73, IF([1st Circuit Status]@row= "Circuit in Construction Phase", .8, IF([1st Circuit Status]@row= "Circuit installed waiting carrier to deliver", .99, IF(OR([1st Circuit Status]@row= "Circuit Delivered", [1st Circuit Status]@row= "Use local Current Circuits"), 1),

IF(OR([2nd Circuit Status]@row= "Contract in Process", [2nd Circuit Status]@row= "Waiting for Quote", [2nd Circuit Status]@row= "Waiting for Order Validation to complete", [2nd Circuit Status]@row= "Cancelled", [2nd Circuit Status]@row= "Requirement Review", [2nd Circuit Status]@row= "Not Started", [2nd Circuit Status]@row= "Out of Scope", [2nd Circuit Status]@row= "Reassigned to another Carrier", [2nd Circuit Status]@row= "Need to reassign to another Carrier", [2nd Circuit Status]@row= "Addendum 37"), 0, IF([2nd Circuit Status]@row= "Started", .01, IF([2nd Circuit Status]@row= "Waiting to assign to local carrier", .05, IF([2nd Circuit Status]@row= "Circuit Order in process", .04, IF([2nd Circuit Status]@row= "Assigned to Local Carrier", .10, IF([2nd Circuit Status]@row= "Circuit in Design Phase", .6, IF([2nd Circuit Status]@row= "Circuit Site Survey in Progress", .65, IF([2nd Circuit Status]@row= "Circuit Site Survey Complete",.70, IF([2nd Circuit Status]@row= "LCON Waiting Carrier to provide Design", .71, IF([2nd Circuit Status]@row= "On Hold", .50, IF([2nd Circuit Status]@row= "Waiting for LCON to Approve Site Survey", .73, IF([2nd Circuit Status]@row= "Circuit in Construction Phase", .8, IF([2nd Circuit Status]@row= "Circuit installed waiting carrier to deliver", .99, IF(OR([2nd Circuit Status]@row= "Circuit Delivered", [2nd Circuit Status]@row= "Use local Current Circuits"), 1)

/2

As a final note, make sure that any time you have a quotation mark in a formula it's straight up and down like so: "

I notice that some of your quotes are curvy (” versus ") which would result in an error in Smartsheet.

Cheers,

Genevieve

RobNY2

Genevieve

Thank you very much! I appreciate your help!

Rob😀