I have several rows of scores, each score in a separate column. (One row
per individual.)
The first row of scores contains the perfect scores (highest scores
possible) for each column.
The second column in each row contains the total of all scores in that row.
(The second column in the first row thus contains the perfect score total.)
In the first column of each row (except the first one), I have the following
formula: =ROUND(((100*B2)/($B$1)),0), where B2 contains the total of all
scores in Row 2, and $B$1 contains the perfect total score. This yields--in
this example--a percentage score of row 2's total compared to the perfect
score total.
So far, everything's great.however.sometimes I don't want to count a
particular score for a given individual/row. In this situation, I enter "E"
(for "excused") in the appropriate cell.
QUESTION: I would like to modify the formula (perhaps replacing
$B$1.or.subtracting a calculated value from it), so that for each row, if
there's one or more "E's" in the row, the formula will adjust the perfect
score total by NOT including the corresponding perfect score (the perfect
score in the same column as the "E") in the perfect score total in the
formula.
I'd appreciate any suggestions! (Alternatively, I could, I suppose, do this
a Macro and run it whenever an E is added to a cell, but a formula seems the
better solution.)
Bert
(I sent a similar post a week or so ago and included a spreadsheet sample,
but the formatting got messed up, so rather that clarify, it only muddied
the waters more, and I don't know of any way to link the actual spreadsheet.
Sorry.)