Calculating goal attainment

[nickxylas]

The state government department where I work uses the following method
to calculate the percentage of Equal Employment Opportunity goals
achieved by other state agencies. I was wondering whether it is
possible to do this automatically in Excel.

The section of the spreadsheet containing goals met for different
classes of person is set so that any cell containing a value >=100%
automatically reads "YES". Overall goal attainment is calculated by:

1. Adding up the percentage figures which are less than 100%.
2. Adding up the number of YESes and multiplying by 100.
3. Adding the totals from step 1 and step 2 together.
4. Dividing the result by the total number of values (percentages and
yeses).

I figure Excel should be able to automate this process, but it would
take formula-writing skills more advanced than mine. The problem I have
is getting it to understand that YES=100. I should probably point out
that the percentage cells are calcualted fields, and that it is
possible for a result to calculate as greater than 100% if an agency
exceeds its target, but when calcualting the overall attainment a value
of, say 103% would be counted as 100%. Any help would be much
appreciated.

 

[Ian]

One way would be to use a helper column.
Assuming your current data is in column A
In B1 put =IF(A1="YES",100,A1) and copy it down the column
To get your result, in a cell type =SUM(B1:B100)/COUNTIF(B1:B100,">0")
This assumes your total (in 4 below) is those cells either greater than zero
or YES.