IF Function

[NLCROSS]

Hi, all - I have been banging my head on the desk over this one...it's gotta be so simple, right? Have searched online, and on Mr. Excel, but still can't get this right.

I need a formula in cell H2 that says "IF cell H2 is greater than 0 but less than 30 then show 0-30, H2 is greater than 31 but less than 60 then show 31-60, H2 is greater than 61 but less than 90 then show 61-90, H2 is greater than 91 but less than 120 then show 91-120, H2 is greater than 121 but less than 150 then show 121-150, H2 is greater than 151 but less than 180 then show 151-180

Thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel.
Do you have a formula in H2?

 

[NLCROSS]

Hi & welcome to MrExcel.
Do you have a formula in H2?

No just numbers

 

[Fluff]

In that case you cannot do it with a formula. A cell can have either a value or a formula but not both.
However you could put a formula in another cell if that's ok?

 

[Dave Patton]

T202112a.xlsm
	H	I	J	K
1
2	175		0	0-30
3	151-180		31	31-60
4	151-180		61	61-90
5			91	91-120
6			121	121-150
7			151	151-180
8			181	>= 181
9
1b

Cell Formulas
Range	Formula
H3	H3	=LOOKUP(H2,J2:K8)
H4	H4	=LOOKUP(H2,{0,"0-30";31,"31-60";61,"61-90";91,"91-120";121,"121-150";151,"151-180";181,">= 181"})

 

[NLCROSS]

In that case you cannot do it with a formula. A cell can have either a value or a formula but not both.
However you could put a formula in another cell if that's ok?

Apologies' I want the 0-30, 31-60 etc to appear next to the column.

 

[Fluff]

In that case I would recommend using a lookup table as shown by Dave in post#5

 

[Dave Patton]

N.B. You can copy the post to a clean sheet.
Click on the icon below the f(x) in the heading below and paste the information into your sheet.
With Name Manager, I named the array (see J2).
New name aL Value {0,"0-30";31,"31-60";61,"61-90";91,"91-120";121,"121-150";151,"151-180";181,">= 181"}

T202112a.xlsm
	H	I	J	K	L	M
1
2	175	151-180	151-180	151-180	0	0-30
3	25	0-30	0-30	0-30	31	31-60
4	75	61-90	61-90	61-90	61	61-90
5	125	121-150	121-150	121-150	91	91-120
6	250	>= 181	>= 181	>= 181	121	121-150
7					151	151-180
8					181	>= 181
9
1b

Cell Formulas
Range	Formula
I2:I6	I2	=LOOKUP(H2,$L$2:$M$8)
J2:J6	J2	=LOOKUP(H2,{0,"0-30";31,"31-60";61,"61-90";91,"91-120";121,"121-150";151,"151-180";181,">= 181"})
K2:K6	K2	=LOOKUP(H2,aL)

 

[NLCROSS]

Thanks for the help guys