# Sharepoint Percentage Working Out

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

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) 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 :) 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... 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? Jun 4, 2013 at 20:33