software

Hi, here's a challenging issue:

What formula could I copy for all cells in Column A so that will
automatically show quot;Noquot; quot;Yesquot; 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 quot;yesquot; than quot;noquot;
blank cell = for every cell in column b that = quot;@NAquot;
quot;@NAquot; is not a formula generated error, it's quot;hard codedquot; 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

Not sure if this is what you are looking for. This formual will print
a blank for any non-number value in column B. It will print quot;Noquot; for
any number that is less than the median or halfway point in the
distribution.

=IF(ISNUMBER(B2),IF(B2lt;MEDIAN($B$2:$B$10),quot;Noquot;,quot;Ye squot;),quot;quot;)

- John
www.JohnMichl.comHi, thanks very much.

That solution is very close to what I am looking for.

The Median should not be for all cells in Column B, but for a certain set of
numbers. Is there a way the Median could quot;resetquot; itself.

For clarity:
I have about 2000 rows.
Cells in Column B Count the number of identical items in Column C.
When the values in Column C changes, Cells in Column B start counting over
again.
For example, if I have 50 Apples in Column C, cells in Column B in show in
descending order quot;1, 2, 3, 4quot;.
Once the values in Column C changes to Oranges, Cells in Column B start
counting over again quot;1,2,3,4quot;

Is there a way so that the formula recognizes the series of numbers of
1,2,3,...50 as a set of numbers and quot;resetsquot; so that for for
quot;...50,1,2,3,4,1,2,3,4,5,6,1,2,3,4,5,6,7,1...quot; it recognizes the Median for
1-50, 1-4, 1-6,1-7, etc?). These series of numbers are in Column B,
descending, e.g.:

....
49
50
1
2
3
4
5
6
1
2
3
4
1
2
etc...

Thanks very much! I appreciate it.

SteveC

quot;John Michlquot; wrote:

gt; Not sure if this is what you are looking for. This formual will print
gt; a blank for any non-number value in column B. It will print quot;Noquot; for
gt; any number that is less than the median or halfway point in the
gt; distribution.
gt;
gt; =IF(ISNUMBER(B2),IF(B2lt;MEDIAN($B$2:$B$10),quot;Noquot;,quot;Ye squot;),quot;quot;)
gt;
gt; - John
gt; www.JohnMichl.com
gt;
gt;

Hi Steve

Try

=IF(B2=quot;@NAquot;,quot;quot;,IF(B2lt;SUMPRODUCT(--($C$2:$C$10=C2),--ISNUMBER($B$2:$B$10),$B$2:$B$10)
/COUNTIF($C$2:$C$10,C2),quot;Noquot;,quot;Yesquot;))
--
Regards

Roger GovierSteveC gt; wrote
gt; Hi, here's a challenging issue:
gt; What formula could I copy for all cells in Column A so that will
gt; automatically show quot;Noquot; quot;Yesquot; or a blank cell automatically.
gt; Note that:
gt; No = the bottom half of a series of numbers
gt; Yes = the top half of a series of numbers (for odd series, there are
gt; always 1 more quot;yesquot; than quot;noquot;
gt; blank cell = for every cell in column b that = quot;@NAquot;
gt; quot;@NAquot; is not a formula generated error, it's quot;hard codedquot; into the
gt; cell See below for an example spreadsheet.
gt; Thanks very much for any help!
gt; ColumnA ColumnB ColumnC
gt; No 1 Apples
gt; No 2 Apples
gt; Yes 3 Apples
gt; Yes 4 Apples
gt; Yes 5 Apples
gt; No 1 Oranges
gt; Yes 2 Oranges
gt; @NA Oranges
gt; No 1 Pears
gt; etcFirst 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 quot;Yesquot; and 30 quot;No.quot; (1,2,...,30
shows quot;Noquot; and 31,32,33,...60 shows quot;Yesquot;)

To be more specific:

In A 1202 I entered the formula:
=IF(B1202=quot;@NAquot;,quot;quot;,IF(B1202lt;SUMPRODUCT(--($C$9:$C$2000=C1202),--ISNUMBER($B$9:$B$2000),$B$9:$B$2000)/COUNTIF($C$9:$C$2000,C1202),quot;Noquot;,quot;Yesquot;))

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
No1Apples
No2Apples
No3Apples
No4Apples
No5Apples
No6Apples
No7Apples
No8Apples
No9Apples
No10Apples
No11Apples
No12Apples
No13Apples
No14Apples
No15Apples
No16Apples
No17Apples
No18Apples
No19Apples
No20Apples
No21Apples
No22Apples
No23Apples
No24Apples
No25Apples
No26Apples
No27Apples
Yes28Apples
Yes29Apples
Yes30Apples
Yes31Apples
Yes32Apples
Yes33Apples
Yes34Apples
Yes35Apples
Yes36Apples
Yes37Apples
Yes38Apples
Yes39Apples
Yes40Apples
Yes41Apples
Yes42Apples
Yes43Apples
Yes44Apples
Yes45Apples
Yes46Apples
Yes47Apples
Yes48Apples
Yes49Apples
Yes50Apples
Yes51Apples
Yes52Apples
Yes53Apples
Yes54Apples
Yes55Apples
Yes56Apples
Yes57Apples
Yes58Apples
Yes59Apples
Yes60Apples
@NAApples
@NAApples
@NAApples
@NAApples
@NAApples
@NAApples
@NAApples
quot;Roger Govierquot; wrote:

gt; Hi Steve
gt;
gt; Try
gt;
gt; =IF(B2=quot;@NAquot;,quot;quot;,IF(B2lt;SUMPRODUCT(--($C$2:$C$10=C2),--ISNUMBER($B$2:$B$10),$B$2:$B$10)
gt; /COUNTIF($C$2:$C$10,C2),quot;Noquot;,quot;Yesquot;))
gt;
gt;
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; SteveC gt; wrote
gt; gt; Hi, here's a challenging issue:
gt; gt; What formula could I copy for all cells in Column A so that will
gt; gt; automatically show quot;Noquot; quot;Yesquot; or a blank cell automatically.
gt; gt; Note that:
gt; gt; No = the bottom half of a series of numbers
gt; gt; Yes = the top half of a series of numbers (for odd series, there are
gt; gt; always 1 more quot;yesquot; than quot;noquot;
gt; gt; blank cell = for every cell in column b that = quot;@NAquot;
gt; gt; quot;@NAquot; is not a formula generated error, it's quot;hard codedquot; into the
gt; gt; cell See below for an example spreadsheet.
gt; gt; Thanks very much for any help!
gt; gt; ColumnA ColumnB ColumnC
gt; gt; No 1 Apples
gt; gt; No 2 Apples
gt; gt; Yes 3 Apples
gt; gt; Yes 4 Apples
gt; gt; Yes 5 Apples
gt; gt; No 1 Oranges
gt; gt; Yes 2 Oranges
gt; gt; @NA Oranges
gt; gt; No 1 Pears
gt; gt; etc
gt;
gt;
gt;
gt;
gt;

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=quot;@NAquot;,quot;quot;,IF(B1202lt;SUMPRODUCT(--($C$9:$C$2000=C1202),--ISNUMBER($B$9:$B$2000),$B$9:$B$2000)/SUMPRODUCT(--($C$9:$C$2000=C1202),--ISNUMBER($B$9:$B$2000)),quot;Noquot;,quot;Yesquot;))

--
Regards

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

and the original example of 1 Pear being quot;Noquot; would be incorrect
because the wording says there is always one more quot;Yesquot;
than quot;Noquot; when there are an odd number.

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

=IF(NOT(ISNUMBER(B2)),quot;quot;,IF(B2lt;=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)),quot;Noquot;,quot;Yesquot;))quot;Roger Govierquot; gt; wrote in message ...
gt; Hi Steve
gt;
gt; From your original data, I hadn't considered the scenario where Apples
gt; had a @NA value in column B.
gt; Try instead, the following amended formula
gt;
gt;
=IF(B1202=quot;@NAquot;,quot;quot;,IF(B1202lt;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)),quot;Noquot;,quot;Yesquot;))
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; SteveC gt; wrote
gt; gt; First of all, thanks so much to you both for your response. This
gt; gt; formula amost works.
gt; gt; This is what I get now, see below for an example. for example. If
gt; gt; the formula runs correctly, I'm supposed to get 30 quot;Yesquot; and 30
gt; gt; quot;No.quot; (1,2,...,30 shows quot;Noquot; and 31,32,33,...60 shows quot;Yesquot;)
gt; gt; To be more specific:
gt; gt; In A 1202 I entered the formula:
gt; gt; =IF(B1202=quot;@NAquot;,quot;quot;,IF(B1202lt;SUMPRODUCT(--($C$9:$C$2000=C1202),--ISNUMB
gt; gt;
gt; gt; E
gt; gt; ($B$9:$B$2000),$B$9:$B$2000)/COUNTIF($C$9:$C$2000,C1202),quot;Noquot;,quot;Yesquot;))
gt; gt; And I dragged this formula to A1268. Then, this is what I currently
gt; gt; see. I checked am I am pretty sure I didn't mess up the formula...
gt; gt; Thanks again, I really appreciate your response.
gt; gt; A B C
gt; gt; No 1 Apples
gt; gt; No 2 Apples
gt; gt; No 3 Apples
gt; gt; No 4 Apples
gt; gt; No 5 Apples
gt; gt; No 6 Apples
gt; gt; No 7 Apples
gt; gt; No 8 Apples
gt; gt; No 9 Apples
gt; gt; No 10 Apples
gt; gt; No 11 Apples
gt; gt; No 12 Apples
gt; gt; No 13 Apples
gt; gt; No 14 Apples
gt; gt; No 15 Apples
gt; gt; No 16 Apples
gt; gt; No 17 Apples
gt; gt; No 18 Apples
gt; gt; No 19 Apples
gt; gt; No 20 Apples
gt; gt; No 21 Apples
gt; gt; No 22 Apples
gt; gt; No 23 Apples
gt; gt; No 24 Apples
gt; gt; No 25 Apples
gt; gt; No 26 Apples
gt; gt; No 27 Apples
gt; gt; Yes 28 Apples
gt; gt; Yes 29 Apples
gt; gt; Yes 30 Apples
gt; gt; Yes 31 Apples
gt; gt; Yes 32 Apples
gt; gt; Yes 33 Apples
gt; gt; Yes 34 Apples
gt; gt; Yes 35 Apples
gt; gt; Yes 36 Apples
gt; gt; Yes 37 Apples
gt; gt; Yes 38 Apples
gt; gt; Yes 39 Apples
gt; gt; Yes 40 Apples
gt; gt; Yes 41 Apples
gt; gt; Yes 42 Apples
gt; gt; Yes 43 Apples
gt; gt; Yes 44 Apples
gt; gt; Yes 45 Apples
gt; gt; Yes 46 Apples
gt; gt; Yes 47 Apples
gt; gt; Yes 48 Apples
gt; gt; Yes 49 Apples
gt; gt; Yes 50 Apples
gt; gt; Yes 51 Apples
gt; gt; Yes 52 Apples
gt; gt; Yes 53 Apples
gt; gt; Yes 54 Apples
gt; gt; Yes 55 Apples
gt; gt; Yes 56 Apples
gt; gt; Yes 57 Apples
gt; gt; Yes 58 Apples
gt; gt; Yes 59 Apples
gt; gt; Yes 60 Apples
gt; gt; @NA Apples
gt; gt; @NA Apples
gt; gt; @NA Apples
gt; gt; @NA Apples
gt; gt; @NA Apples
gt; gt; @NA Apples
gt; gt; @NA Apples
gt; gt; quot;Roger Govierquot; wrote:
gt; gt;gt; Hi Steve
gt; gt;gt; Try
gt; gt;gt; =IF(B2=quot;@NAquot;,quot;quot;,IF(B2lt;SUMPRODUCT(--($C$2:$C$10=C2),--ISNUMBER($B$2:$B
gt; gt;gt; $10),$B$2:$B$10) /COUNTIF($C$2:$C$10,C2),quot;Noquot;,quot;Yesquot;))
gt; gt;gt; --
gt; gt;gt; Regards
gt; gt;gt; Roger Govier
gt; gt;gt; SteveC gt; wrote
gt; gt;gt;gt; Hi, here's a challenging issue:
gt; gt;gt;gt; What formula could I copy for all cells in Column A so that will
gt; gt;gt;gt; automatically show quot;Noquot; quot;Yesquot; or a blank cell automatically.
gt; gt;gt;gt; Note that:
gt; gt;gt;gt; No = the bottom half of a series of numbers
gt; gt;gt;gt; Yes = the top half of a series of numbers (for odd series, there are
gt; gt;gt;gt; always 1 more quot;yesquot; than quot;noquot;
gt; gt;gt;gt; blank cell = for every cell in column b that = quot;@NAquot;
gt; gt;gt;gt; quot;@NAquot; is not a formula generated error, it's quot;hard codedquot; into the
gt; gt;gt;gt; cell See below for an example spreadsheet.
gt; gt;gt;gt; Thanks very much for any help!
gt; gt;gt;gt; ColumnA ColumnB ColumnC
gt; gt;gt;gt; No 1 Apples
gt; gt;gt;gt; No 2 Apples
gt; gt;gt;gt; Yes 3 Apples
gt; gt;gt;gt; Yes 4 Apples
gt; gt;gt;gt; Yes 5 Apples
gt; gt;gt;gt; No 1 Oranges
gt; gt;gt;gt; Yes 2 Oranges
gt; gt;gt;gt; @NA Oranges
gt; gt;gt;gt; No 1 Pears
gt; gt;gt;gt; etc
gt;
gt;
gt;
gt;
It works great! Thanks so much. This will save me a tremendous amount of
time.

Thanks very much! it works great! It will save me a lot of time.

quot;Roger Govierquot; wrote:

gt; Hi Steve
gt;
gt; From your original data, I hadn't considered the scenario where Apples
gt; had a @NA value in column B.
gt; Try instead, the following amended formula
gt;
gt; =IF(B1202=quot;@NAquot;,quot;quot;,IF(B1202lt;SUMPRODUCT(--($C$9:$C$2000=C1202),--ISNUMBER($B$9:$B$2000),$B$9:$B$2000)/SUMPRODUCT(--($C$9:$C$2000=C1202),--ISNUMBER($B$9:$B$2000)),quot;Noquot;,quot;Yesquot;))
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; SteveC gt; wrote
gt; gt; First of all, thanks so much to you both for your response. This
gt; gt; formula amost works.
gt; gt; This is what I get now, see below for an example. for example. If
gt; gt; the formula runs correctly, I'm supposed to get 30 quot;Yesquot; and 30
gt; gt; quot;No.quot; (1,2,...,30 shows quot;Noquot; and 31,32,33,...60 shows quot;Yesquot;)
gt; gt; To be more specific:
gt; gt; In A 1202 I entered the formula:
gt; gt; =IF(B1202=quot;@NAquot;,quot;quot;,IF(B1202lt;SUMPRODUCT(--($C$9:$C$2000=C1202),--ISNUMB
gt; gt;
gt; gt; E
gt; gt; ($B$9:$B$2000),$B$9:$B$2000)/COUNTIF($C$9:$C$2000,C1202),quot;Noquot;,quot;Yesquot;))
gt; gt; And I dragged this formula to A1268. Then, this is what I currently
gt; gt; see. I checked am I am pretty sure I didn't mess up the formula...
gt; gt; Thanks again, I really appreciate your response.
gt; gt; A B C
gt; gt; No 1 Apples
gt; gt; No 2 Apples
gt; gt; No 3 Apples
gt; gt; No 4 Apples
gt; gt; No 5 Apples
gt; gt; No 6 Apples
gt; gt; No 7 Apples
gt; gt; No 8 Apples
gt; gt; No 9 Apples
gt; gt; No 10 Apples
gt; gt; No 11 Apples
gt; gt; No 12 Apples
gt; gt; No 13 Apples
gt; gt; No 14 Apples
gt; gt; No 15 Apples
gt; gt; No 16 Apples
gt; gt; No 17 Apples
gt; gt; No 18 Apples
gt; gt; No 19 Apples
gt; gt; No 20 Apples
gt; gt; No 21 Apples
gt; gt; No 22 Apples
gt; gt; No 23 Apples
gt; gt; No 24 Apples
gt; gt; No 25 Apples
gt; gt; No 26 Apples
gt; gt; No 27 Apples
gt; gt; Yes 28 Apples
gt; gt; Yes 29 Apples
gt; gt; Yes 30 Apples
gt; gt; Yes 31 Apples
gt; gt; Yes 32 Apples
gt; gt; Yes 33 Apples
gt; gt; Yes 34 Apples
gt; gt; Yes 35 Apples
gt; gt; Yes 36 Apples
gt; gt; Yes 37 Apples
gt; gt; Yes 38 Apples
gt; gt; Yes 39 Apples
gt; gt; Yes 40 Apples
gt; gt; Yes 41 Apples
gt; gt; Yes 42 Apples
gt; gt; Yes 43 Apples
gt; gt; Yes 44 Apples
gt; gt; Yes 45 Apples
gt; gt; Yes 46 Apples
gt; gt; Yes 47 Apples
gt; gt; Yes 48 Apples
gt; gt; Yes 49 Apples
gt; gt; Yes 50 Apples
gt; gt; Yes 51 Apples
gt; gt; Yes 52 Apples
gt; gt; Yes 53 Apples
gt; gt; Yes 54 Apples
gt; gt; Yes 55 Apples
gt; gt; Yes 56 Apples
gt; gt; Yes 57 Apples
gt; gt; Yes 58 Apples
gt; gt; Yes 59 Apples
gt; gt; Yes 60 Apples
gt; gt; @NA Apples
gt; gt; @NA Apples
gt; gt; @NA Apples
gt; gt; @NA Apples
gt; gt; @NA Apples
gt; gt; @NA Apples
gt; gt; @NA Apples
gt; gt; quot;Roger Govierquot; wrote:
gt; gt;gt; Hi Steve
gt; gt;gt; Try
gt; gt;gt; =IF(B2=quot;@NAquot;,quot;quot;,IF(B2lt;SUMPRODUCT(--($C$2:$C$10=C2),--ISNUMBER($B$2:$B
gt; gt;gt; $10),$B$2:$B$10) /COUNTIF($C$2:$C$10,C2),quot;Noquot;,quot;Yesquot;))
gt; gt;gt; --
gt; gt;gt; Regards
gt; gt;gt; Roger Govier
gt; gt;gt; SteveC gt; wrote
gt; gt;gt;gt; Hi, here's a challenging issue:
gt; gt;gt;gt; What formula could I copy for all cells in Column A so that will
gt; gt;gt;gt; automatically show quot;Noquot; quot;Yesquot; or a blank cell automatically.
gt; gt;gt;gt; Note that:
gt; gt;gt;gt; No = the bottom half of a series of numbers
gt; gt;gt;gt; Yes = the top half of a series of numbers (for odd series, there are
gt; gt;gt;gt; always 1 more quot;yesquot; than quot;noquot;
gt; gt;gt;gt; blank cell = for every cell in column b that = quot;@NAquot;
gt; gt;gt;gt; quot;@NAquot; is not a formula generated error, it's quot;hard codedquot; into the
gt; gt;gt;gt; cell See below for an example spreadsheet.
gt; gt;gt;gt; Thanks very much for any help!
gt; gt;gt;gt; ColumnA ColumnB ColumnC
gt; gt;gt;gt; No 1 Apples
gt; gt;gt;gt; No 2 Apples
gt; gt;gt;gt; Yes 3 Apples
gt; gt;gt;gt; Yes 4 Apples
gt; gt;gt;gt; Yes 5 Apples
gt; gt;gt;gt; No 1 Oranges
gt; gt;gt;gt; Yes 2 Oranges
gt; gt;gt;gt; @NA Oranges
gt; gt;gt;gt; No 1 Pears
gt; gt;gt;gt; etc
gt;
gt;
gt;
gt;
gt;

David, thanks for the quot;not(isnumber)quot; language... that will come in handy.

One thing though, when I use your formula I get 2 quot;Noquot; and 1 quot;Yes.quot; For
example:

No1Eggs
No2Eggs
Yes3Eggs

But it should read (2 Yes, 1 No):
No1Eggs
Yes2Eggs
Yes3Eggs

Any suggestions... You guys are great... thanks

Steve

quot;David McRitchiequot; wrote:

gt; Congratulations Roger.
gt;
gt; and the original example of 1 Pear being quot;Noquot; would be incorrect
gt; because the wording says there is always one more quot;Yesquot;
gt; than quot;Noquot; when there are an odd number.
gt;
gt; Rearranging would be better, but in any case instead of using =quot;@NAquot;
gt; it think you be testing if it is a number or not
gt;
gt; =IF(NOT(ISNUMBER(B2)),quot;quot;,IF(B2lt;=SUMPRODUCT(--($C$2:$C$2000=C2),--ISNUMBER($B$2:$B$2000),$B$2:$B$2000)/SUMPRODUCT(--($C$2:$C$2000=C2)
gt; ,--ISNUMBER($B$2:$B$2000)),quot;Noquot;,quot;Yesquot;))
gt;
gt;
gt;
gt;
gt;
gt; quot;Roger Govierquot; gt; wrote in message ...
gt; gt; Hi Steve
gt; gt;
gt; gt; From your original data, I hadn't considered the scenario where Apples
gt; gt; had a @NA value in column B.
gt; gt; Try instead, the following amended formula
gt; gt;
gt; gt;
gt; =IF(B1202=quot;@NAquot;,quot;quot;,IF(B1202lt;SUMPRODUCT(--($C$9:$C$2000=C1202),--ISNUMBER($B$9:$B$2000),$B$9:$B$2000)/SUMPRODUCT(--($C$9:$C$2000=C120
gt; 2),--ISNUMBER($B$9:$B$2000)),quot;Noquot;,quot;Yesquot;))
gt; gt;
gt; gt; --
gt; gt; Regards
gt; gt;
gt; gt; Roger Govier
gt; gt;
gt; gt;
gt; gt; SteveC gt; wrote
gt; gt; gt; First of all, thanks so much to you both for your response. This
gt; gt; gt; formula amost works.
gt; gt; gt; This is what I get now, see below for an example. for example. If
gt; gt; gt; the formula runs correctly, I'm supposed to get 30 quot;Yesquot; and 30
gt; gt; gt; quot;No.quot; (1,2,...,30 shows quot;Noquot; and 31,32,33,...60 shows quot;Yesquot;)
gt; gt; gt; To be more specific:
gt; gt; gt; In A 1202 I entered the formula:
gt; gt; gt; =IF(B1202=quot;@NAquot;,quot;quot;,IF(B1202lt;SUMPRODUCT(--($C$9:$C$2000=C1202),--ISNUMB
gt; gt; gt;
gt; gt; gt; E
gt; gt; gt; ($B$9:$B$2000),$B$9:$B$2000)/COUNTIF($C$9:$C$2000,C1202),quot;Noquot;,quot;Yesquot;))
gt; gt; gt; And I dragged this formula to A1268. Then, this is what I currently
gt; gt; gt; see. I checked am I am pretty sure I didn't mess up the formula...
gt; gt; gt; Thanks again, I really appreciate your response.
gt; gt; gt; A B C
gt; gt; gt; No 1 Apples
gt; gt; gt; No 2 Apples
gt; gt; gt; No 3 Apples
gt; gt; gt; No 4 Apples
gt; gt; gt; No 5 Apples
gt; gt; gt; No 6 Apples
gt; gt; gt; No 7 Apples
gt; gt; gt; No 8 Apples
gt; gt; gt; No 9 Apples
gt; gt; gt; No 10 Apples
gt; gt; gt; No 11 Apples
gt; gt; gt; No 12 Apples
gt; gt; gt; No 13 Apples
gt; gt; gt; No 14 Apples
gt; gt; gt; No 15 Apples
gt; gt; gt; No 16 Apples
gt; gt; gt; No 17 Apples
gt; gt; gt; No 18 Apples
gt; gt; gt; No 19 Apples
gt; gt; gt; No 20 Apples
gt; gt; gt; No 21 Apples
gt; gt; gt; No 22 Apples
gt; gt; gt; No 23 Apples
gt; gt; gt; No 24 Apples
gt; gt; gt; No 25 Apples
gt; gt; gt; No 26 Apples
gt; gt; gt; No 27 Apples
gt; gt; gt; Yes 28 Apples
gt; gt; gt; Yes 29 Apples
gt; gt; gt; Yes 30 Apples
gt; gt; gt; Yes 31 Apples
gt; gt; gt; Yes 32 Apples
gt; gt; gt; Yes 33 Apples
gt; gt; gt; Yes 34 Apples
gt; gt; gt; Yes 35 Apples
gt; gt; gt; Yes 36 Apples
gt; gt; gt; Yes 37 Apples
gt; gt; gt; Yes 38 Apples
gt; gt; gt; Yes 39 Apples
gt; gt; gt; Yes 40 Apples
gt; gt; gt; Yes 41 Apples
gt; gt; gt; Yes 42 Apples
gt; gt; gt; Yes 43 Apples
gt; gt; gt; Yes 44 Apples
gt; gt; gt; Yes 45 Apples
gt; gt; gt; Yes 46 Apples
gt; gt; gt; Yes 47 Apples
gt; gt; gt; Yes 48 Apples
gt; gt; gt; Yes 49 Apples
gt; gt; gt; Yes 50 Apples
gt; gt; gt; Yes 51 Apples
gt; gt; gt; Yes 52 Apples
gt; gt; gt; Yes 53 Apples
gt; gt; gt; Yes 54 Apples
gt; gt; gt; Yes 55 Apples
gt; gt; gt; Yes 56 Apples
gt; gt; gt; Yes 57 Apples
gt; gt; gt; Yes 58 Apples
gt; gt; gt; Yes 59 Apples
gt; gt; gt; Yes 60 Apples
gt; gt; gt; @NA Apples
gt; gt; gt; @NA Apples
gt; gt; gt; @NA Apples
gt; gt; gt; @NA Apples
gt; gt; gt; @NA Apples
gt; gt; gt; @NA Apples
gt; gt; gt; @NA Apples
gt; gt; gt; quot;Roger Govierquot; wrote:
gt; gt; gt;gt; Hi Steve
gt; gt; gt;gt; Try
gt; gt; gt;gt; =IF(B2=quot;@NAquot;,quot;quot;,IF(B2lt;SUMPRODUCT(--($C$2:$C$10=C2),--ISNUMBER($B$2:$B
gt; gt; gt;gt; $10),$B$2:$B$10) /COUNTIF($C$2:$C$10,C2),quot;Noquot;,quot;Yesquot;))
gt; gt; gt;gt; --
gt; gt; gt;gt; Regards
gt; gt; gt;gt; Roger Govier
gt; gt; gt;gt; SteveC gt; wrote
gt; gt; gt;gt;gt; Hi, here's a challenging issue:
gt; gt; gt;gt;gt; What formula could I copy for all cells in Column A so that will
gt; gt; gt;gt;gt; automatically show quot;Noquot; quot;Yesquot; or a blank cell automatically.
gt; gt; gt;gt;gt; Note that:
gt; gt; gt;gt;gt; No = the bottom half of a series of numbers
gt; gt; gt;gt;gt; Yes = the top half of a series of numbers (for odd series, there are
gt; gt; gt;gt;gt; always 1 more quot;yesquot; than quot;noquot;
gt; gt; gt;gt;gt; blank cell = for every cell in column b that = quot;@NAquot;
gt; gt; gt;gt;gt; quot;@NAquot; is not a formula generated error, it's quot;hard codedquot; into the
gt; gt; gt;gt;gt; cell See below for an example spreadsheet.
gt; gt; gt;gt;gt; Thanks very much for any help!
gt; gt; gt;gt;gt; ColumnA ColumnB ColumnC
gt; gt; gt;gt;gt; No 1 Apples
gt; gt; gt;gt;gt; No 2 Apples
gt; gt; gt;gt;gt; Yes 3 Apples
gt; gt; gt;gt;gt; Yes 4 Apples
gt; gt; gt;gt;gt; Yes 5 Apples
gt; gt; gt;gt;gt; No 1 Oranges
gt; gt; gt;gt;gt; Yes 2 Oranges
gt; gt; gt;gt;gt; @NA Oranges
gt; gt; gt;gt;gt; No 1 Pears
gt; gt; gt;gt;gt; etc
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt;