Finding SUM ignoring zero or blank values

[tsmith0330]

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??????????

 

[CyberTaz]

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,C912,H11)

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

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

 

[JE McGimpsey]

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

 

[tsmith0330]

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!

 

[JE McGimpsey]

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.

 

[tsmith0330]

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!

 

[CyberTaz]

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

 

[tsmith0330]

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!

 

[Carl Witthoft]

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])))} *_*

 

[JE McGimpsey]

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:


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.

 

[Carl Witthoft]

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

Naaah: Rule Number [something] of Help Desk: figure out what the user
actually wants, not what he asks for

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.

oops. silly me for not bothering to check the documentation on
average() . Sorry. But if the OP actually has some *zero* values and
he wants those ignored, then of course XL's average() won't do what he
wanted. --- neither would my cheap hack.

So anyway, I apologize if, among other things, the OP had some aversion
to an average and absolutely had to find the sum, biased by the number
of valid entries. So I'm drifting further and further OT here, but
it's becoming clear that this is one of many cases where the original
question came about due to poor design/specification of the data
analysis to be done.
BTW, I will stoutly maintain that, while XL has plenty of good uses,
it's a disservice to fail to steer people doing stats or data analysis
away from XL and towards proper tools. Yes, there's a learning curve,
and yes, it's well worth the startup effort.

 

[JE McGimpsey]

Naaah: Rule Number [something] of Help Desk: figure out what the user
actually wants, not what he asks for

I thought rule #1 was "Have you restarted your machine? You did? Well do
it again ... I'll wait."

BTW, I will stoutly maintain that, while XL has plenty of good uses,
it's a disservice to fail to steer people doing stats or data analysis
away from XL and towards proper tools. Yes, there's a learning curve,
and yes, it's well worth the startup effort.

While I'm personally very sympathetic with your preference (I've
supported clients using MATLAB, MINITAB, R, SPSS, and S-PLUS), I'm more
cynical. My experience is that, in most cases, those that are having
problems with XL do not solve them by changing applications.

For example, the vast majority of my clients who decry the loss of the
ATP wizards in XL08 don't understand that those wizards just applied
built-in XL functions. Yes, a few of them lament the loss of convenience
-- and many of those are satisfied with the Oatbran-type templates --
but the majority wouldn't know how to build a histogram if their careers
depended on it. That leads me to believe that they have so little
understanding of what their tool is doing that they're actually
dangerous to their employers. Changing to R won't solve that. If
anything, I worry that they'll blindly trust the results from R *more*
because the application is "better"...

Again, in my experience, with few exceptions it has been those who've
already competently extended XL to its limits that both see the
advantage of changing and have the motivation to competently change.

Just my US$0.02. YMMV.

 

[Carl Witthoft]

While I'm personally very sympathetic with your preference (I've
supported clients using MATLAB, MINITAB, R, SPSS, and S-PLUS), I'm more
cynical. My experience is that, in most cases, those that are having
problems with XL do not solve them by changing applications.

You have a good point there.

I'll shut up (mostly ) about alternatives, in this ng anyway.


Carl

 

[JE McGimpsey]

You have a good point there.

I'll shut up (mostly ) about alternatives, in this ng anyway.

Oh, no need to stop - you may hit someone who could benefit!

I'd just leave out the "real" in "real tool". XL's a real tool, too.
Hammers have limitations when it comes to driving screws (and a few more
when backing them out again). But then, so do MATLAB and R...