Thanks:  0
Likes:  0

1. 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".

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

3. 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 ]

4. 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.

5. 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 ]

6. 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%.

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

Just kidding....nicely done as always

8. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•