Congratulations Roger.

and the original example of 1 Pear being "No" would be incorrect
because the wording says there is always one more "Yes"
than "No" when there are an odd number.

Rearranging would be better, but in any case instead of using ="@NA"
it think you be testing if it is a number or not

=IF(NOT(ISNUMBER(B2)),"",IF(B2<=SUMPRODUCT(--($C$2:$C$2000=C2),--ISNUMBER($B$2:$B$2000),$B$2:$B$2000)/SUMPRODUCT(--($C$2:$C$2000=C2)
,--ISNUMBER($B$2:$B$2000)),"No","Yes"))





"Roger Govier" wrote in message ...
Hi Steve

From your original data, I hadn't considered the scenario where Apples
had a @NA value in column B.
Try instead, the following amended formula


=IF(B1202="@NA","",IF(B1202<SUMPRODUCT(--($C$9:$C$2000=C1202),--ISNUMBER($B$9:$B$2000),$B$9:$B$2000)/SUMPRODUCT(--($C$9:$C$2000=C120
2),--ISNUMBER($B$9:$B$2000)),"No","Yes"))

--
Regards

Roger Govier


SteveC wrote
First of all, thanks so much to you both for your response. This
formula amost works.
This is what I get now, see below for an example. for example. If
the formula runs correctly, I'm supposed to get 30 "Yes" and 30
"No." (1,2,...,30 shows "No" and 31,32,33,...60 shows "Yes")
To be more specific:
In A 1202 I entered the formula:
=IF(B1202="@NA","",IF(B1202<SUMPRODUCT(--($C$9:$C$2000=C1202),--ISNUMB

E
($B$9:$B$2000),$B$9:$B$2000)/COUNTIF($C$9:$C$2000,C1202),"No","Yes"))
And I dragged this formula to A1268. Then, this is what I currently
see. I checked am I am pretty sure I didn't mess up the formula...
Thanks again, I really appreciate your response.
A B C
No 1 Apples
No 2 Apples
No 3 Apples
No 4 Apples
No 5 Apples
No 6 Apples
No 7 Apples
No 8 Apples
No 9 Apples
No 10 Apples
No 11 Apples
No 12 Apples
No 13 Apples
No 14 Apples
No 15 Apples
No 16 Apples
No 17 Apples
No 18 Apples
No 19 Apples
No 20 Apples
No 21 Apples
No 22 Apples
No 23 Apples
No 24 Apples
No 25 Apples
No 26 Apples
No 27 Apples
Yes 28 Apples
Yes 29 Apples
Yes 30 Apples
Yes 31 Apples
Yes 32 Apples
Yes 33 Apples
Yes 34 Apples
Yes 35 Apples
Yes 36 Apples
Yes 37 Apples
Yes 38 Apples
Yes 39 Apples
Yes 40 Apples
Yes 41 Apples
Yes 42 Apples
Yes 43 Apples
Yes 44 Apples
Yes 45 Apples
Yes 46 Apples
Yes 47 Apples
Yes 48 Apples
Yes 49 Apples
Yes 50 Apples
Yes 51 Apples
Yes 52 Apples
Yes 53 Apples
Yes 54 Apples
Yes 55 Apples
Yes 56 Apples
Yes 57 Apples
Yes 58 Apples
Yes 59 Apples
Yes 60 Apples
@NA Apples
@NA Apples
@NA Apples
@NA Apples
@NA Apples
@NA Apples
@NA Apples
"Roger Govier" wrote:
Hi Steve
Try
=IF(B2="@NA","",IF(B2<SUMPRODUCT(--($C$2:$C$10=C2),--ISNUMBER($B$2:$B
$10),$B$2:$B$10) /COUNTIF($C$2:$C$10,C2),"No","Yes"))
--
Regards
Roger Govier
SteveC wrote
Hi, here's a challenging issue:
What formula could I copy for all cells in Column A so that will
automatically show "No" "Yes" or a blank cell automatically.
Note that:
No = the bottom half of a series of numbers
Yes = the top half of a series of numbers (for odd series, there are
always 1 more "yes" than "no"
blank cell = for every cell in column b that = "@NA"
"@NA" is not a formula generated error, it's "hard coded" into the
cell See below for an example spreadsheet.
Thanks very much for any help!
ColumnA ColumnB ColumnC
No 1 Apples
No 2 Apples
Yes 3 Apples
Yes 4 Apples
Yes 5 Apples
No 1 Oranges
Yes 2 Oranges
@NA Oranges
No 1 Pears
etc