Summing up mixed data?

[Ricktaxx]

I have been looking at a way to solve a minor issue while I recreate a
score keeping wookbook.

We are tracking review scores that have % score and a Pass Fail. There
is a grid by person by date and scores saved as combined values. So we
end up with 85F, 76P, 100, 80P in the month. At the end of the month we
need to average up the scores and the pass fails and they have been
doing it by hand.

Is there anyway to get Excel to calculate the scores? I can find and
count the Pass Fails, but have not managed a way to get the percents
added up...

Thoughts, and thanks in adavnce.

 

[Biff]

Hi!

What category does the 100 fall under?

The best thing you could do is to separate the number value from the letter!

To count F or P: (Just substitute the F and P in all of these formulas)

=COUNTIF(A1:A4,"*F")

To get a sum of F or P: Entered as an array using the key combo of
CTRL,SHIFT,ENTER:

=SUM(IF(RIGHT(A1:A4)="P",--SUBSTITUTE(A1:A4,"P","")))

For the average: Entered as an array using the key combo of
CTRL,SHIFT,ENTER:

=AVERAGE(IF(RIGHT(A1:A4)="P",--SUBSTITUTE(A1:A4,"P","")))

Biff

 

[Ricktaxx]

Yes, I am working on a new sheet that has the Pass and Fail in their own
cells. It is a long task though.

This will help get me by for the short term, thanks again.