Finding SUM ignoring zero or blank values

There must be more to the issue Let's assume the cells to be summed are
A1:A10 and only 4 of the cells contain the number 2, the rest of the cells
contain text, 0, or are blank. The sum of the 10 cells will be 8 - IOW,
neither of those 3 types of content have any influence on a SUM.

I have no idea what type of conversion to a "score" you need to do, but
summing the cells should be no problem at all. Your formula could be any of:

=SUM(A1:G20)
=SUM(A1,B10,C5,D12)
=SUM(A1:B10,C9:D12,H11)

Only the cells that actually contain values will influence the total.

Regards |:>)
Bob Jones
[MVP] Office:Mac



On 1/2/09 9:56 AM, in article
418B59AD-9D84-48F5-BF98-E8E35D4E9969 RemoveThis @microsoft.com, "tsmith0330"
<tsmith0330 RemoveThis @discussions.microsoft.com> wrote:

> I have multiple columns with four choices in a drop-down menu, one of which
> is "Not Applicable". The person filling out the form makes a selection from
> these four choices, and depending on the selection, a value is returned.
> What I need to do is calculate the sum of the resulting values, ignoring any
> zero/blank cells returned by the "Not Applicable" choice. My boss then wants
> me to take that total and convert it to a "score". I have been tearing my
> hair out trying to do this. I have found how to calculate averages ignoring
> zero/blank values, but not how to calculate sums that ignore zero/blank
> values. What really makes it interesting is that I have no idea which cells
> will end up being blank/zero--that will be determined by the user whenever
> they are performing the evaluation using this spreadsheet. Help??????????

Sorry--I knew I was leaving something out. I need for the blanks to not
detract from the person's overall score. Right now, I have it where the
choice "Excellent" returns a 3, "Satisfactory" returns a 2, "Needs Coaching"
returns a 1, and "Not Applicable" returns a blank. The highest score a
person can get is if all the responses are "Excellent". In one section,
there are four skills the person is being critiqued on, so the highest score
they could receive is a 12 if they are rated as "Excellent" on all four
skills. If the responses to all four questions are any of the first three
choices, then the overall score is accurately reflected. When I throw in a
"Not Applicable" response, though, it detracts from the person's overall
score. I need to be able to have the N/A choice without it having a negative
impact on the resulting sum, or score.

Am I just totally fubarred on this one? I'm probably going about this in
totally the wrong way!

"CyberTaz" wrote:

> There must be more to the issue Let's assume the cells to be summed are
> A1:A10 and only 4 of the cells contain the number 2, the rest of the cells
> contain text, 0, or are blank. The sum of the 10 cells will be 8 - IOW,
> neither of those 3 types of content have any influence on a SUM.
>
> I have no idea what type of conversion to a "score" you need to do, but
> summing the cells should be no problem at all. Your formula could be any of:
>
> =SUM(A1:G20)
> =SUM(A1,B10,C5,D12)
> =SUM(A1:B10,C9:D12,H11)
>
> Only the cells that actually contain values will influence the total.
>
> Regards |:>)
> Bob Jones
> [MVP] Office:Mac
>
>
>
> On 1/2/09 9:56 AM, in article
> 418B59AD-9D84-48F5-BF98-E8E35D4E9969 RemoveThis @microsoft.com, "tsmith0330"
> <tsmith0330 RemoveThis @discussions.microsoft.com> wrote:
>
> > I have multiple columns with four choices in a drop-down menu, one of which
> > is "Not Applicable". The person filling out the form makes a selection from
> > these four choices, and depending on the selection, a value is returned.
> > What I need to do is calculate the sum of the resulting values, ignoring any
> > zero/blank cells returned by the "Not Applicable" choice. My boss then wants
> > me to take that total and convert it to a "score". I have been tearing my
> > hair out trying to do this. I have found how to calculate averages ignoring
> > zero/blank values, but not how to calculate sums that ignore zero/blank
> > values. What really makes it interesting is that I have no idea which cells
> > will end up being blank/zero--that will be determined by the user whenever
> > they are performing the evaluation using this spreadsheet. Help??????????
>
>

You're still leaving something out -

The text entry of Not Applicable will be ignored by SUM() so the total
will not be negatively impacted.

For instance, 3 "Excellent"s and an Not Applicable should still add up
to 9 points, right?

How do you "have it" to get your numeric results? Does Not Applicable
actually return a negative value?

Perhaps if you replied with your actual formulae, that might give a clue.


In article <79D748C4-C2F6-4E89-BD4F-4DC3FD0AB82A.TakeThisOut@microsoft.com>,
tsmith0330 <tsmith0330.TakeThisOut@discussions.microsoft.com> wrote:

> Sorry--I knew I was leaving something out. I need for the blanks to not
> detract from the person's overall score. Right now, I have it where the
> choice "Excellent" returns a 3, "Satisfactory" returns a 2, "Needs Coaching"
> returns a 1, and "Not Applicable" returns a blank. The highest score a
> person can get is if all the responses are "Excellent". In one section,
> there are four skills the person is being critiqued on, so the highest score
> they could receive is a 12 if they are rated as "Excellent" on all four
> skills. If the responses to all four questions are any of the first three
> choices, then the overall score is accurately reflected. When I throw in a
> "Not Applicable" response, though, it detracts from the person's overall
> score. I need to be able to have the N/A choice without it having a negative
> impact on the resulting sum, or score.
>
> Am I just totally fubarred on this one? I'm probably going about this in
> totally the wrong way!
>
> "CyberTaz" wrote:
>
> > There must be more to the issue Let's assume the cells to be summed are
> > A1:A10 and only 4 of the cells contain the number 2, the rest of the cells
> > contain text, 0, or are blank. The sum of the 10 cells will be 8 - IOW,
> > neither of those 3 types of content have any influence on a SUM.
> >
> > I have no idea what type of conversion to a "score" you need to do, but
> > summing the cells should be no problem at all. Your formula could be any
> > of:
> >
> > =SUM(A1:G20)
> > =SUM(A1,B10,C5,D12)
> > =SUM(A1:B10,C9:D12,H11)
> >
> > Only the cells that actually contain values will influence the total.
> >
> > Regards |:>)
> > Bob Jones
> > [MVP] Office:Mac
> >
> >
> >
> > On 1/2/09 9:56 AM, in article
> > 418B59AD-9D84-48F5-BF98-E8E35D4E9969.TakeThisOut@microsoft.com, "tsmith0330"
> > <tsmith0330.TakeThisOut@discussions.microsoft.com> wrote:
> >
> > > I have multiple columns with four choices in a drop-down menu, one of
> > > which
> > > is "Not Applicable". The person filling out the form makes a selection
> > > from
> > > these four choices, and depending on the selection, a value is returned.
> > > What I need to do is calculate the sum of the resulting values, ignoring
> > > any
> > > zero/blank cells returned by the "Not Applicable" choice. My boss then
> > > wants
> > > me to take that total and convert it to a "score". I have been tearing
> > > my
> > > hair out trying to do this. I have found how to calculate averages
> > > ignoring
> > > zero/blank values, but not how to calculate sums that ignore zero/blank
> > > values. What really makes it interesting is that I have no idea which
> > > cells
> > > will end up being blank/zero--that will be determined by the user
> > > whenever
> > > they are performing the evaluation using this spreadsheet.
> > > Help??????????
> >
> >

Ok, here's the formula I'm using to return a value when the person makes a
selection:

=IF(D11="N/A", "-", IF(D11="C", 1, IF(D11="B", 2, IF(D11="A", 3, )))).

Here is a snapshot of one section of my worksheet:

Customer Interactions

A. Selected Appropriate Text Not Applicable N/A -
B. Applied Appropriate Structure Needs Coaching C 1
C. Customized text appropriately Satisfactory B 2
D. Delivered SWA Brand Promise Excellent A 3
E. Valued Customer's time Excellent A 3

Subtotal Customer Interaction Score: 9

So, the employee being critiqued receives a score of 9, but the N/A line
should not count against that person the way it is doing. Will simply
changing the value of the N/A selection to a 3 solve that problem? Then, I
guess, if the supervisor rates the employee "Not Applicable" on all four
skills, the total score would show up the same as if the employee had been
rated "Excellent" on all four skills...but I guess that would be ok...I mean,
we'd be able to see that, while the total reflects a perfect score, it would
say "Not Applicable" all the way down, so we'd just know to disregard the
score. Geez...I think I'm making this a lot more complicated than I need to!


"JE McGimpsey" wrote:

> You're still leaving something out -
>
> The text entry of Not Applicable will be ignored by SUM() so the total
> will not be negatively impacted.
>
> For instance, 3 "Excellent"s and an Not Applicable should still add up
> to 9 points, right?
>
> How do you "have it" to get your numeric results? Does Not Applicable
> actually return a negative value?
>
> Perhaps if you replied with your actual formulae, that might give a clue.
>
>
> In article <79D748C4-C2F6-4E89-BD4F-4DC3FD0AB82A.DeleteThis@microsoft.com>,
> tsmith0330 <tsmith0330.DeleteThis@discussions.microsoft.com> wrote:
>
> > Sorry--I knew I was leaving something out. I need for the blanks to not
> > detract from the person's overall score. Right now, I have it where the
> > choice "Excellent" returns a 3, "Satisfactory" returns a 2, "Needs Coaching"
> > returns a 1, and "Not Applicable" returns a blank. The highest score a
> > person can get is if all the responses are "Excellent". In one section,
> > there are four skills the person is being critiqued on, so the highest score
> > they could receive is a 12 if they are rated as "Excellent" on all four
> > skills. If the responses to all four questions are any of the first three
> > choices, then the overall score is accurately reflected. When I throw in a
> > "Not Applicable" response, though, it detracts from the person's overall
> > score. I need to be able to have the N/A choice without it having a negative
> > impact on the resulting sum, or score.
> >
> > Am I just totally fubarred on this one? I'm probably going about this in
> > totally the wrong way!
> >
> > "CyberTaz" wrote:
> >
> > > There must be more to the issue Let's assume the cells to be summed are
> > > A1:A10 and only 4 of the cells contain the number 2, the rest of the cells
> > > contain text, 0, or are blank. The sum of the 10 cells will be 8 - IOW,
> > > neither of those 3 types of content have any influence on a SUM.
> > >
> > > I have no idea what type of conversion to a "score" you need to do, but
> > > summing the cells should be no problem at all. Your formula could be any
> > > of:
> > >
> > > =SUM(A1:G20)
> > > =SUM(A1,B10,C5,D12)
> > > =SUM(A1:B10,C9:D12,H11)
> > >
> > > Only the cells that actually contain values will influence the total.
> > >
> > > Regards |:>)
> > > Bob Jones
> > > [MVP] Office:Mac
> > >
> > >
> > >
> > > On 1/2/09 9:56 AM, in article
> > > 418B59AD-9D84-48F5-BF98-E8E35D4E9969.DeleteThis@microsoft.com, "tsmith0330"
> > > <tsmith0330.DeleteThis@discussions.microsoft.com> wrote:
> > >
> > > > I have multiple columns with four choices in a drop-down menu, one of
> > > > which
> > > > is "Not Applicable". The person filling out the form makes a selection
> > > > from
> > > > these four choices, and depending on the selection, a value is returned.
> > > > What I need to do is calculate the sum of the resulting values, ignoring
> > > > any
> > > > zero/blank cells returned by the "Not Applicable" choice. My boss then
> > > > wants
> > > > me to take that total and convert it to a "score". I have been tearing
> > > > my
> > > > hair out trying to do this. I have found how to calculate averages
> > > > ignoring
> > > > zero/blank values, but not how to calculate sums that ignore zero/blank
> > > > values. What really makes it interesting is that I have no idea which
> > > > cells
> > > > will end up being blank/zero--that will be determined by the user
> > > > whenever
> > > > they are performing the evaluation using this spreadsheet.
> > > > Help??????????
> > >
> > >
>

This works to return the numerical equivalents of the responses, but the
question remains "How are you totaling the results?". And what do you mean
by "counting against that person"? Your example *should* total 9.

No offense, but if I'm guessing correctly the problem you're perceiving lies
with your evaluation methodology, not the math.

The maximum - or "perfect" - score is 15, but the way you're going about it
every N/A reduces that individual's possible total by 3. IOW, someone who is
rated N/A, N/A, 3, N/A, N/A would get a total of 3 & so would someone who
was rated 1, 1, 1, N/A, N/A - even though the first individual's evaluation
should be "Excellent".

You might want to take a look at SUMIF which can be written to sum only
those cells which contain 1-3 (do not contain N/A) or rethink your
evaluation approach. John will probably have other thoughts to offer.

Regards |:>)
Bob Jones
[MVP] Office:Mac



On 1/2/09 11:07 AM, in article
DA9B2E28-AFC5-4450-953A-04C88D3DF1D3 RemoveThis @microsoft.com, "tsmith0330"
<tsmith0330 RemoveThis @discussions.microsoft.com> wrote:

> =IF(D11="N/A", "-", IF(D11="C", 1, IF(D11="B", 2, IF(D11="A", 3, ))))

Ok, I'll think about the evaluation process some more and see if I can come
up with something better. Thank you both for trying to help me!

"CyberTaz" wrote:

> This works to return the numerical equivalents of the responses, but the
> question remains "How are you totaling the results?". And what do you mean
> by "counting against that person"? Your example *should* total 9.
>
> No offense, but if I'm guessing correctly the problem you're perceiving lies
> with your evaluation methodology, not the math.
>
> The maximum - or "perfect" - score is 15, but the way you're going about it
> every N/A reduces that individual's possible total by 3. IOW, someone who is
> rated N/A, N/A, 3, N/A, N/A would get a total of 3 & so would someone who
> was rated 1, 1, 1, N/A, N/A - even though the first individual's evaluation
> should be "Excellent".
>
> You might want to take a look at SUMIF which can be written to sum only
> those cells which contain 1-3 (do not contain N/A) or rethink your
> evaluation approach. John will probably have other thoughts to offer.
>
> Regards |:>)
> Bob Jones
> [MVP] Office:Mac
>
>
>
> On 1/2/09 11:07 AM, in article
> DA9B2E28-AFC5-4450-953A-04C88D3DF1D3.DeleteThis@microsoft.com, "tsmith0330"
> <tsmith0330.DeleteThis@discussions.microsoft.com> wrote:
>
> > =IF(D11="N/A", "-", IF(D11="C", 1, IF(D11="B", 2, IF(D11="A", 3, ))))
>
>

Gosh, I didn't think it was *that hard* to guess what the OP really
wanted 0_0

I figured what he wants is the *average* of all numeric responses. As
such, he can't just do a SUM(a1:a100) because that won't indicate how
many NAs there are.

Leaving aside the obvious option to use a real tool like R or MatLab,
which will easily allow one to reject NAs when calculating an average,
SUMIF() won't work because it doesn't keep count of the number of non-NA
cells involved.
A quick kludge would be to calculate COUNTIF(ISNUMBER([cellrange])) to
determine how many valid entries there are, and divide the result of
some summing operation by that value.

I bet there's an array formula involving
{AVERAGE(IF(ISNUMBER([cellrange])))} *_*


In article <jemcgimpsey-4F6F8F.08190502012009 RemoveThis @news.microsoft.com>,
JE McGimpsey <jemcgimpsey RemoveThis @mvps.org> wrote:

> In article <418B59AD-9D84-48F5-BF98-E8E35D4E9969 RemoveThis @microsoft.com>,
> tsmith0330 <tsmith0330 RemoveThis @discussions.microsoft.com> wrote:
>
> > I have multiple columns with four choices in a drop-down menu, one of which
> > is "Not Applicable". The person filling out the form makes a selection
> > from
> > these four choices, and depending on the selection, a value is returned.
> > What I need to do is calculate the sum of the resulting values, ignoring
> > any
> > zero/blank cells returned by the "Not Applicable" choice. My boss then
> > wants
> > me to take that total and convert it to a "score". I have been tearing my
> > hair out trying to do this. I have found how to calculate averages
> > ignoring
> > zero/blank values, but not how to calculate sums that ignore zero/blank
> > values. What really makes it interesting is that I have no idea which
> > cells
> > will end up being blank/zero--that will be determined by the user whenever
> > they are performing the evaluation using this spreadsheet. Help??????????
>
> First, even if you didn't ignore zero values, it wouldn't affect a
> SUM(), since adding zero to any number doesn't change the result.
>
> Second, blank cells are treated as zeroes by XL in math functions, so
> you don't need to worry about them either.
>
> OTOH, Text (including space characters) cause a #VALUE! error in math
> functions, e.g.,
>
> =A1 + B1
>
> However, if you use SUM(), e.g.:
>
> =SUM(A1:A10)
>
> or
>
> =SUM(A1, B2, C3)
>
> the function will ignore text (as well as blanks or zeros).

--
Team EM to the rescue! http://www.team-em.com

In article <carl-E901FA.17225102012009 DeleteThis @reader.motzarella.org>,
Carl Witthoft <carl DeleteThis @witthoft.com> wrote:

> Gosh, I didn't think it was *that hard* to guess what the OP really
> wanted 0_0
>
> I figured what he wants is the *average* of all numeric responses. As
> such, he can't just do a SUM(a1:a100) because that won't indicate how
> many NAs there are.

Silly me... rather than guessing, I took the OP's written words:

> > I have found how to calculate averages ignoring zero/blank values,
> > but not how to calculate sums that ignore zero/blank values.


at face value to indicate that he/she *didn't* want more help with the
average...

> Leaving aside the obvious option to use a real tool like R or MatLab,
> which will easily allow one to reject NAs when calculating an average,

Um... XL's AVERAGE() does this automatically, and doesn't require either
purchasing an expensive new package or learning a comprehensive stats
application to calculate an average.

> I bet there's an array formula involving
> {AVERAGE(IF(ISNUMBER([cellrange])))}

Not sure how that would be of any use, given that AVERAGE() already
ignores any non-numeric entries.