sum, ave with limits, help pls.
 

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.

"Pete_UK" wrote:

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" wrote in message
...
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.

sum, ave with limits, help pls.
 

First, separate the column containing the part and maximum points into
two columns. So let's assume that A1:G5 contains the following data...

john mark john john eric
open 10 10 10 10 5 10
body 10 10 10 5 10 n/a
close 5 n/a 5 0 5 0
total 25 20 25 15 20 10

Let I2:I4 contain John, Mark, and Eric. Then try...

J2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF($C$1:$G$1=I2,IF(COLUMN($C$1:$G$1)<=SMALL(I F($C$1:$G$1=I2,COLUMN($
C$1:$G$1)),MIN(2,COUNTIF($C$1:$G$1,I2))),$C$5:$G$5 )))

K2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(SUMIF($A$2:$A$4,IF(ISNUMBER($C$2:$G$4),IF($C$ 1:$G$1=I2,IF(COLUMN($C$
1:$G$1)<=SMALL(IF($C$1:$G$1=I2,COLUMN($C$1:$G$1)), MIN(2,COUNTIF($C$1:$G$1
,I2))),$A$2:$A$4))),$B$2:$B$4))

Hope this helps!

In article ,
Chris wrote:

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.

"Pete_UK" wrote:

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" wrote in message
...
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.

sum, ave with limits, help pls.
 

Hi,

If you set your data up as follows starting in A1 and allowing 3 rows per
employee even if they don't have 3 evals.

O B C
John 10 10 5
10 10 5
10 10 5
Mark 7 8 1
4 1 2

Eric 1 0

Additionally you enter 10, 10, 5 in F1:H1 respectively (the max possible)

Then the average score is found by entering the following formula in D2 and
making a copy on the corresponding rows for Mark and Eric:

=SUM(B2:D4)/SUM((F$1:H$1)*(B2:D4<""))

This is an array entered formula so you press Shift+Ctrl+Enter instead of
Enter.

If you don't want to enter it as an array:

=SUMPRODUCT(B2:D4)/SUMPRODUCT((F$1:H$1)*(B2:D4<""))

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Chris" wrote:

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.

"Pete_UK" wrote:

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" wrote in message
...
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.

sum, ave with limits, help pls.
 

This was PERFECT!!!, tnx so much, I combined the 2 fomulas so that I can get
the average without using another cell.. tnx again :)

"Domenic" wrote:

First, separate the column containing the part and maximum points into
two columns. So let's assume that A1:G5 contains the following data...

john mark john john eric
open 10 10 10 10 5 10
body 10 10 10 5 10 n/a
close 5 n/a 5 0 5 0
total 25 20 25 15 20 10

Let I2:I4 contain John, Mark, and Eric. Then try...

J2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF($C$1:$G$1=I2,IF(COLUMN($C$1:$G$1)<=SMALL(I F($C$1:$G$1=I2,COLUMN($
C$1:$G$1)),MIN(2,COUNTIF($C$1:$G$1,I2))),$C$5:$G$5 )))

K2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(SUMIF($A$2:$A$4,IF(ISNUMBER($C$2:$G$4),IF($C$ 1:$G$1=I2,IF(COLUMN($C$
1:$G$1)<=SMALL(IF($C$1:$G$1=I2,COLUMN($C$1:$G$1)), MIN(2,COUNTIF($C$1:$G$1
,I2))),$A$2:$A$4))),$B$2:$B$4))

Hope this helps!

In article ,
Chris wrote:

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.

"Pete_UK" wrote:

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" wrote in message
...
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.

sum, ave with limits, help pls.
 

You're very welcome! Thanks for the feedback!

In article ,
Chris wrote:

This was PERFECT!!!, tnx so much, I combined the 2 fomulas so that I can get
the average without using another cell.. tnx again :)

sum, ave with limits, help pls.
 

Hi,

I have one more question, Like I said, I combined the formulas so that it
would average the data but if there is no data, it shows as #DIV/0!. I need
this to show as blank or empty

this is BTW the formula that I used. I tried adding another If condition
with "" as an option but it wont work, pls help. Tnx again.

=SUM(IF(DIO!$D$3:$FG$3=A10,IF(COLUMN(DIO!$D$3:$FG$ 3)<=SMALL(IF(DIO!$D$3:$FG$3=A10,COLUMN(DIO!$D$3:$F G$3)),MIN(8,COUNTIF(DIO!$D$3:$FG$3,A10))),DIO!$D$8 :$FG$8)))/SUM(SUMIF(DIO!$B$4:$B$7,IF(ISNUMBER(DIO!$D$4:$FG$7 ),IF(DIO!$D$3:$FG$3=A10,IF(COLUMN(DIO!$D$3:$FG$3)< =SMALL(IF(DIO!$D$3:$FG$3=A10,COLUMN(DIO!$D$3:$FG$3 )),MIN(8,COUNTIF(DIO!$D$3:$FG$3,A10))),DIO!$B$4:$B $7))),DIO!$C$4:$C$7))



"Domenic" wrote:

You're very welcome! Thanks for the feedback!

In article ,
Chris wrote:

This was PERFECT!!!, tnx so much, I combined the 2 fomulas so that I can get
the average without using another cell.. tnx again :)

sum, ave with limits, help pls.
 

Try...

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",SUM(IF(($C$1 :$G$1=I2)*(COLUMN($C$1:
$G$1)<=SMALL(IF($C$1:$G$1=I2,COLUMN($C$1:$G$1)),MI N(2,COUNTIF($C$1:$G$1,I
2)))),$C$5:$G$5))/SUM(SUMIF($A$2:$A$4,IF(ISNUMBER($C$2:$G$4)*($C$1:$ G$1=I
2)*(COLUMN($C$1:$G$1)<=SMALL(IF($C$1:$G$1=I2,COLUM N($C$1:$G$1)),MIN(2,COU
NTIF($C$1:$G$1,I2)))),$A$2:$A$4),$B$2:$B$4))&""))

....confirmed with CONTROL+SHIFT+ENTER. Note that while the formula
returns a number when data is present the number is formatted as text.
Here's an alternative which returns a numerical value...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,SUM(I F(($C$1:$G$1=I2)*(COLUM
N($C$1:$G$1)<=SMALL(IF($C$1:$G$1=I2,COLUMN($C$1:$G $1)),MIN(2,COUNTIF($C$1
:$G$1,I2)))),$C$5:$G$5))/SUM(SUMIF($A$2:$A$4,IF(ISNUMBER($C$2:$G$4)*($C$1
:$G$1=I2)*(COLUMN($C$1:$G$1)<=SMALL(IF($C$1:$G$1=I 2,COLUMN($C$1:$G$1)),MI
N(2,COUNTIF($C$1:$G$1,I2)))),$A$2:$A$4),$B$2:$B$4) )))

....confirmed with CONTROL+SHIFT+ENTER. Note, however, the formula
returns 0 even when data is not present.

Hope this helps!

In article ,
Chris wrote:

Hi,

I have one more question, Like I said, I combined the formulas so that it
would average the data but if there is no data, it shows as #DIV/0!. I need
this to show as blank or empty

this is BTW the formula that I used. I tried adding another If condition
with "" as an option but it wont work, pls help. Tnx again.

=SUM(IF(DIO!$D$3:$FG$3=A10,IF(COLUMN(DIO!$D$3:$FG$ 3)<=SMALL(IF(DIO!$D$3:$FG$3=
A10,COLUMN(DIO!$D$3:$FG$3)),MIN(8,COUNTIF(DIO!$D$3 :$FG$3,A10))),DIO!$D$8:$FG$8
)))/SUM(SUMIF(DIO!$B$4:$B$7,IF(ISNUMBER(DIO!$D$4:$FG$7 ),IF(DIO!$D$3:$FG$3=A10,
IF(COLUMN(DIO!$D$3:$FG$3)<=SMALL(IF(DIO!$D$3:$FG$3 =A10,COLUMN(DIO!$D$3:$FG$3))
,MIN(8,COUNTIF(DIO!$D$3:$FG$3,A10))),DIO!$B$4:$B$7 ))),DIO!$C$4:$C$7))