Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Understanding formula | Excel Worksheet Functions | |||
Taking out string part which represents date | Excel Worksheet Functions | |||
Understanding a formula | Excel Worksheet Functions | |||
Understanding a formula | Excel Worksheet Functions | |||
Understanding this formula | Excel Worksheet Functions |