Finding a number

S

Sam

Hi, I wonder if any one can help, I want to look for a number in a row of
cells, I have a row of twenty cells that have a number in each all different,
What I would like to do is search the row for a number between 39814 and
39844 and copy it into another cell, there will only be one number in this
range.
Can any one help, Thanks in advance, Sam.
 
T

T. Valko

One way...

A1:T1 = numbers

A3 = 39814
B3 = 39844

Is it just a coincidence that those are the serial date numbers for the
dates 1/1/2009 and 1/31/2009 ?

=SUMIF(A1:T1,">="&A3,A1:T1)-SUMIF(A1:T1,">"&B3,A1:T1)
 
T

T. Valko

=SUMIF(A1:T1,">="&A3,A1:T1)-SUMIF(A1:T1,">"&B3,A1:T1)

Even better!

=SUMIF(A1:T1,">="&A3)-SUMIF(A1:T1,">"&B3)
 
M

Max

Here's one way, Sam

Assume your source range is A2:A20
Put this in the cell where you want the result,
say in B2, press normal ENTER to confirm:
=INDEX(A2:A20,MATCH(1,INDEX((A2:A20>39814)*(A2:A20<39844),),0))
B2 will return the required result

If you need an error trap to return neat looking blanks should there not be
a match, use IF(ISNA to trap the MATCH part of it, indicatively:
=IF(ISNA(MATCH(..)),"",INDEX(..))

viz, in B2
=IF(ISNA(MATCH(1,INDEX((A2:A20>39814)*(A2:A20<39844),),0)),"",INDEX(A2:A20,MATCH(1,INDEX((A2:A20>39814)*(A2:A20<39844),),0)))

Success? celebrate it, hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
S

Sam

HI, T thanks a will give that a try and yes they are dates, my dad said it
may be easier to convert them to numbers, Sam
 
Top