Conditional SUMmations and averages

[Tom]

Excel 2003 SP3
WinXP Pro SP3

I have a set of tests that I need to average for some students.
In row 7 for columns D through 12 I indicate a Y or an N if I'd like to
include that weeks test/quiz into the final test score. Sometimes I provide
pre-tests and pre-quizes just to determine the level of understanding of the
material. I need to keep these results but I don't want to include them in
the final outcome. Hence, the student's results may be like this:
Column
A B C D E F
Week # 1 2 3 4 5
Student Name
Include? N N Y N Y
Results for 72 64 84 53 89 Average should be (84+89)/2 (2
test w/ Y)
Doe, John
Results for
Smith, Mary 75 79 85 67 79 Average should be (85+79)/2 (2
tests w/ Y)
How can I conditionally SUM and average the above data by including ONLY
those results with a Y in the "Include?" cell of the same column that has the
appropriate results? (I don't want a deep IF-conditional test if at all
possible)
TIA!

 

[Mike H]

Tom,

Try this array formula

=AVERAGE(IF(B2:H2="y",B3:H3))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

 

[Tom]

Mike
GREAT answer! It worked! There are some students who have not taken a test
yet and hence I get a "#div/0" response for these students. How do I correct
this, too? WHERE did you find/know how to do this!?? Never even seen it!
TIA!!

 

[Ragdyer]

Using Mike's array formula:
=AVERAGE(IF(B$2:F$2="y",B4:F4))
And copying down *after* the CSE, I *can't* duplicate your results.

I just get a simple 0 return, no error message!

What *exact* formula are you using?
What, if anything, is in the cells (B4:F4, B5:F5, etc...) before the test
scores are entered?

 

[Tom]

Ok, so I found what I did wrong. Stupid error! I had copied the formula
down to the associated cells in their respective rows but I failed to do the
B$2:F$2. My error, my bad! I had B2:F2 etc. ;-(

Where can I found additional info on the CSE features? I guess I didn't
even know they existed!

Is there an easy way to determine which day of the week the students do
their best? That is, can I average the Monday, Tuesday, Wednesday, Thursday
and Friday results and see which day is best? I know how to determine if a
day is on Monday through Friday but keep in mind that not all days have
quizes and some weeks are only four (4) days - like Memorial Day week or
Easter/Good Friday Week or Christmas/New Years Day week or Thanksgiving Day
week (no Thursday or Friday classes and sometimes not even a Wednesday).
Hence, I'd need to check for the day of the week before I do anything.

So, how can I determine from a set of quizes (M-F) what the average was for
Monday vs Friday? I'd like to also be able to sum the total # right for,
say, Friday.
Thanks!

 

[Ragdyer]

If you're looking to tabulate test results by the day of the week, where do
(will) you have these date values entered in your datalist?

With week number in Row1 and "include/not include" in Row2, you need to
redesign your datalist.

If you replaced the week number with the actual date of the test, would
there be enough entries to form a pattern of days of the week to be
considered relevant?

 

[Tom]

All data is kept in the same SET of columns per student somewhat similar to
below. I've provided some miscellaneous data. Note that there's no Monday,
5/25 and no Friday 6/5. Some worksheets have tests and others have quizes.
Hence, we could see the following. I'm not too sure how to go about
averaging all Mondays, all Tuesdays, all Wednesdays, all Thursdays and all
Fridays each as a group for a day of the week. Friday is day 5 relative to
Sunday being day 0, if I remember how I did this originally (or Friday is day
6 relative to Sunday being day 1). I can't easily use 'include/don't
include' unless I create a separate column for each day of the week and
conditionally check for the respective day. I'd like to just have a
'look-back' based on say, Friday or Monday etc. Can I look-back a rolling 30
days or ALL days of a week even past 30 calendar days.

Doe, John 5/22/2009 Fri 66.78%
5/26/2009 Tue 75.66%
5/27/2009 Wed 78.99%
5/28/2009 Thu 85.45%
5/29/2009 Fri 79.89%
6/1/2009 Mon 65.14%
6/2/2009 Tue 75.89%
6/3/2009 Wed 88.91%
6/4/2009 Thu 92.89%
6/8/2009 Mon 84.50%
6/9/2009 Tue 79.68%
6/10/2009 Wed 85.62%
6/11/2009 Thu 92.11%
6/12/2009 Fri 93.50%
HTH....
Thanks for your help!

 

[RagDyeR]

The test dates and scores that you listed,
they're going *across* columns, along a row ... aren't they?

Why can't you just use another row for the "include/don't include"?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

All data is kept in the same SET of columns per student somewhat similar to
below. I've provided some miscellaneous data. Note that there's no Monday,
5/25 and no Friday 6/5. Some worksheets have tests and others have quizes.
Hence, we could see the following. I'm not too sure how to go about
averaging all Mondays, all Tuesdays, all Wednesdays, all Thursdays and all
Fridays each as a group for a day of the week. Friday is day 5 relative to
Sunday being day 0, if I remember how I did this originally (or Friday is
day
6 relative to Sunday being day 1). I can't easily use 'include/don't
include' unless I create a separate column for each day of the week and
conditionally check for the respective day. I'd like to just have a
'look-back' based on say, Friday or Monday etc. Can I look-back a rolling
30
days or ALL days of a week even past 30 calendar days.

Doe, John 5/22/2009 Fri 66.78%
5/26/2009 Tue 75.66%
5/27/2009 Wed 78.99%
5/28/2009 Thu 85.45%
5/29/2009 Fri 79.89%
6/1/2009 Mon 65.14%
6/2/2009 Tue 75.89%
6/3/2009 Wed 88.91%
6/4/2009 Thu 92.89%
6/8/2009 Mon 84.50%
6/9/2009 Tue 79.68%
6/10/2009 Wed 85.62%
6/11/2009 Thu 92.11%
6/12/2009 Fri 93.50%
HTH....
Thanks for your help!

 

[Tom]

Ok, busy at work today...
Isn't the test for day-of-the-week (DOW) nearly the same as "include/don't
include"? Hence, if I test for DOW=6 (Friday) then isn't that the same logic
but WITHIN the cell as opposed to manually entering a "Y/N" to
"included/don't include"?

On the row/column issue: For a SINGLE student we're in a column. For
multiple students we're in a row... as I have it. Does this make it more
understandable?

I'm trying to stay away from manually setting the "include/don't include" so
as to do the calculations based upon the actual data. That way, I don't
mess-up with an invalid "Y/N".
THANKS again!!!

 

[RagDyer]

Say dates in Column B, days in Column C, and scores in Column D,
with data in B3 to D16.

If days is a text value that you simply keyed in (*not* an XL legal date),
enter the day to average in say C1, and use this:

=SUMIF(C3:C16,C1,D316)/COUNTIF(C3:C16,C1)

On the other hand, if the days column is a *true* XL date that you formatted
to display the name of the day, use this:

=SUMPRODUCT((WEEKDAY(C3:C16)=WEEKDAY(C1))*D316)/SUMPRODUCT(--(WEEKDAY(C3:C16)=WEEKDAY(C1)))

*BUT* make sure that C1 now holds a true XL date that equates to the day you
wish to average.

If you wish, you can include a start and end date in this formula if say,
you just wanted to average a certain day for the past month only.

Enter the start date in B1, and the end date in B2, maybe 5/1 and 5/31.

=SUMPRODUCT((B3:B16>=B1)*(B3:B16<=B2)*(WEEKDAY(C3:C16)=WEEKDAY(C1))*D316)/SUMPRODUCT((B3:B16>=B1)*(B3:B16<=B2)*(WEEKDAY(C3:C16)=WEEKDAY(C1)))