Output in Column A based on Input in Column B

[SteveC]

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

 

[John Michl]

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 "No" for
any number that is less than the median or halfway point in the
distribution.

=IF(ISNUMBER(B2),IF(B2<MEDIAN($B$2:$B$10),"No","Yes"),"")

- John
www.JohnMichl.com

 

[SteveC]

Hi, 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 "reset" 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 "1, 2, 3, 4".
Once the values in Column C changes to Oranges, Cells in Column B start
counting over again "1,2,3,4"

Is there a way so that the formula recognizes the series of numbers of
1,2,3,...50 as a set of numbers and "resets" so that for for
"...50,1,2,3,4,1,2,3,4,5,6,1,2,3,4,5,6,7,1..." 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

 

[Roger Govier]

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"))

 

[SteveC]

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),--ISNUMBER($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]

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=C1202),--ISNUMBER($B$9:$B$2000)),"No","Yes"))

 

[David McRitchie]

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"))

 

[SteveC]

It works great! Thanks so much. This will save me a tremendous amount of
time.

 

[SteveC]

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

 

[SteveC]

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

One thing though, when I use your formula I get 2 "No" and 1 "Yes." For
example:

No 1 Eggs
No 2 Eggs
Yes 3 Eggs

But it should read (2 Yes, 1 No):
No 1 Eggs
Yes 2 Eggs
Yes 3 Eggs

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

Steve

 

[SteveC]

Please disregard my follow up question.

I should have noticed it was the result of only adding a "=" to the
"<SUMPRODUCT" so that it reads "<=SUMPRODUCT." Thanks very much.