MATCH??

mcarter973

Board Regular
Joined
Mar 24, 2002
Messages
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".
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
what about something like this, what lets you set the tolerance...... =IF(MAX(A1:A20)-MIN(A1:A20)<=0.005,"true","FALSE")
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Consider the array formula...

{=AND(ROUND(A1:A20,<font color=red>2</font>)=0.01)}

The desired precision is governed by the 2nd ROUND argument (in<font color=red>red</font>).

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
 
Upvote 0
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%.
 
Upvote 0
Aladin, a BIT convoluted????? I got a nosebleed when trying to read that one.

Just kidding....nicely done as always
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top