summing values in one row based on values in another row
 

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.)

summing values in one row based on values in another row
 

Bert,

If I follow you correctly then try:

=ROUND(((100*B2)/($B$1-SUMPRODUCT((C2:K2="E")*C1:K1))),0)

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Bert" wrote in message
...
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.)

summing values in one row based on values in another row
 

why not the more efficient

=ROUND((100*B2)/($B$1-SUMIF(C2:K2,"E",C1:K1)),0)



--
HTH

Bob Phillips

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

"Sandy Mann" wrote in message
...
Bert,

If I follow you correctly then try:

=ROUND(((100*B2)/($B$1-SUMPRODUCT((C2:K2="E")*C1:K1))),0)

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Bert" wrote in message
...
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.)

summing values in one row based on values in another row
 

I've tried both Sandy's formula and Bob's. Both seem to work equally well.
Thanks to both of you!
Is there a functional equivalence to the two formula's? I'm enough of a
formula novice that I could possible say.
(It's enough to see that they're working!)
Thanks again.
Bert

"Bob Phillips" wrote in message
...
why not the more efficient

=ROUND((100*B2)/($B$1-SUMIF(C2:K2,"E",C1:K1)),0)



--
HTH

Bob Phillips

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

"Sandy Mann" wrote in message
...
Bert,

If I follow you correctly then try:

=ROUND(((100*B2)/($B$1-SUMPRODUCT((C2:K2="E")*C1:K1))),0)

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Bert" wrote in message
...
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.)

summing values in one row based on values in another row
 

Bert,

They are functionally equivalent, I just presented the alternative as SUMIF
is more efficient than SUMPRODUCT. SP is generally used with more than one
condition, and there is only one here, so it is not necessary.

--
HTH

Bob Phillips

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

"Bert" wrote in message
...
I've tried both Sandy's formula and Bob's. Both seem to work equally
well.
Thanks to both of you!
Is there a functional equivalence to the two formula's? I'm enough of a
formula novice that I could possible say.
(It's enough to see that they're working!)
Thanks again.
Bert

"Bob Phillips" wrote in message
...
why not the more efficient

=ROUND((100*B2)/($B$1-SUMIF(C2:K2,"E",C1:K1)),0)



--
HTH

Bob Phillips

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

"Sandy Mann" wrote in message
...
Bert,

If I follow you correctly then try:

=ROUND(((100*B2)/($B$1-SUMPRODUCT((C2:K2="E")*C1:K1))),0)

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Bert" wrote in message
...
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.)

summing values in one row based on values in another row
 

"Bob Phillips" wrote in message
...
why not the more efficient

Why not indeed sir! <g

I have noticed in the NG's - and I include myself in this - that because
SUMPRODUCT() can be used as a cover-all sticking plaster to use it rather
than think the solution through.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk

summing values in one row based on values in another row
 

"Sandy Mann" wrote in message
...
"Bob Phillips" wrote in message
...
why not the more efficient

Why not indeed sir! <g

I have noticed in the NG's - and I include myself in this - that because
SUMPRODUCT() can be used as a cover-all sticking plaster to use it rather
than think the solution through.

Guilty also!