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

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

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


"Bert" <bertwhit@theworld.com> wrote in message
news:e7mne4$rot$1@pcls4.std.com...
>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.)
>
>
>

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" <sandymann2@mailinator.com> wrote in message
news:OqyRgXJmGHA.2112@TK2MSFTNGP04.phx.gbl...
> 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
>
> sandymann2@mailinator.com
> Replace@mailinator.com with @tiscali.co.uk
>
>
> "Bert" <bertwhit@theworld.com> wrote in message
> news:e7mne4$rot$1@pcls4.std.com...
> >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.)
> >
> >
> >
>
>

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" <bob.NGs@somewhere.com> wrote in message
news:uVA9nUKmGHA.3844@TK2MSFTNGP04.phx.gbl...
> 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" <sandymann2@mailinator.com> wrote in message
> news:OqyRgXJmGHA.2112@TK2MSFTNGP04.phx.gbl...
>> 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
>>
>> sandymann2@mailinator.com
>> Replace@mailinator.com with @tiscali.co.uk
>>
>>
>> "Bert" <bertwhit@theworld.com> wrote in message
>> news:e7mne4$rot$1@pcls4.std.com...
>> >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.)
>> >
>> >
>> >
>>
>>
>
>

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" <bertwhit@theworld.com> wrote in message
news:e7no2f$491$1@pcls4.std.com...
> 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" <bob.NGs@somewhere.com> wrote in message
> news:uVA9nUKmGHA.3844@TK2MSFTNGP04.phx.gbl...
> > 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" <sandymann2@mailinator.com> wrote in message
> > news:OqyRgXJmGHA.2112@TK2MSFTNGP04.phx.gbl...
> >> 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
> >>
> >> sandymann2@mailinator.com
> >> Replace@mailinator.com with @tiscali.co.uk
> >>
> >>
> >> "Bert" <bertwhit@theworld.com> wrote in message
> >> news:e7mne4$rot$1@pcls4.std.com...
> >> >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.)
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>
>

"Bob Phillips" <bob.NGs@somewhere.com> wrote in message
news:uVA9nUKmGHA.3844@TK2MSFTNGP04.phx.gbl...
> 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

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:eSp81bTmGHA.4816@TK2MSFTNGP03.phx.gbl...
> "Bob Phillips" <bob.NGs@somewhere.com> wrote in message
> news:uVA9nUKmGHA.3844@TK2MSFTNGP04.phx.gbl...
> > 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!