Lickert Scale Formula Help Needed!

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

"Joe User" wrote:

"Danny Boy" wrote:
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" wrote:
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" wrote:

"Danny Boy" wrote:
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(B32, "Definitely False",
IF(B33, "Mostly False",
IF(B34, "No Impact",
IF(B35, "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 -----

"Danny Boy" wrote:
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