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!

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" wrote:

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


"Mike H" wrote:

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" wrote:

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!

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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tom" wrote in message
...
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!!


"Mike H" wrote:

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" wrote:

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!

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" wrote:

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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tom" wrote in message
...
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!!


"Mike H" wrote:

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" wrote:

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!

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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tom" wrote in message
...
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" wrote:

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?
--
Regards,

RD


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

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


"Mike H" wrote:

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" wrote:

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!

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" wrote:

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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tom" wrote in message
...
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" wrote:

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?
--
Regards,

RD


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

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


"Mike H" wrote:

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" wrote:

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!

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

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

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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tom" wrote in message
...
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" wrote:

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?
--
Regards,

RD


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

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


"Mike H" wrote:

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" wrote:

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!

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" wrote:

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

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

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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tom" wrote in message
...
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" wrote:

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?
--
Regards,

RD


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

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


"Mike H" wrote:

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" wrote:

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!

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,D3:D16)/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))*D3:D16)/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))*D3:D16)/SUMPRODUCT((B3:B16=B1)*(B3:B16<=B2)*(WEEKDAY(C3:C 16)=WEEKDAY(C1)))

--
HTH,

RD

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

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

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

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

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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"Tom" wrote in message
...
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" wrote:

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?
--
Regards,

RD


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

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


"Mike H" wrote:

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" wrote:

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!