Excel Question #46

Ghoulish Delight · 02-01-2011, 07:41 PM

My first notion is this (not an easy 1-cell solution unfortunately). The following code will return the two end points of the range (assuming the range is in cell A1:

Left(A1,FIND("-",A1)-1)
Right(A1,Find("-",A1)-1)

Those are text results, and the only way I've found so far to get a numeric value is to put those formulas in ohter cells (we'll use A2 and A3), then use =Value()

So then, for a value of 289 in Cell B1 you can do a =IF(VALUE(A2)<=B2,IF(B2<=VALUE(A3),1,0),0)

You'll get a 1 if it is in the range, 0 if not.

02-01-2011, 07:41 PM	#1
Ghoulish Delight I Floop the Pig Join Date: Jan 2005 Location: Alternative Swankstyle Posts: 19,348	My first notion is this (not an easy 1-cell solution unfortunately). The following code will return the two end points of the range (assuming the range is in cell A1: Left(A1,FIND("-",A1)-1) Right(A1,Find("-",A1)-1) Those are text results, and the only way I've found so far to get a numeric value is to put those formulas in ohter cells (we'll use A2 and A3), then use =Value() So then, for a value of 289 in Cell B1 you can do a =IF(VALUE(A2)<=B2,IF(B2<=VALUE(A3),1,0),0) You'll get a 1 if it is in the range, 0 if not. __________________ 'He who receives an idea from me, receives instruction himself without lessening mine; as he who lights his taper at mine, receives light without darkening me.' -TJ
	Submit to Quotes