I am having the minimum and maximum values in two columns in an exce
spread sheet.I need to check whether the given value falls between th
maximum and minimum values.

For e.g Minimum column contains value like AAA_0001 and Maximum colum
contains AAA_1000
Let the given value be AAA_560.I need to check whether this value i
between those two columns.
Could anyone help me out in solving this .....?


regards
Jayanth

--
Jayanth
-----------------------------------------------------------------------
Jayanthi's Profile: http://www.excelforum.com/member.php...fo&userid=3003
View this thread: http://www.excelforum.com/showthread.php?threadid=49722

Hi

In a cell, say C1 enter your Minimum value AAA_0001
In D1 enter you rmax value AAA_1000

then the test is
=AND(A1$C$1,A1<$D$1)

Your value of AAA_560.1 would return FALSE, but AAA_0560.1 would return
TRUE


--
Regards

Roger Govier



Jayanthi wrote:
I am having the minimum and maximum values in two columns in an excel
spread sheet.I need to check whether the given value falls between the
maximum and minimum values.

For e.g Minimum column contains value like AAA_0001 and Maximum column
contains AAA_1000
Let the given value be AAA_560.I need to check whether this value is
between those two columns.
Could anyone help me out in solving this .....?


regards
Jayanthi


--
Jayanthi
------------------------------------------------------------------------
Jayanthi's Profile:
http://www.excelforum.com/member.php...o&userid=30035 View
this thread: http://www.excelforum.com/showthread...hreadid=497229

Thanks a lot Roger
I will try with this



Roger Govier Wrote:
Hi

In a cell, say C1 enter your Minimum value AAA_0001
In D1 enter you rmax value AAA_1000

then the test is
=AND(A1$C$1,A1<$D$1)

Your value of AAA_560.1 would return FALSE, but AAA_0560.1 woul
return
TRUE


--
Regards

Roger Govier



Jayanthi
wrote:
I am having the minimum and maximum values in two columns in a
excel
spread sheet.I need to check whether the given value falls betwee
the
maximum and minimum values.

For e.g Minimum column contains value like AAA_0001 and Maximu
column
contains AAA_1000
Let the given value be AAA_560.I need to check whether this value is
between those two columns.
Could anyone help me out in solving this .....?


regards
Jayanthi


--
Jayanthi

------------------------------------------------------------------------
Jayanthi's Profile:
http://www.excelforum.com/member.php...fo&userid=3003
View
this thread: http://www.excelforum.com/showthread.php?threadid=49722

--
Jayanth
-----------------------------------------------------------------------
Jayanthi's Profile: http://www.excelforum.com/member.php...fo&userid=3003
View this thread: http://www.excelforum.com/showthread.php?threadid=49722

The answer depends on how you want to do the comparison. If you want
to do a alphanumeric comparison you may want to ensure that the lengths
of all strings are the same and follow Roger's suggestion.

If, on the other hand, the comparison is based on the numeric part of
the tokens, consider something like:

Suppose the min. value AAA_0001 is in C2 and the corresponding max.
value is in D2. Suppose your test value is in E2. Then, to check your
value is greater than the min. use =0+MID(E2,5,1024)0+MID(C2,5,1024).
To check it is less than the max. use
=0+MID(E2,5,1024)<0+MID(D2,5,1024). Suppose the min. comparison result
is in F2 and the max. comparison result is in G2. Then, in H2 use the
AND() function to combine the two parts.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

I am having the minimum and maximum values in two columns in an excel
spread sheet.I need to check whether the given value falls between the
maximum and minimum values.

For e.g Minimum column contains value like AAA_0001 and Maximum column
contains AAA_1000
Let the given value be AAA_560.I need to check whether this value is
between those two columns.
Could anyone help me out in solving this .....?


regards
Jayanthi


--
Jayanthi
------------------------------------------------------------------------
Jayanthi's Profile: http://www.excelforum.com/member.php...o&userid=30035
View this thread: http://www.excelforum.com/showthread...hreadid=497229

For archival purposes, please try to use more meaningful subject lines and
please refrain from using the word URGENT!. All requests here are equally
urgent.
Try a helper column (could hide) with the formula
=VALUE(MID(B6,FIND("_",B6)+1,LEN(B6)))
then use something like

Sub getnumberincellmax_min()
myval = 500
minval = Application.Min(Columns("c"))
maxval = Application.Max(Columns("c"))
If myval = minval And myval <= maxval Then MsgBox "OK"
End Sub
--
Don Guillett
SalesAid Software

"Jayanthi" wrote in
message ...

I am having the minimum and maximum values in two columns in an excel
spread sheet.I need to check whether the given value falls between the
maximum and minimum values.

For e.g Minimum column contains value like AAA_0001 and Maximum column
contains AAA_1000
Let the given value be AAA_560.I need to check whether this value is
between those two columns.
Could anyone help me out in solving this .....?


regards
Jayanthi


--
Jayanthi
------------------------------------------------------------------------
Jayanthi's Profile:
http://www.excelforum.com/member.php...o&userid=30035
View this thread: http://www.excelforum.com/showthread...hreadid=497229

I tried with that ... I could'nt get those
will you please provide the code....

Cell A1 conatins AM_CM_0001
Cell B1 conatins AM_CM_1000

GIven value is AM_CM_0050


need to compare the characters then have to check whether the give
value is in that rang

--
Jayanth
-----------------------------------------------------------------------
Jayanthi's Profile: http://www.excelforum.com/member.php...fo&userid=3003
View this thread: http://www.excelforum.com/showthread.php?threadid=49722

In your original post you said aaa_001 NOT aaa_aaa_001. Try this.

=VALUE(MID(G4,FIND("_",G4,FIND("_",G4)+1)+1,LEN(G4 )-FIND("_",G4,FIND("_",G4))))

--
Don Guillett
SalesAid Software

"Jayanthi" wrote in
message ...

I tried with that ... I could'nt get those
will you please provide the code....

Cell A1 conatins AM_CM_0001
Cell B1 conatins AM_CM_1000

GIven value is AM_CM_0050


need to compare the characters then have to check whether the given
value is in that range


--
Jayanthi
------------------------------------------------------------------------
Jayanthi's Profile:
http://www.excelforum.com/member.php...o&userid=30035
View this thread: http://www.excelforum.com/showthread...hreadid=497229