Excel Formula

[vijaychennai]

Hello all,

I have exel file in the below format.

Excel Workbook
	A
1	Hours
2	2
3	3
4	4
5	4
6	5
7	6
8	29
9	30
Sheet1

Answer :

Excel Workbook
	A	B
1	Hours	Answer
2	2
3	3
4	4
5	4
6	4.1	4 to 6
7	6	4 to 6
8	29	28 to 29
9	30	29 to 30
Sheet1

I required split....

 

[Drrellik]

you can set up a table and use vlookup or index(match) if you have much more that 4 arguments, or a nested IF statement like =if(A2<=4,"<4",if(A2<=6,"4 to 6",if(ect... for your arguments) I see you have a 28 to 29 do you have others between 4 to 6 in this answer sheet.

Don

 

[vijaychennai]

Not working... can you please explain more...

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E
1	Hours			Criteria
2	2	< 4		Hours	Result
3	3	< 4		0	< 4
4	4	4 to 6		4	4 to 6
5	4	4 to 6		7	6 to 27
6	5	4 to 6		28	28 to 29
7	6	4 to 6		29.1	>29 to 30
8	29	28 to 29
9	30	>29 to 30
10
2b

Excel 2003

Cell Formulas
Range	Formula
B2	=VLOOKUP(A2,$D$3:$E$7,2,1)

Edit the Lookup table for your exact criteria and results.

Check Excel's Help for Lookups.