Need Function for questionnaire totals Pls

[Waldo Hechter]

Hi

Thank you for those who will help me.

I have a questionnaire that generates a number sum total between 0 and 40. (in cell A1)

The totals represent the following attributes:

Normal 0-13
Borderline 14-16
Abnormal 17-40

How do I show the value, Normal or Borderline or Abnormal (in cell B1) by referencing cell A1

Note: I'm new to functions

Thanks again

 

[royUK]

Use VLOOKUP for a nearest match - enter True not false. Set the table up as below

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"][TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64"] Normal [/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD] Borderline [/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD] Abnormal [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]


=VLOOKUP(J4,G4:H6,2,TRUE)

Adjust the range reference

 

[royUK]

Use VLOOKUP for a nearest match - enter True not false. Set the table up as below

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="width: 64, align: right"][TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64"] Normal [/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD] Borderline [/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD] Abnormal [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]


=VLOOKUP(J4,G4:H6,2,TRUE)

Adjust the range reference

 

[royUK]

Hi

Use VLOOKUP for a nearest match - enter True not false. Set the table up as below

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="width: 64, align: right"][TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64"] Normal [/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD] Borderline [/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD] Abnormal [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]


=VLOOKUP(J4,G4:H6,2,TRUE)

Adjust the range reference

 

[Billy C]

You could use a nested if statement

=IF(AND(A1>=0,A1<=13),"Normal",IF(AND(A1>=14,A1<=16),"Borderline",IF(AND(A1>=17,A1<=40),"Abnormal")))

 

[Waldo Hechter]

Thank you royUK

If I had more skill I could have used this I'm sure unfortunately I could not use this one.

Hi

Use VLOOKUP for a nearest match - enter True not false. Set the table up as below

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64, align: right"][TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64"] Normal[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD] Borderline[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD] Abnormal[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]


=VLOOKUP(J4,G4:H6,2,TRUE)

Adjust the range reference

 

[Waldo Hechter]

This worked perfectly, I could even adjust it to perform a similar function.

Thank you

You could use a nested if statement

=IF(AND(A1>=0,A1<=13),"Normal",IF(AND(A1>=14,A1<=16),"Borderline",IF(AND(A1>=17,A1<=40),"Abnormal")))