Help with Weighted Average w/ a Nested If Function

Drtyblk7

I have a Text/Number Column with the following nested 'IF' function generated Percents based on a status column:

IF(Milestone3 = "Proposed", "0%", IF(Milestone3 = "In Progress", "10%", IF(Milestone3 = "Assigned to QA", "20%", IF(Milestone3 = "QA in Progress", "30%", IF(Milestone3 = "Ready for Staging", "40%", IF(Milestone3 = "PM Initiated UAT Testing and Sign Off", "50%", IF(Milestone3 = "Documentation Complete", "60%", IF(Milestone3 = "Training Complete", "70%", IF(Milestone3 = "In Production", "80%", IF(Milestone3 = "PM Internal Testing on Production", "90%", IF(Milestone3 = "Client Acceptance Testing and Final Sign Off", "100%")))))))))))

I then take the results of that column and tried the weighted average and the child average functions to generate the percent complete for that task group:

Either: (

=AVGW([% Complete]10:[% Complete]15, [Points of Effort]10:[Points of Effort]15)

or

=AVG(CHILDREN([% Complete]3:[% Complete]8))

 

Both give me the result of #Divide by Zero.

 

However, there are no '0's or Blanks in my fields. I think that the values the IF function is entering are not being recognized as numerical values.

Have you experienced this or know a way around this issue?

Best,

DRTYBLK7

 

 

Paul Newcome

You are correct. It is not reading any values. To calculate percentages Smartsheet uses decimals, so 1 = 100%, .5 = 50%, etc. For example, you would want to replace "70%" with .7 leaving out the quotes as well. You can then format the column to percentages to have them calculate as numbers and display how you want.

 

Another option would be to build a table and use an INDEX/MATCH function. It will be a lot shorter and require much less work if any updates are needed. (Column headers are in BOLD) (The "T" in the column names represent they are being used as a table. No spaces are used)

 

StatusT                      PercentageT

Not Started                 0

Just Started                .25

Halfway                      .5

Almost Finished          .75

Done                           1

 

Your formula would be along the lines of

 

=VALUE(INDEX(PercentageT:PercentageT, MATCH([Status Column Name]@row, StatusT:StatusT)))

 

What this will do is look at whatever is in the column name and pull the corresponding number from the table. Once you format your Percentage Column to %, you should be all set.

 

Using the INDEX/MATCH allows you to make adjustments as needed without having to go in and dig through an excessively long nested IF statement. 

Drtyblk7

Thanks! @PaulNewcome!

That was exceptionally helpful!

Paul Newcome

Happy to help!