| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Output in Column A based on Input in Column B
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Output in Column A based on Input in Column B
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","Ye s"),"") - John www.JohnMichl.com |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Output in Column A based on Input in Column B
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 "John Michl" wrote: 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","Ye s"),"") - John www.JohnMichl.com |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Output in Column A based on Input in Column B
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Output in Column A based on Input in Column B
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" 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Output in Column A based on Input in Column B
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")) -- 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Output in Column A based on Input in Column B
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Thanks
It works great! Thanks so much. This will save me a tremendous amount of
time. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Output in Column A based on Input in Column B
Thanks very much! it works great! It will save me a lot of time.
"Roger Govier" wrote: 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")) -- 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Output in Column A based on Input in Column B
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 "David McRitchie" wrote: 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Output in Column A based on Input in Column B
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| move contents of column C based on criteria related to column A | Excel Discussion (Misc queries) | |||
| IF/AND/OR/DATEIF Issue...sorry...long post... | Excel Worksheet Functions | |||
| Row filtering based on input box entry (column heading) | Excel Worksheet Functions | |||
| Hide column based on data value | Excel Discussion (Misc queries) | |||
| Putting text in a column based on variable text from another colum | Excel Discussion (Misc queries) | |||
Linear Mode
