understanding what part of a formula represents
 

I have numbers in column F and I want to see if they are duplicated anywhere
in column B. I was given a formula that seems to work but I do not
understand part of the formula. The part I want to know what is represents
is =1
The formula is as follows:
=IF(COUNTIF($F$2:$F$20),B2)=1,"remains on report","fell off report")
What does greater then or equal to 1 mean?
--
mrs luke

understanding what part of a formula represents
 

First, there was an extra closing paren in your formula. It should have read:

=IF(COUNTIF($F$2:$F$20,B2)=1,"remains on report","fell off report")

And = is the symbol for "greater than or equal to"

So if the number of times the value in B2 shows up in F2:F20 is greater than or
equal to 1 (it appears at least one time), then return "remains on report". If
the value is 0 (it doesn't appear), then show "fell off report.

I think I would have used:

=IF(COUNTIF($F$2:$F$20,B2)0,"remains on report","fell off report")

But that's probably more of a personal preference.

Mrs Luke wrote:

I have numbers in column F and I want to see if they are duplicated anywhere
in column B. I was given a formula that seems to work but I do not
understand part of the formula. The part I want to know what is represents
is =1
The formula is as follows:
=IF(COUNTIF($F$2:$F$20),B2)=1,"remains on report","fell off report")
What does greater then or equal to 1 mean?
--
mrs luke

--

Dave Peterson

understanding what part of a formula represents
 

COUNTIF($F$2:$F$20,B2) will return the count of the criteria in the
referenced range. The criteria is B2 and the referenced range is F2:F20.

This count is being used as the logical test in the IF function. In plain
English:

If the count of B2 in the range F2:F20 is greater than or equal to 1 then
return the string "remains on report" otherwise return the string "fell off
report".

This can also be expressed as

=IF(COUNTIF($F$2:$F$20,B2),"remains on report","fell off report")

If the count is any number other than 0 then the logical test is TRUE and
returns the string "remains on report". If the count is 0 then the logical
test is FALSE and returns the string "fell off report".

--
Biff
Microsoft Excel MVP


"Mrs Luke" wrote in message
...
I have numbers in column F and I want to see if they are duplicated
anywhere
in column B. I was given a formula that seems to work but I do not
understand part of the formula. The part I want to know what is
represents
is =1
The formula is as follows:
=IF(COUNTIF($F$2:$F$20),B2)=1,"remains on report","fell off report")
What does greater then or equal to 1 mean?
--
mrs luke