sum, ave with limits, help pls.

[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.

 

[Domenic]

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(IF($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!

 

[ShaneDevenshire]

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(B24)/SUM((F$1:H$1)*(B24<>""))

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(B24)/SUMPRODUCT((F$1:H$1)*(B24<>""))

If this helps, please click the Yes button.

 

[Chris]

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]

You're very welcome! Thanks for the feedback!

 

[Chris]

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))

 

[Domenic]

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)),MIN(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,COLUMN($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(IF(($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=I2,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!