1

Scenario: We quality score work in our business to ensure this is updated correctly and we give each 'engineer' a mark out of 5 each week. Currently we are able to work out the percentage each week of a given score i.e. 5 out of 5 gives you 100%.

What we want to work out is how to count up this by month but to exclude the week where scoring isn't applicable.

For example: Sharepoint 2010 List - Quality Scoring

Single Text Cells: WK1: 100% (SP Value 5) WK2: N/A (SP Value 0) WK3: 100% (SP Value 5) WK4: N/A (SP Value 0)

When i do a calculated column to work out the percentage we are using formula =(WK1+WK2+WK3+WK4)/20 this gives us the result : 50%

What I'm struggling to understand is how can I not count up the N/A or 0 cell each week? In theory that would mean where the above is 100% for two of the four weeks you would achieve 100%.

Can anyone help me with the formula? I'm sure this is something fairly simple but I'm missing it.....

Improve this question

1 Answer 1

Reset to default
0

A bit messy but this might work:

=(WK1+WK2+WK3+WK4)/(5*SUM(if(WK1>0,1,0),if(WK2>0,1,0),if(WK3>0,1,0),if(WK4>0,1,0)))

However, if a zero score could also mean a poor performing week then you need another way to define whether the week should be included (1 or 0).

EDIT - added brackets around the divider

Improve this answer
4
  • oooo I'm almost there now! that works (regardless of how messy the formula is) but the values still aren't correct, to explain further: Using your formula on the same cells I posted in my query the value is now returned as : 800% (using 0 instead of N/A as this returns a #VALUE error)
    – Blake Reader
    Jun 4, 2013 at 11:19
  • I've amended your formula to be : =(WK1+WK2+WK3+WK4)/20*SUM(if(WK1>0,1,0),if(WK2>0,1,0),if(WK3>0,1,0),if(WK4>0,1,0)) (I adjusted the /5 to /20) which returns 200% for two weeks of 100% out of a possible four) I'm so close now I can feel it :)
    – Blake Reader
    Jun 4, 2013 at 11:24
  • EDIT: If i have 100% for two of the four weeks (with a blank cell for the two non scored weeks) the value is returned as 200%. If i remove the 0's the value is returned as 100%.... In any case the total value for each month cannot exceed 100%.....I thought we were close...
    – Blake Reader
    Jun 4, 2013 at 11:31
  • Hmm - I have made a tweak to the formula above to add brackets around what we are dividing by - see if that helps?
    – Dave Paylor
    Jun 4, 2013 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.