Excluding a cell from an average

[wmd]

I'm working on creating a grade book, but I've encountered a problem
with excluding an assignment for individual students when averaging the
grades.

I have several different worksheets for different subjects. On each
sheet the students are listed on the left (A2:A26), the
assignments/quizzes are at the top (B1:Z1), and the value of each
assignment is at the bottom (B27:Z27).

I also have a "Results" page with the average mark (percent) for each
student, for each subject. The formula I've used on the "Results" page
is as follows (as an example):

=SUM(Sheet1!B2:Z2)/SUM(Sheet1!$B$27:$Z$27)

This all works fine. However, what I'm looking to do is have Excel
ignore a cell (and the corresponding assignment value) when computing
the percentage, IF an "X" is entered instead of a grade.

If I leave the cell blank, the percentage will calculate as if there was
a 0 grade in that cell; instead I want the program to essentially "skip"
that cell (and the assignment value) for that particular student when an
"X" is entered, and calculate the percentage properly.

Any suggestions?

Thanks in advance.

wmd

 

[Bernie Deitrick]

wmd,

=SUM(Sheet1!B2:Z2)/SUMIF(Sheet1!B2:Z2,"<>X",Sheet1!$B$27:$Z$27)

Bernie

 

[wmd]

wmd,

=SUM(Sheet1!B2:Z2)/SUMIF(Sheet1!B2:Z2,"<>X",Sheet1!$B$27:$Z$27)

Bernie

Thanks a bunch! That did exactly what I wanted.

What does the <> mean in Excel? I think I tried a SUMIF function while
trying to figure it out, but I only entered "X" instead of "<>X".

wmd

 

[Bob Phillips]

IS this being calculated in the correct way, should you average the
percentage mark of each assignment, rather than summing the overall marks
and dividing by total assignment value.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[wmd]

IS this being calculated in the correct way, should you average the
percentage mark of each assignment, rather than summing the overall marks
and dividing by total assignment value.

At first I wasn't quite sure what you meant, but I see now what you're
talking about.

Consider two grades: 5/10 and 20/20.

Using the total grades / total marks method and then converting to
percent, you arrive at 83% (i.e. 25/30).

But converting to percent first, as you suggest, results in 75% (i.e.
(50% + 100%) / 2).

So, going back to my original question that I received an answer for,
the formula that I'm using for each student on the Results page is:

=SUM(Spelling!B4:Z4)/SUMIF(Spelling!B4:Z4,"<>X",Spelling!$B$29:$Z$29)

Is there a way to easily convert the grades to percentages FIRST, and
then average those percentages on the Results page, in one single formula?

Thanks for catching that error!

wmd

 

[Bob Phillips]

There is. I came up with a formula using average, which resulted in a
different answer to Bernies, which is why I asked the question <g>

This is what I came up with

=AVERAGE(IF(B2:Z2<>"X",Sheet1!B2:Z2/Sheet1!$B$27:$Z$27))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[wmd]

There is. I came up with a formula using average, which resulted in a
different answer to Bernies, which is why I asked the question <g>

This is what I came up with

=AVERAGE(IF(B2:Z2<>"X",Sheet1!B2:Z2/Sheet1!$B$27:$Z$27))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Thanks. I entered the formula as you have it written, and it seemed to
work but with a bug. Do array formulas automatically update? Initially
the calculated values on the Results page were correct, but when I
changed one of the values on "Sheet1" it didn't seem to update on the
Results page. But if I deleted the formula and re-entered it, the
correct value displayed. Odd.

I'll play around some more with it... thanks to both you and Bernie for
your help. I'm slowly learning more about Excel as I go!

wmd

 

[Bob Phillips]

Try it like this

=AVERAGE(IF(B2:Z2<>"X",B2:Z2/$B$27:$Z$27))

still array entered.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[wmd]

Try it like this

=AVERAGE(IF(B2:Z2<>"X",B2:Z2/$B$27:$Z$27))

still array entered.

In this case, how would Excel know where the data range was? I'm
entering this on a Results page, but my data is entered on a subject
page (for example, Spelling!).

Your original formula with the worksheet reference works initially... it
just doesn't seem to update automatically.

wmd

 

[Bob Phillips]

Okay, I thought you were doing the results on the same page.

You could put the sheet names in column A say, and use

=AVERAGE(IF(INDIRECT("'"&A2&"'!B2:Z2")<>"X",INDIRECT("'"&A2&"'!B2:Z2")/INDIR
ECT("'"&A2&"'!B27:Z27")))

still an array formula


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)