Function with mutltiple value statements

[mikey090tx]

Hello All-

I am wondering if anyone would be able to assist with the following scenario. I am working on a project that involves adding variable data into cells that is then "graded" and given a point value. The data is as follows


Let's say cells A1-A20 contain the variable data and that the data can range from 2:55 to 3:25 (time). Cell B2-B20 would return a "score / grade" if the data in cell A1 was between the following criteria

2:55 – 3:05= 3 score
3:06-3:15= 2 score
3:16-3:25= 1 score

I have only been able to come up with this function and can only return 1 value and cannot place a difference or return the accurate variable if the time doesnt fall between the time that i use in the fuction. i tried to add more fuctions in different cells but get mutiple returns on the same time in A1. I hope this make sense. In the example below, if i entered 3:00 in cell A1 and used a fuction for every one of the three score categories i would end up with a 3 and a 2 and a 1 and i only want the formula to return one value for which the time actually fall between.

=IF(A1="","0",IF(A1<3:16,2,0))

 

[CWatts]

Perhaps this:

=LOOKUP(B6,{0,0.1215,0.129,0.136,0.143},{"Error",3,2,1,"Error"})

 

[T. Valko]

Hello All-

I am wondering if anyone would be able to assist with the following scenario. I am working on a project that involves adding variable data into cells that is then "graded" and given a point value. The data is as follows


Let's say cells A1-A20 contain the variable data and that the data can range from 2:55 to 3:25 (time). Cell B2-B20 would return a "score / grade" if the data in cell A1 was between the following criteria

2:55 – 3:05= 3 score
3:06-3:15= 2 score
3:16-3:25= 1 score

I have only been able to come up with this function and can only return 1 value and cannot place a difference or return the accurate variable if the time doesnt fall between the time that i use in the fuction. i tried to add more fuctions in different cells but get mutiple returns on the same time in A1. I hope this make sense. In the example below, if i entered 3:00 in cell A1 and used a fuction for every one of the three score categories i would end up with a 3 and a 2 and a 1 and i only want the formula to return one value for which the time actually fall between.

=IF(A1="","0",IF(A1<3:16,2,0))

Here's one way...

Book1
	A	B
1	3:10	2
2	3:25	1
3	2:59	3
4	3:06	2
5	3:21	1
6	3:20	1
7	2:55	3
8	2:57	3
9	3:16	1
10	3:22	1
Sheet1

Formula entered in B1 and copied down:

=IF(A1>=TIME(3,16,0),1,IF(A1>=TIME(3,6,0),2,3))

 

[mikey090tx]

Biff-

Thanks for the reply, the formula works great. One small question, where would i add the " ", 0 to add a 0 value if i have an empty cell as it scores at a 3 since the it is blank and would considered to be less than 3:05?

 

[T. Valko]

Biff-

Thanks for the reply, the formula works great. One small question, where would i add the " ", 0 to add a 0 value if i have an empty cell as it scores at a 3 since the it is blank and would considered to be less than 3:05?

Try this...

=IF(A1="",0,IF(A1>=TIME(3,16,0),1,IF(A1>=TIME(3,6,0),2,3)))

 

[pgc01]

Hi

Try also, like CWatt's:

=LOOKUP(A1,0+{0,"2:55","3:06","3:16"},{" ",3,2,1})