If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lickert Scale Formula Help Needed!
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 |
#2
|
|||
|
|||
Lickert Scale Formula Help Needed!
=IF(B3="","",your formula goes here)
"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 |
#3
|
|||
|
|||
Lickert Scale Formula Help Needed!
"Teethless mama" wrote: =IF(B3="","",your formula goes here) "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 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!!!! |
#4
|
|||
|
|||
Lickert Scale Formula Help Needed!
"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 |
#5
|
|||
|
|||
Lickert Scale Formula Help Needed!
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 "Danny Boy" wrote in message ... "Teethless mama" wrote: =IF(B3="","",your formula goes here) "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 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!!!! |
#6
|
|||
|
|||
Lickert Scale Formula Help Needed!
"Danny Boy" wrote:
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 ----- "Danny Boy" wrote: "Teethless mama" wrote: =IF(B3="","",your formula goes here) "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 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!!!! |
#7
|
|||
|
|||
Lickert Scale Formula Help Needed!
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(B32,"Definitely False",IF(B33,"Mostly False",IF(B34,"No Impact",IF(B35,"Mostly True","Definitely True"))))) Hope this helps. Pete On Feb 12, 6:39*pm, Danny Boy wrote: 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!!!!- Hide quoted text - - Show quoted text - |
#8
|
|||
|
|||
Lickert Scale Formula Help Needed!
PS:
"Danny Boy" wrote: 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" wrote: "Teethless mama" wrote: =IF(B3="","",your formula goes here) "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 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!!!! |
#9
|
|||
|
|||
Lickert Scale Formula Help Needed!
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 |
#10
|
|||
|
|||
Lickert Scale Formula Help Needed!
"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 |
|
Thread Tools | |
Display Modes | |
|
|