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.....

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

  • 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 '13 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 '13 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 '13 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 '13 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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