Getting conditional formula to work correctly

[D Kronk]

Does anyone have any suggestions on how to actually make
Excel do what it's supposed to do concerning conditional
formula. Specifically, I have to perform some basic
statistical analysis on a list of numbers and I'm trying
to get totals of the values that fall within the 1st, 2nd,
and 3rd, standard deviations (without having to count them
by hand!). I calculated the mean and standard deviation
but couldn't find a quick way to output values that told
me how many of my values fell into each category (how many
in 1st, how many in 2nd, and how many in 3rd).

I attempted to write a formula that would provide me with
certain output that I had hoped to use in a second
conditional formula. It's probably easiest if I give an
example: Suppose my Mean is 10 and my Standard Deviation
is 7. I wrote this formula: =IF(10-7<=A4<=10+7, "1st")
My value in cell A4 was 12 so I should have gotten "1st"
since it is between 3 and 17. However, I got "FALSE" as
my output. That's wrong.

I'm trying to write a nested formula that will provide me
with values that I could summarize more quickly, like this:
=IF(10-7<= CELL REFERENCE <=10+7, "1st", IF(10-(2*7)<=
CELL REFERENCE <=10+(2*7), "2nd", IF(10-(3*7)<= CELL
REFERENCE <=10+(3*7), "3rd", "OVER")))

That should look at the value in the cell I'm interested
in, compare it to the values within the ranges of 1st,
2nd, and 3rd standard deviations, and tell me which group
it's in by outputting "1st", "2nd", "3rd", or "OVER".

Am I completely wrong about this? I even tested this by
writing a simple IF statement and I still got FALSE as my
output. =IF(3<=12<=17, "1st") returned FALSE

Your help would be greatly appreciated since I'm trying to
get this done for fiscal year end. Thanks.

Doug Kronk
(e-mail address removed)
(e-mail address removed)

 

[Chip Pearson]

Doug,

I wrote this formula: =IF(10-7<=A4<=10+7, "1st")

The formula is syntactically wrong. You need to write this as

=IF(AND(10-7<=A4,A4<=10+7),"1st","result_if_false")

I'm trying to write a nested formula that will provide me
with values that I could summarize more quickly, like this:
=IF(10-7<= CELL REFERENCE <=10+7, "1st", IF(10-(2*7)<=
CELL REFERENCE <=10+(2*7), "2nd", IF(10-(3*7)<= CELL
REFERENCE <=10+(3*7), "3rd", "OVER")))

This, too, is syntactically incorrect. It should be,

=IF(AND(10-7<=A4,A4<=10+7),"1st",IF(AND(10-(2*7)<=A4,A4<=10+(2*7)),"2nd",IF(
AND(10-(3*7)<=A4,A4<=10+(3*7)),"3d","result_if_all_false")))

Am I completely wrong about this?

More or less, yes.

=IF(3<=12<=17, "1st") returned FALSE

What is happening here can be seen by watching Excel parse and evaluate the
formula. It starts with
=IF(3<=12<=17, "1st")
and the evaluates 3<=12, which returns TRUE:
=IF(TRUE<=17,"1st")
It then evaluates TRUE<=17. TRUE is ALWAYS greater than any number, so
TRUE<=17 returns FALSE:
=IF(FALSE,"1st")
Since there is no if false clause in the IF statement, the result is FALSE.
=FALSE



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com (e-mail address removed)

 

[D Kronk]

Chip,

Thanks so much for the help! I spent hours trying to find
an answer in the online help and never even found a hint
that I needed the AND condition placed in the beginning of
the formula. Intuitively, I knew what my formula was
asking, but I didn't know how to state it so the computer
knew what I ws asking. The explanation on how the data is
parsed helps immensely. Now I have a better understanding
about how to format my formulae for later.

I will be able to use this to get me a step closer to my
ultimate goal, but I have one last question. Is there
a 'canned' summary function that will tell me how many of
the values within my sample fall within the 1st-3rd
standard deviations?

Thanks again,

Doug Kronk