Excel 2010 Percentage Working Out

[blakeyuk]

Hi All,

I have an issue where by I'm trying to work out the average of a month but I want to ignore any values that are 0 or N/A

(see attached file)

In columns B1:B4 is our 'score', the value here will always be between 0.5-5. Anything as 0 or N/A would need to be ignored.

Columns C1:C4 is the percentage score for each week out of 5
Column C6 is the percentage score for the total four weeks

What I'm aiming for in column C6 is to return the percentage mark for the four weeks excluding weeks where no score was recorded (i.e. a 0 or N/A field).

If we percentage it over the four weeks i.e. /20 then the score comes back as 50% (as there is no value for two of the four weeks), how can I get this so the value is 100%? i.e. the engineer hit 100% on the weeks he was scored on...

Any help would be gratefully appreciated

[etaf]

this may not work for you
BUT as you have an entry for every week (and a week represents 5 days )
you could count the entries that do not have a zero and then mulitply by 5
=SUM(B2:B5)/(COUNTIF(B2:B5,"<>0")*5)

[blakeyuk]

@etaf, that returns value 1

[:) Sixthsense :)]

May be this...

In C6 Cell

Please Login or Register  to view this content.

[blakeyuk]

Both solutions actually work now I've put them in the right cell.....now just to convert that to a Sharepoint formula )

[etaf]

mine does not include N/A - so Sixthsense solution meets the requirements