MATCH??

mcarter973 · Apr 2nd, 2002, 08:41 AM

I have data in A1:A20 - I would like to input a formula in A21 to tell me if data in A1:A20 is the same (i.e. each cell equals 1%). Also, can i include a threshold (if one cell equals 1.0002% and another cell equals 1.0003% the formula would still return "TRUE".

wi_guy · Apr 2nd, 2002, 08:51 AM

what about something like this, what lets you set the tolerance...... =IF(MAX(A1:A20)-MIN(A1:A20)<=0.005,"true","FALSE")

Dave Patton · Apr 2nd, 2002, 08:58 AM

consider one of the following

=AND(SUM(A1:A20)>=0.2,SUM(A1:A20)<=0.200005,COUNTIF(A1:A20,0.01)>=18)

=AND(SUM(A1:A20)>=0.2,SUM(A1:A20)<=0.200005)

=IF((SUM(A1:A20)>=0.2)*(SUM(A1:A20)<=0.200005),"Yes within range","No outside range")

[ This Message was edited by: Dave Patton on 2002-04-02 07:59 ]

Al Chara · Apr 2nd, 2002, 09:06 AM

Try the following:

=IF(C1:C5<0.02,IF(LEFT(C1:C5,4="0.01","TRUE","FALSE"),"FALSE")

You can change the tolerance by changing LEFT(C1:C5,4="0.01"
Since this is an array formula (CSE formula) you have to enter it in by pressing Ctr+Shift+Enter not just a plain enter.

Mark W. · Apr 2nd, 2002, 09:08 AM

Consider the array formula...

{=AND(ROUND(A1:A20,2)=0.01)}

The desired precision is governed by the 2nd ROUND argument (in red).

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

[ This Message was edited by: Mark W. on 2002-04-02 08:37 ]

Aladin Akyurek · Apr 2nd, 2002, 09:34 AM

Probably a bit convoluted, but try:

=AND(ROUND(MAX(A1:A20),LEN(B2)-2)<=ROUND(B1+B2,LEN(B2)-2),ROUND(MIN(A1:A20),LEN(B2)-2)>=ROUND(B1-B2,LEN(B2)-2))

where B1 houses the essential value (that is, 1%) and B2 the allowed difference or the tolerance, e.g., 0.0002%.

wi_guy · Apr 2nd, 2002, 09:37 AM

Aladin, a BIT convoluted????? I got a nosebleed when trying to read that one.

Just kidding....nicely done as always

Aladin Akyurek · Apr 2nd, 2002, 09:40 AM

Quote:

On 2002-04-02 08:37, wi_guy wrote:
Aladin, a BIT convoluted????? I got a nosebleed when trying to read that one.

Just kidding....nicely done as always

Inspired from yours... forget to record that.

Aladin

Apr 2nd, 2002, 09:06 AM	#4
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Try the following: =IF(C1:C5<0.02,IF(LEFT(C1:C5,4="0.01","TRUE","FALSE"),"FALSE") You can change the tolerance by changing LEFT(C1:C5,4="0.01" Since this is an array formula (CSE formula) you have to enter it in by pressing Ctr+Shift+Enter not just a plain enter. __________________ Kind regards, Al Chara

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 2nd, 2002, 08:41 AM	#1
mcarter973 Board Regular Join Date: Mar 2002 Posts: 83	I have data in A1:A20 - I would like to input a formula in A21 to tell me if data in A1:A20 is the same (i.e. each cell equals 1%). Also, can i include a threshold (if one cell equals 1.0002% and another cell equals 1.0003% the formula would still return "TRUE".

Apr 2nd, 2002, 08:51 AM	#2
wi_guy Board Regular Join Date: Mar 2002 Posts: 50	what about something like this, what lets you set the tolerance...... =IF(MAX(A1:A20)-MIN(A1:A20)<=0.005,"true","FALSE")

Apr 2nd, 2002, 08:58 AM	#3
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	consider one of the following =AND(SUM(A1:A20)>=0.2,SUM(A1:A20)<=0.200005,COUNTIF(A1:A20,0.01)>=18) =AND(SUM(A1:A20)>=0.2,SUM(A1:A20)<=0.200005) =IF((SUM(A1:A20)>=0.2)*(SUM(A1:A20)<=0.200005),"Yes within range","No outside range") [ This Message was edited by: Dave Patton on 2002-04-02 07:59 ]

Apr 2nd, 2002, 09:08 AM	#5
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Consider the array formula... {=AND(ROUND(A1:A20,2)=0.01)} The desired precision is governed by the 2nd ROUND argument (in red). Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. [ This Message was edited by: Mark W. on 2002-04-02 08:37 ]

Apr 2nd, 2002, 09:34 AM	#6
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	Probably a bit convoluted, but try: =AND(ROUND(MAX(A1:A20),LEN(B2)-2)<=ROUND(B1+B2,LEN(B2)-2),ROUND(MIN(A1:A20),LEN(B2)-2)>=ROUND(B1-B2,LEN(B2)-2)) where B1 houses the essential value (that is, 1%) and B2 the allowed difference or the tolerance, e.g., 0.0002%.

Apr 2nd, 2002, 09:37 AM	#7
wi_guy Board Regular Join Date: Mar 2002 Posts: 50	Aladin, a BIT convoluted????? I got a nosebleed when trying to read that one. Just kidding....nicely done as always