How do I add multiple values in an array based on multiple hits?

[Guest]

Background:
I want to do this in Excel!
I have a table of weighted percentages for a grade book.
Currently they are weighted averages that look like this. They are the sum
total
of all the students' quizzes, tests, and Homework assignments based on their
weighted percentages. I'm using the sumproduct funtion in the AVG column to
calculate the average based on the percentage weights. It works very well
and is and this allows me to change the weights if I decide to do so.

Students Quizzes Tests HW AVG
25% 65% 10%

Bob 100 90 100 94
Jim 50 100 100 88
Fred 50 50 100 55

Question:
I want a way to gather all and average all the data into its respective
column from another array. For example, the following would be their scores:

Type HW Q T HW T
Bob 100% 100% 100% 100% 80%
Jim 100% 50% 100% 100% 100%
Fred 100% 50% 50% 100% 50%

All the homework (HW types) would roll up into an average in the HW column
in the first array, all the Quizes (Q types) would roll up into an average in
the Quizzes column in the first array, and all the Tests (T types) would do
the same in the tests column. Also - as you can see, the different types are
not necessarily next to each other. Furthermore, If, for example, Fred only
has one HW in, I want to be able to leave the other HWs blank if I choose to
excuse him from that assignment. The AVERAGE function will do that just
fine, but I put this down in case the solution doesn't include that function.

Any ideas anyone? This would be a life saver for me and I'd be ever so
grateful. I looked for a function in excel but couldn't find one.

Thanks - Steve

 

[Ragdyer]

Using your examples, with the averages on Sheet1,
Row 1 & 2 are labels,
Names in A3 to A5,

Sheet 2 - labels in Row1, from A1 to F1
*NOTE* - labels in B1 to F1 *must* match exactly to labels on Sheet 1, B1 to
D1.
Needless to say, the names in A2 to A4 of Sheet2 must also match *exactly*
to the names in Sheet1, from A3 to A5..

This formula, entered in B3 of Sheet1, will return the average for the name
in A3 for *all* occurrences in Sheet2 that match the column label in B1:

=SUMPRODUCT((Sheet2!$A$2:$A$4=$A3)*(Sheet2!$B$1:$F$1=B$1)*
Sheet2!$B$2:$F$4)/COUNTIF(Sheet2!$B$1:$F$1,B$1)

Copy this formula across to D3, and then down to Row5.

You could incorporate this formula with your formula for weighting the
averages.

 

[Guest]

Assuming your labels (HW, Q, T) are in B1:H1, J1 contains the label for what
you want to average (eg HW) and your first row of data is in B2:H2, you could
try:

=AVERAGE(IF($B$1:$H$1=J$1,$B2:$H2))

array entered w/Cntrl+Shift+Enter. Then copy it down the length of your
table. But, it would treat blank cells as 0 and include them in the average.
You could correct this by putting "E" (for excused) in the cells instead of
leaving them blank.

or you, really want to use blank cells and ignore them, you could try:
=SUMIF($B$1:$H$1,J$1,$B2:$H2)/SUMPRODUCT(--($B$1:$H$1=J$1),--($B2:$H2<>""))

entered normally and copied down.

 

[Guest]

I believe this will treat blank cells as 0 and include them in the average.

 

[Guest]

Hi Ragdyer!

A follow up question:
Your example works beautifully if the two tables are separated into 2
different sheets, but I want to have the two tables in the same sheet. In
fact, I don't even want to worry about matching the names of the students.
The raw data (which is actually formatted as percentages though not shown
here for space reasons and so it also will not to be confused with the
calculated percentages) will be lined up right next to each student's row
with their calculated averages. This will avoid having two sheets for each
subject (allowing me to keep each subject in it's own worksheet as a separate
tab). Example below (Column F is blank)

1 A B C D E F G H I
J K
2 Stu Quiz Test HW AVG HW Quiz Test HW Test
3 25% 65% 10%
4 Bob 100 90 100 94% 100 100 100 100 80
5 Jim 50 100 100 88% 100 50 100 100 100
6 Fred 50 50 100 55% 100 50 50 100 50

Question: How would your sumproduct formula change. I was trying to figure
it out, but it's a bit tricky for me to understand.

Thanks - Steve

 

[Guest]

Hi JMB,
I tried the Average(If)... below, but had to modify it slightly because the
data is not really in two different sheets - the data is lined up next to
each other in the same sheet. In any event, when I tried it I got a #VALUE!
error in the cell with that formula. Maybe I modified it incorrectly. I
replied to the other person who put me on to the sumproduct formula (which
worked, by the way - though I haven't tested it with blank cells or E in the
cell) and laid my data out differently side by side showing the letters and
numbers of the cells. It might be easier to advise me if you see the setup I
sent in my reply to Ragdyer (RD). I'd be interested in your solution as
well. Actually, I want to include two blank cells on the outside of my
labels which will be colored in as markers so that if I insert more columns,
it would be done inbetween the data. I don't know if that makes sense to
you. In any event, I tried the =Average(If ... statement with and without
the blank cells on either end and it still got the #VALUE! error. Any advice?

Thanks - Steve

 

[Guest]

One more bit of information if forgot to include in my last reply ...
I also want to leave blank cells in case a student doesn't take a test and I
decide
to do an average without that particular score. It seems the sumproduct
function is unforgiving unless there is data in the range of cells with the
raw scores. I can't even put in an "E" for excused as it expects a number.
Another person replied saying I could do something like this

=AVERAGE(IF($B$1:$H$1=J$1,$B2:$H2))

but that left me with a #VALUE! in the cell with that formula (see JMB's
reply). Also, I'd like to leave a skinny blank column on either end of my
data (which I'll color in) so in case I want to insert more columns of scores
inbetween, I'll know to do it inside the colored columns (a trick that has
been fail-proof). So I was also wondering if the formula you propose could
include the blank cells of those skinny columns. I hope I'm not asking for
the moon! :0) Steve

 

[Guest]

When finished typing in the formula, you must hold down the Control and Shift
keys while pressing Enter. You can't just hit enter to confirm the formula.

Also, anytime you edit the formula you have to use Cntrl+Shift+Enter to
confirm it.

And now that I think about, instead of using my last suggestion
(Sumif/Sumproduct) if you want to leave the cells blank, you could try (using
my earlier assumptions):

=AVERAGE(IF(($B$1:$H$1=J$1)*($B2:$H2<>""),$B2:$H2))

also entered w/Cntrl+Shift+Enter

 

[Ragdyer]

With names in A3 to A5, try this in B3:

=SUMPRODUCT(--($G$1:$K$1=B$1),$G3:$K3)/
SUMPRODUCT(--($G$1:$K$1=B$1),--(ISNUMBER($G3:$K3)))

Copy across to D3, then down to Row 5.

Now, this formula will accept blank cells and alpha cells, and not have them
effect the average.
However, if a category has *no* numeric entry whatsoever, you'll get a
#DIV/0! error.

 

[Guest]

It WORKED! Like a CHARM! Even with blank cells!
I've never heard of using Cntrl+Shift+Enter!! What does confirming do? It
obviously makes a huge difference!!!! One thing I noticed is when you copy
the formula to other cells is you don't have to confirm each (in fact, I
don't even think you can). THANKS SO MUCH!! Steve

 

[Guest]

Hi Ragdyer! You're striking GOLD for me today!! Thanks so much. It worked
like a charm. I'm REALLY curious what the two dashes do and what the
ISNUMBER function is. I can look up the ISNUMBER function, but I don't know
about the dashes. I feel like I'm completely out of my league here as I
don't understand the formulae, but they certain do work -- EVEN WITH THE
BLANK CELLS I wanted!!!!
If you have time to reply about my question with the dashes or isnumber,
great, but I don't want to take too much of your time. You've been extremely
helpful!

Steve

 

[Guest]

more information regarding array formulae:

http://cpearson.com/excel/ArrayFormulas.aspx

 

[Ragdyer]

The double unary (dashes), do the same thing as the asterisk in my first
formula;
convert True & False returns from within the Sumproduct function to numeric
values (True = 1, False = 0), so that they can be used in calculations.

The difference is that the asterisk form will not accept alpha values
*anywhere* within the calculating ranges.
Since you want to be able to insert "E" in the same range as the percents, I
had to revise from one form to the other.
I started out using the asterisk form because, besides the fact I simply
prefer it, the original formula was constructed around a scenario of ranges
of mixed sizes, which is not handled by the unary form.
Your single page design eliminated the need to calculate ranges of different
sizes.

Here are a couple of links to expand *much* further on the unary and
Sumproduct:

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

http://www.xldynamic.com/source/xld.SUMPRODUCT.html