Sum with limits

[Chris]

Sheet 1

john
mark
eric

Sheet 2
A B C D E
1 john mark john john eric
2 open(10) 10 10 10 5 10
3 body(10) 10 10 5 10 n/a
4 close(5) n/a 5 0 5 0
5 total (25) 20 25 15 20 10

The numbers in ( ) are max pts that can be awarded per category.

With the sample table, what I need to do is total all the point for all data
in sheet one(john, mark and eric) and divide that with the total possible
points per evaluation(john=70-20+25+25, mark=25, eric=15) BUT I should only
total and divide the first 2 sample data.

So in this case for mark its 25/25
for Eric 10/15
for john 35/45 (first 2 - 20 + 15 / pts possible 20 + 25)

If you can give me just one formula without a helper cell, that would be
perfect.. please include explanations so that I can futher analyze the
formula.

Again, Tnx for all your help.

 

[Pete_UK]

This makes no sense to me - perhaps you can have another go at describing
what you want to do. Explain in particular, why have you got 3 columns for
John in Sheet2, and how Eric totals 15.

Pete

 

[Chris]

My apologies for the cofusion.

John, mark and eric are employee's, they each are being evaluated with the
quality of their writing, each quality evaluation is composed of 3 parts,
opening, body and close. Maximum number of pts that can be awarded for
opening and body is 10 pts and for close its 5.

Normally the max number of pts that can be earned per evaluation is 25pts(10
for opening and body and 5 for close) but there are cases that 1 of the 3
parts are not applicable, in cases like this, the maximum pts possible is
lessened, depending on which part is not aaplicable (ex. If close is not
applicable then amx pts will be 20).

There are 3 evaluations for john because he was evaluated 3 times and one
each for erik and mark but in this scenario, I only need to average the first
2 evaluations for all of the. I will bve averaging both erik and marks bec
they only have 1 survey each but for john, I will only total and average the
firsst 2 data.

Averaging should be done by getting the TOTAL pts for the 2 evaluations and
dividing that with the max pts possible for the same 2 monits.

I hope this clears it up.. tnx.