Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
nospaminlich
 
Posts: n/a
Default Checking ALL values in a range

I'm trying to create a formula which checks that a range of cells e.g. A2:F2
contain all the values 1,2,3,4,5,6 irrespective of which cell each number is
in.

Unfortunately, everything I've tried has failed so any help would be
appreciated.

Thanks
  #2   Report Post  
Trevor Shuttleworth
 
Posts: n/a
Default

One way, though not scaleable:

=IF(COUNTIF(A2:F2,1)0,1,0)+IF(COUNTIF(A2:F2,2)0, 1,0)+IF(COUNTIF(A2:F2,3)0,1,0)+IF(COUNTIF(A2:F2,4 )0,1,0)+IF(COUNTIF(A2:F2,5)0,1,0)+IF(COUNTIF(A2: F2,6)0,1,0)

Regards

Trevor


"nospaminlich" wrote in message
...
I'm trying to create a formula which checks that a range of cells e.g.
A2:F2
contain all the values 1,2,3,4,5,6 irrespective of which cell each number
is
in.

Unfortunately, everything I've tried has failed so any help would be
appreciated.

Thanks



  #3   Report Post  
nospaminlich
 
Posts: n/a
Default

Thanks a lot Trevor. I might have to go for this option but I'm really
looking for a more scaleable solution .
  #4   Report Post  
JE McGimpsey
 
Posts: n/a
Default

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=COUNT(MATCH(ROW(INDIRECT("1:6")),A2:F2,0))=6

In article ,
nospaminlich wrote:

I'm trying to create a formula which checks that a range of cells e.g. A2:F2
contain all the values 1,2,3,4,5,6 irrespective of which cell each number is
in.

Unfortunately, everything I've tried has failed so any help would be
appreciated.

  #5   Report Post  
nospaminlich
 
Posts: n/a
Default

That's brilliant, thanks a lot.


  #6   Report Post  
nospaminlich
 
Posts: n/a
Default

Just a thought...

I've adapted this so I can use it for columns as well as rows but is it
possible to set it so it covers a range of say A2:D6 e.g. a 4X4 area
containing the numbers 1 through 16?

Thanks a lot
  #7   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

=SUMPRODUCT((A2:A6<"")/COUNTIF(A2:D6,A2:D6&""),--ISNUMBER(MATCH(A2:D6,ConditionRange,0)))=COUNT(Con ditionRange)

where ConditionRange refers to a range housing the values whose
existence in A2:D6 you want to check.

Or

=SUMPRODUCT((A2:A6<"")/COUNTIF(A2:D6,A2:D6&""),--ISNUMBER(MATCH(A2:D6,{1,2,3,4,5,6},0)))=6

if you refer to the values of interest by means of a constant array like
{1,2,3,4,5,6}.

nospaminlich wrote:
Just a thought...

I've adapted this so I can use it for columns as well as rows but is it
possible to set it so it covers a range of say A2:D6 e.g. a 4X4 area
containing the numbers 1 through 16?

Thanks a lot

  #8   Report Post  
Dave Peterson
 
Posts: n/a
Default

A2:D5???

Another version:

=SUMPRODUCT(--(COUNTIF(A2:D5,ROW(INDIRECT("1:16")))0))



nospaminlich wrote:

Just a thought...

I've adapted this so I can use it for columns as well as rows but is it
possible to set it so it covers a range of say A2:D6 e.g. a 4X4 area
containing the numbers 1 through 16?

Thanks a lot


--

Dave Peterson
  #9   Report Post  
nospaminlich
 
Posts: n/a
Default

The SUMPRODUCT solutions work fine. Excellent, thanks a lot.
  #10   Report Post  
Jack Sons
 
Posts: n/a
Default

JE,

I can't figure out how it works exactly. Be so kind as to explain.
TIA

Jack Sons
The Netherlands

"JE McGimpsey" schreef in bericht
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=COUNT(MATCH(ROW(INDIRECT("1:6")),A2:F2,0))=6

In article ,
nospaminlich wrote:

I'm trying to create a formula which checks that a range of cells e.g.

A2:F2
contain all the values 1,2,3,4,5,6 irrespective of which cell each

number is
in.

Unfortunately, everything I've tried has failed so any help would be
appreciated.





  #11   Report Post  
JE McGimpsey
 
Posts: n/a
Default

ROW(INDIRECT("1:6"))

returns an array of numbers {1,2,3,4,5,6}

MATCH() compares these numbers to the values in A2:F2. If the number is
found, a numeric index is returned, if not, #N/A is returned.

COUNT() counts the number of numeric values returned and ignores the
#N/A errors.

The =6 checks to see that 6 numeric values were returned. If any #N/As
are returned, the comparison will fail.


In article ,
"Jack Sons" wrote:

I can't figure out how it works exactly. Be so kind as to explain.
TIA

Jack Sons
The Netherlands

"JE McGimpsey" schreef in bericht
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=COUNT(MATCH(ROW(INDIRECT("1:6")),A2:F2,0))=6

  #12   Report Post  
Jack Sons
 
Posts: n/a
Default

JE,

Thanks for your explanation.

What if the 6 elements of the range A2:F2 are not the neat 1 to 6 but, say,
2, 5, 234, a, -10 and dce? Must {2,5,234,"a",-10,"dce"} be put in the place
of ROW(INDIRECT("1:6"))?
I tried, I entered and array entered, but the formula in both cases results
in zero. Please help me further.

Jack.

"JE McGimpsey" schreef in bericht
...
ROW(INDIRECT("1:6"))

returns an array of numbers {1,2,3,4,5,6}

MATCH() compares these numbers to the values in A2:F2. If the number is
found, a numeric index is returned, if not, #N/A is returned.

COUNT() counts the number of numeric values returned and ignores the
#N/A errors.

The =6 checks to see that 6 numeric values were returned. If any #N/As
are returned, the comparison will fail.


In article ,
"Jack Sons" wrote:

I can't figure out how it works exactly. Be so kind as to explain.
TIA

Jack Sons
The Netherlands

"JE McGimpsey" schreef in bericht
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=COUNT(MATCH(ROW(INDIRECT("1:6")),A2:F2,0))=6



  #13   Report Post  
JE McGimpsey
 
Posts: n/a
Default

Substituting your array in place of ROW(INDIRECT("1:6")) and
array-entering the result works fine for me:

=COUNT(MATCH({2,5,234,"a",-10,"dce"},A2:F2,0))=6


In article ,
"Jack Sons" wrote:

What if the 6 elements of the range A2:F2 are not the neat 1 to 6 but, say,
2, 5, 234, a, -10 and dce? Must {2,5,234,"a",-10,"dce"} be put in the place
of ROW(INDIRECT("1:6"))?
I tried, I entered and array entered, but the formula in both cases results
in zero. Please help me further.

  #14   Report Post  
Jack Sons
 
Posts: n/a
Default

JE,

Right, I now saw what went wrong. I also noticed that it is not necessary to
array enter.

How should the formula be modified if it must work for al larger range than
only the cells with the correct entries. I tried to put in A2:F3 in stead of
A2:F2 but then it says FALSE. Why? You wrote "COUNT() counts the number of
numeric values returned and ignores the #N/A errors", so if all the correct
values are allready in A2:F2 why has the formula problems with other cells?

What I am looking for is a formula that shows TRUE if in the used range each
element of the {.....} part is found at least once. I tried
=COUNT(MATCH({2,5,234,"a",-10,"dce"},A2:F3,0))=6 with A3:F3 blank
but to no avail. It seems that MATCH() returns 6 times #N/A for the A3:F3
part of the range and nothing for the A2:F2 part.
Please help me out again.

Jack.


"JE McGimpsey" schreef in bericht
...
Substituting your array in place of ROW(INDIRECT("1:6")) and
array-entering the result works fine for me:

=COUNT(MATCH({2,5,234,"a",-10,"dce"},A2:F2,0))=6


In article ,
"Jack Sons" wrote:

What if the 6 elements of the range A2:F2 are not the neat 1 to 6 but,

say,
2, 5, 234, a, -10 and dce? Must {2,5,234,"a",-10,"dce"} be put in the

place
of ROW(INDIRECT("1:6"))?
I tried, I entered and array entered, but the formula in both cases

results
in zero. Please help me further.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to move Y-axis values when X range is -a to +b [email protected] Charts and Charting in Excel 4 February 1st 05 12:54 AM
Creating an EXCEL COUNTIF formula for a range of values Pat Walsh Excel Discussion (Misc queries) 5 January 21st 05 03:57 PM
Second serie doesn't use X-as values JackRnl Charts and Charting in Excel 1 January 20th 05 02:04 AM
Counting values within a Date Range Jana Excel Discussion (Misc queries) 7 December 9th 04 11:18 PM
Can you average data in 1 column based on a range of values in another? kman24 Excel Worksheet Functions 2 November 17th 04 03:09 PM


All times are GMT +1. The time now is 08:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"