countif for a range

  • Thread starter Thread starter wc
  • Start date Start date
W

wc

I am trying to conduct the following operation where "X"
is whatever value is being evaluated "A4:A18". That is,
if current cell in "A4:A18" is say A6, I want to count it
if A6<=A6-$A$4. How do I do this?

=COUNTIF(A4:A18,"<=(X-$A$4)")

Thanks
 
Maybe you mean
=COUNTIF(A4:A18,"<="&(A4:A18-$A$4))
Click to expand...

No, that isn't it either. Let me try again. Take the
following data for column A: 6,3,2,7,5,8

Now in column B I want the number of values in column A
that are greater than the value in the 'current' cell.
For B1 the answer would be 2, since 7 and 8 are both
greater than 6. For B2 the answer would be 3 since 7,5,8
are all greater than 3. B3=3 since 7,5,8 are greater than
2. And on and on. Perhaps this makes more sense for
Matlab folks... but I can't do this in Excel...


a=[6 3 2 7 5 8]

for j=1:5
count=0
for i=j:5
if a(i+1)>a(j)
count=count+1;
end
end
b(j)=count;
end
 
WC,

=COUNTIF($A$1:$A$6,">=" & A1) - 1
copy down

Counts all of the values in the range A1 to A6
greater than or equal to A1 (for example) subtracts
1 because A1 will always be equal to A1 and i'm
assuming you don't want that counted.

Dan E

wc said:
Maybe you mean

=COUNTIF(A4:A18,"<="&(A4:A18-$A$4))
Click to expand...

No, that isn't it either. Let me try again. Take the
following data for column A: 6,3,2,7,5,8

Now in column B I want the number of values in column A
that are greater than the value in the 'current' cell.
For B1 the answer would be 2, since 7 and 8 are both
greater than 6. For B2 the answer would be 3 since 7,5,8
are all greater than 3. B3=3 since 7,5,8 are greater than
2. And on and on. Perhaps this makes more sense for
Matlab folks... but I can't do this in Excel...


a=[6 3 2 7 5 8]

for j=1:5
count=0
for i=j:5
if a(i+1)>a(j)
count=count+1;
end
end
b(j)=count;
end
Click to expand...
 
Thanks a bunch. Now I need to replace "A1" with some
simple math (say "A1+10") but it doesn't seem to work
{=COUNTIF($A$1:$A$6,">=" & (A1+10)) - 1} Any
suggestions? What does the & do?
 
=COUNTIF($A$1:$A$6,">=" & A1) - 1
copy down
Click to expand...
...

This isn't what the OP's MatLab code does. If it were, then If there were no
duplicates,

=COUNTIF($A$1:$A$6,">"&A1)

would be better. If there were duplicates, and if they should be counted, then
why shouldn't A1 itself be counted?
. . . Perhaps this makes more sense for
Matlab folks... but I can't do this in Excel...

a=[6 3 2 7 5 8]

for j=1:5
count=0
for i=j:5
if a(i+1)>a(j)
count=count+1;
end
end
b(j)=count;
end
Click to expand...
Click to expand...

j = 1, inner loop checks if [3 2 7 5 8] > 6, b(1) = 2
j = 2, inner loop checks if [2 7 5 8] > 3, b(2) = 3
j = 3, inner loop checks if [7 5 8] > 2, b(3) = 3
j = 4, inner loop checks if [5 8] > 7, b(4) = 1
j = 5, inner loop checks if 8 > 5, b(5) = 1

Given *strictly* greater than, in B1 enter the formula

=COUNTIF(A2:A$6,">"&B1)

Fill B1 down into B2:B5. This makes B1:B5 *exactly* the same as your MatLab
array b() if A1:A6 is exactly the same as your MatLab array a().
 
You are correct!
Sorry about that...

Dan E

Harlan Grove said:
=COUNTIF($A$1:$A$6,">=" & A1) - 1
copy down
Click to expand...
..

This isn't what the OP's MatLab code does. If it were, then If there were no
duplicates,

=COUNTIF($A$1:$A$6,">"&A1)

would be better. If there were duplicates, and if they should be counted, then
why shouldn't A1 itself be counted?
. . . Perhaps this makes more sense for
Matlab folks... but I can't do this in Excel...

a=[6 3 2 7 5 8]

for j=1:5
count=0
for i=j:5
if a(i+1)>a(j)
count=count+1;
end
end
b(j)=count;
end
Click to expand...
Click to expand...

j = 1, inner loop checks if [3 2 7 5 8] > 6, b(1) = 2
j = 2, inner loop checks if [2 7 5 8] > 3, b(2) = 3
j = 3, inner loop checks if [7 5 8] > 2, b(3) = 3
j = 4, inner loop checks if [5 8] > 7, b(4) = 1
j = 5, inner loop checks if 8 > 5, b(5) = 1

Given *strictly* greater than, in B1 enter the formula

=COUNTIF(A2:A$6,">"&B1)

Fill B1 down into B2:B5. This makes B1:B5 *exactly* the same as your MatLab
array b() if A1:A6 is exactly the same as your MatLab array a().

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.
Click to expand...
 
Back
Top