Lickert Scale Formula Help Needed!

[Danny Boy]

I'm trying to figure out a formula that would produce the following flags"
based upon an evaluation form that patients are completing regarding the
services they received:

0-1.9=Definitely False
2.0-2.9=Mostly False
3.0-3.9=No Impact
4.0-4.9=Mostly True
5.0 or greater=Definitely True

The identifying issue are in Column A (e.g. staff was courteous), the raw
score outcomes are in Column B (3.91, 4.12, etc), and the formulas with the
identifying flags (e.g. No Impact, Mostly True, etc) are in Column C. In
total we are looking at 18 different indicators of service (e.g. courteous,
on time appointments, etc).

Example:

A B C

3 Courteous Staff 3.91 No Impact

I did attempt a formula, but it did not seem to work at 100%. Below is what
I came up with:

=IF(B3=0,"",IF(AND(B3>=0,B3<=1.9),"Definitely
False",IF(AND(B3>=2,B3<=2.9),"Mostly False",IF(AND(B3>=3,B3<=3.9),"No
Impact",IF(AND(B3>=4,B3<=4.9),"Mostly True","Definitely True")))))

Please note that if no value was yet indicated in column B, I wanted column
C to remain blank (no flag response at all). For this reason I had the
following as part of the formula:

=IF(B3=0,"",

Any help would be greatly appreciated!
Thank you in advance,

Dan

 

[Teethless mama]

=IF(B3="","",your formula goes here)

 

[Danny Boy]

=IF(B3="","",your formula goes here)

Actually it isn't the =IF(B3="","" portion of the formuula that was giving
me a problem. It was the other part of the formula:

IF(AND(B3>=0,B3<=1.9),"Definitely False",IF(AND(B3>=2,B3<=2.9),"Mostly
False",IF(AND(B3>=3,B3<=3.9),"No Impact",IF(AND(B3>=4,B3<=4.9),"Mostly
True","Definitely True")))))

For example, one of the outcome values I received (in column b) was 3.91.
Based upon that value, the flag in column c should have resuted in "No
Impact", however, the flag said "definitely true".

HELP!!!!

 

[Joe User]

I came up with:
=IF(B3=0,"",IF(AND(B3>=0,B3<=1.9),
"Definitely False",
IF(AND(B3>=2,B3<=2.9),"Mostly False",
IF(AND(B3>=3,B3<=3.9),"No Impact",
IF(AND(B3>=4,B3<=4.9),"Mostly True",
"Definitely True")))))

First, it is generally unnecessary and, in this case, undesirable to specify
lower as well as upper bounds of the numeric ranges. It is sufficient to
specify the just the one of the bounds, thus:

=IF(B3="","",
IF(B3<2, "Definitely False",
IF(B3<3, "Mostly False",
IF(B3<4, "No Impact",
IF(B3<5, "Mostly True", "Definitely True")))))

However, you can avoid so many nested function calls as follows:

=IF(B3="", "",
LOOKUP(B3, {0,2,3,4,5},
{"Definitely False","Mostly False","No Impact",
"Mostly True", "Definitely True"}))

Please note that if no value was yet indicated
in column B, I wanted column C to remain blank
(no flag response at all).

I've incorporated the answer in the suggestions above. Note the test for
B3="" instead of B3=0.


----- original message -----

 

[Meebers]

What if you get a value that does not meet the criteria of between =>3 and
=<3.9? In your example 3.91 does not fall within that range. Perhaps you
would use for "No impact" =>3 and <4, 3.91 or 3.9867 would fall within.
Others in your formula need the same changes...

What

 

[Joe User]

For example, one of the outcome values I received
(in column b) was 3.91. Based upon that value, the
flag in column c should have resuted in "No Impact",
however, the flag said "definitely true".

I think both of my suggestions address this. Our messages crossed on the
internet.

However, you need to decide and explain how you want to treat, say, 3.95 or
3.96. Do you want to consider that 3 ("no impact") or 4 ("definitely true")?

If 3, then again, I think both of my suggestions cover that.

If 4, then change references to B3 to ROUND(B3,0) in numeric comparisons.
That is:

=IF(B3="","",
IF(ROUND(B3,0)<2, "Definitely False",
IF(ROUND(B3,0)<3, "Mostly False",
IF(ROUND(B3,0)<4, "No Impact",
IF(ROUND(B3,0)<5, "Mostly True", "Definitely True")))))

However, you can avoid so many nested function calls as follows:

=IF(B3="", "",
LOOKUP(ROUND(B3,0), {0,2,3,4,5},
{"Definitely False","Mostly False","No Impact",
"Mostly True", "Definitely True"}))

You can see the increasing of the simplified form using LOOKUP.


----- original message -----

 

[Pete_UK]

You don't really need to test for a range of values if you apply the
IFs in sequence. So, you could try this:

=IF(OR(B3="",B3<=0),"",IF(B3<2,"Definitely False",IF(B3<3,"Mostly
False",IF(B3<4,"No Impact",IF(B3<5,"Mostly True","Definitely
True")))))

Hope this helps.

Pete

 

[Joe User]

PS:

Actually it isn't the =IF(B3="","" portion of the
formuula that was giving me a problem.

Sure it was! You just had not tested sufficiently to realize it.

You had said that if B3 was actually zero, you wanted "definitely false".
But you have written IF(B3=0, "", ...). That would have results in the
appearance of a blank cell, not "definitely false", when B3 is actually zero,
not simply blank.


----- original message -----

 

[Danny Boy]

Brilliant. I always feel so thankful for the wisdom in this group, and I
always learn so much too. Your formula Joe actually does that which makes
more sense (rounding up for scores whcih are closer to the next
interpretation level). I think it makes more sense for a 3.95 to be more
reflective of "Mostly True" as opposed to "No Impact". I didn't even think of
that.

Btw, if you have a moment Joe, could you explain the concept behind the
"lookup" feature. I've never used it, and I'm not sure I fully understand it.

Your formula however is great:

=IF(B3="", "",LOOKUP(ROUND(B3,0), {0,2,3,4,5},{"Definitely False","Mostly
False","No Impact","Mostly True","Definitely True"}))

Dan

 

[Joe User]

Your formula Joe actually does that which
makes more sense (rounding up for scores

You're welcome. Glad it helped.

Btw, if you have a moment Joe, could you
explain the concept behind the "lookup"
feature. I've never used it, and I'm not sure
I fully understand it.

I'll try to explain. But if it is still a mystery, see the description of
the "vector" syntax in the Help page for the LOOKUP function.

The general form that I used is:

LOOKUP(lookupValue, lookupVector, resultVector)

LOOKUP searches the lookupVector for the largest value less than or equal to
the lookupValue. If found, LOOKUP returns the corresponding value in the
resultVector.

Consider if you did not round B3. If B3 is 3.95, LOOKUP finds that 3 is the
largest value less than or equal to 3.95. That's the 3rd element of the
lookupVector. So LOOKUP returns the 3rd element of the resultVector, namely
"no impact".

Of course, since you have decided to round to an integer, if B3 is 3.95, you
would lookup 4. Since that is the 4th element in the lookupVector, LOOKUP
returns the 4th element of the resultVector, namely "mostly true".


----- original message -----

 

[Danny Boy]

Thanks again Joe. I am such a data geek, and I love Excel. Now I need to
learn Access!

I had another evaluation database which needs to be adjusted, so I am going
to extrapolate what you gave me and update those as well to account for
"rounding".

Have a nice weekend. Hope it is warmer where you are at than where I am lol!

Best,

Dan