IF cell between values, then...

[BravoBravoAu]

Hi guys,

Hopefully another quick and easy formatting (or conditional formatting) solution here, but I'm stuck.

I have a pretty sizable workbook, with one section focusing on hours worked for a particular task. What I'd like to be able to do is, depending on how many hours are recorded as being worked in column A, then an X is placed in the range (less than 2 hours / 2-3 hours / 3-6 hours / more than 6 hours).

I envisage cell B2 would need to be formulated IF A2 is less than 2 hours, then "X"; C2 IF A2 is 2-3, then "X"; D2 if A2 is 3-6, then "X"; E2 IF A2 is more than 6 then "X". BUT, I can't seem to get any to work as intended. Please help?

	A	B	C	D	E
1	Hours worked	0-2	2-3	3-6	6+
2	1	X
3	5			X
4	9				X
5	2		X
6	3		X
7	6
8	7
9	5
10	4
11	2

<tbody>
</tbody><colgroup><col><col><col span="4"></colgroup>

 

[BravoBravoAu]

I've solved it.

I put an additional column in to B to consider a nested IF formula (So...=IF(A2<2,"A",IF(A2<3,"B",IF(A2<6,"C","D")))).

C2 has the formula =IF(B2="A","X","").
D2 has the formula =IF(B2="B","X","").
E2 has the formula =IF(B2="C","X","").
F2 has the formula =IF(B2="D","X","").

I'm sure this isn't the best way, but it now works.

Thanks for your help.

 

[mrhstn]

You can do this with a single formula, take the first formula below enter it in B2, drag across then down

Book1
	A	B	C	D	E
1	Hours worked	0-2	2-3	3-6	6+
2	1	X
3	5			X
4	9				X
5	2		X
6	3			X
7	6				X
8	7				X
9	5			X
10	4			X
11	2		X
Sheet2

Cell Formulas
Range	Formula
B2	=IF(COLUMN(A1)=MATCH($A2, {0,2,3,6,99}), "X", "")
C2	=IF(COLUMN(B1)=MATCH($A2, {0,2,3,6,99}), "X", "")
D2	=IF(COLUMN(C1)=MATCH($A2, {0,2,3,6,99}), "X", "")
E2	=IF(COLUMN(D1)=MATCH($A2, {0,2,3,6,99}), "X", "")