If Formula to Select Answer from List

[JenMcG]

Hello,

I help to generate a formula that will select an answer from a list (1-5) from the following range:

5= 0 to 4 hours
4= 4.01 to 12 hours
3= 12.01 to 24 hours
2= 24.01 to 28 hours
1= 28.01+ hours

I already have the calculation formula to generate the total number of hours. Now I need a formula to select a score (1-5) based on the above criteria.

The cell that has the answer for the calculation of time is AG6.

Thanks,
Jen

 

[Joe4]

If the value in AG6 actually a "date/time" value, or just a simple number with decimal?
An easy way to verify is to change the Format to General, and see if the number changes or not.

If numbers, you can just use this formula:

=LOOKUP(AG6,{0,4.01,12.01,24.01,28.01},{5,4,3,2,1})

 

[Scott T]

Excel 2010
	A	B	C	D	E	F	G
1	num of hrs	Score				0	5
2	28	2				4.01	4
3						12.01	3
4						24.01	2
5						28.01	1
Sheet5

Cell Formulas
Range	Formula
B2	=VLOOKUP(A2,F1:G5,2)

 

[JenMcG]

The number returned in AG6 is [h]:mm:ss

 

[Joe4]

If it entered as time, then you will need to divide each of the values in the second argument of the formula ({0,4.01,12.01,24.01,28.01}) by 24 to get them to work correctly.
That is because dates/times are actually stored as numbers in Excel, specifically the number of days since 1/0/1900, and hours are just the fraction of one day.
So 12 hours is equivalent to 0.5 in Excel.

 

[JenMcG]

Hello,

I tried the above and it didn't seem to work. It returned an NA.

[TABLE="width: 68"]

<tbody>[TR]
[TD="width: 23%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=003366]#003366[/URL] "]
Received Date
[/TD]

[TD="width: 28%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=003366]#003366[/URL] "]
Contacted Date
[/TD]

[TD="width: 31%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] "]
TAT DAYS FROM RECEIVED TO CONTACTED
[/TD]

[TD="width: 18%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] "]
SCORE
[/TD]

[/TR]

[TR]
[TD="width: 23%"]
5/2/2021 6:31:47 PM
[/TD]

[TD="width: 28%"]
5/3/2021 12:42:29 PM
[/TD]

[TD="width: 31%"]
18:10:42
[/TD]

[TD="width: 18%"][/TD]

[/TR]

[TR]
[TD="width: 23%"]
3/22/2021 3:28:28 PM
[/TD]

[TD="width: 28%"]
3/22/2021 3:37:01 PM
[/TD]

[TD="width: 31%"]
0:08:33
[/TD]

[TD="width: 18%"][/TD]

[/TR]

[TR]
[TD="width: 23%"]
5/3/2021 8:13:52 AM
[/TD]

[TD="width: 28%"]
5/3/2021 12:44:48 PM
[/TD]

[TD="width: 31%"]
4:30:56
[/TD]

[TD="width: 18%"][/TD]

[/TR]

</tbody>[/TABLE]
Received Date Contacted Date TAT DAYS FROM RECEIVED TO CONTACTED SCORE
5/2/2021 6:31:47 PM 5/3/2021 12:42:29 PM 18:10:42
3/22/2021 3:28:28 PM 3/22/2021 3:37:01 PM 0:08:33
5/3/2021 8:13:52 AM 5/3/2021 12:44:48 PM 4:30:56

 

[Joe4]

If you make the change I suggested in my last post, it works (it returns 3, 5, 4 respectively, based on your examples).
Here is the formula with the adjustments I mentioned:

=LOOKUP(AG6,{0,0.167,0.501,1.01,1.167},{5,4,3,2,1})

 

[Scott T]

Excel 2010

	A	B	C	D	E	F	G
num of hrs	Score

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]18:10:42[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4:00:36[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12:00:36[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]24:00:36[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]28:00:36[/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=VLOOKUP(A2,F1:G5,2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F1[/TH]
[TD="align: left"]=0/24[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=4.01/24[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=12.01/24[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=24.01/24[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]=28.01/24[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[JenMcG]

I'm so sorry- you have both been very helpful but I can't get either of these to work. Doesn't it matter that my workbook is set to 1904 time/date?

Could this be done in a formula? I tried to write one but it wouldn't work. But I've used one like this in the past for something similar:

=IF(AND(AG6>=28:01:00),1,IF(AND(AG6<=28:00:00,AG6>=24:01:00),2,IF(AND(AG6<=24:00:00,AG6>=12:01:00),3,IF(AND(AG6<=12:01:00,AG6>=24:00:00),4,5))))