| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP!
I have the 3 columns of data in columns "m", "n", and "o" (No headings, data
starts in row 1). There are 216 rows starting in row 1. From the example below, I will create in column "r", the heading FB2. Under the heading, I want to see each 3 digit number that corresponds to FB2, with out repeating the same 3 digit number. M N O p Q R 000 0.198407405 PO3 FB2 001 0.207502916 CV3 003 002 0.984589896 CV3 003 0.715903627 FB2 .... 253 0.874352341 FB2 253 .... 345 0.345343556 FB2 345 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
HELP!
Try this...
Assuming there are no empty cells in column M. The entries in column M are numbers and those with leading zeros have been formatted to display leading zeros. R1 = FB2 (or any other code from column O) Enter this array formula** in cell S1. This will return the count of unique numbers in column M that meet the criteria. =COUNT(1/FREQUENCY(IF(O1:O216=R1,M1:M216),M1:M216)) Enter this array formula** in cell R2: =IF(S10,MIN(IF(O1:O216=R1,M1:M216)),"") Enter this array formula** in R3 and copy down until you get blanks: =IF(ROWS(R$2:R3)<=S$1,MIN(IF((O$1:O$216=R$1)*(M$1: M$216R2),M$1:M$216)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bill" wrote in message ... I have the 3 columns of data in columns "m", "n", and "o" (No headings, data starts in row 1). There are 216 rows starting in row 1. From the example below, I will create in column "r", the heading FB2. Under the heading, I want to see each 3 digit number that corresponds to FB2, with out repeating the same 3 digit number. M N O p Q R 000 0.198407405 PO3 FB2 001 0.207502916 CV3 003 002 0.984589896 CV3 003 0.715903627 FB2 ... 253 0.874352341 FB2 253 ... 345 0.345343556 FB2 345 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
HELP!
{=COUNT(1/FREQUENCY(IF(O1:O216=R1,M1:M216),M1:M216))}
No matter what data poi nt is in R1, this formula returns 0. Then the second formula does not find anything, because s1=0 "T. Valko" wrote: Try this... Assuming there are no empty cells in column M. The entries in column M are numbers and those with leading zeros have been formatted to display leading zeros. R1 = FB2 (or any other code from column O) Enter this array formula** in cell S1. This will return the count of unique numbers in column M that meet the criteria. =COUNT(1/FREQUENCY(IF(O1:O216=R1,M1:M216),M1:M216)) Enter this array formula** in cell R2: =IF(S10,MIN(IF(O1:O216=R1,M1:M216)),"") Enter this array formula** in R3 and copy down until you get blanks: =IF(ROWS(R$2:R3)<=S$1,MIN(IF((O$1:O$216=R$1)*(M$1: M$216R2),M$1:M$216)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bill" wrote in message ... I have the 3 columns of data in columns "m", "n", and "o" (No headings, data starts in row 1). There are 216 rows starting in row 1. From the example below, I will create in column "r", the heading FB2. Under the heading, I want to see each 3 digit number that corresponds to FB2, with out repeating the same 3 digit number. M N O p Q R 000 0.198407405 PO3 FB2 001 0.207502916 CV3 003 002 0.984589896 CV3 003 0.715903627 FB2 ... 253 0.874352341 FB2 253 ... 345 0.345343556 FB2 345 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
HELP!
{=COUNT(1/FREQUENCY(IF(O1:O216=R1,M1:M216),M1:M216))}
Whether entered as an array or not, this formula returns 0. With this being 0, the second formula finds nothing because S1 is not greater than 0. I'm sure I missed something, but I just can't figure it out. "T. Valko" wrote: Try this... Assuming there are no empty cells in column M. The entries in column M are numbers and those with leading zeros have been formatted to display leading zeros. R1 = FB2 (or any other code from column O) Enter this array formula** in cell S1. This will return the count of unique numbers in column M that meet the criteria. =COUNT(1/FREQUENCY(IF(O1:O216=R1,M1:M216),M1:M216)) Enter this array formula** in cell R2: =IF(S10,MIN(IF(O1:O216=R1,M1:M216)),"") Enter this array formula** in R3 and copy down until you get blanks: =IF(ROWS(R$2:R3)<=S$1,MIN(IF((O$1:O$216=R$1)*(M$1: M$216R2),M$1:M$216)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bill" wrote in message ... I have the 3 columns of data in columns "m", "n", and "o" (No headings, data starts in row 1). There are 216 rows starting in row 1. From the example below, I will create in column "r", the heading FB2. Under the heading, I want to see each 3 digit number that corresponds to FB2, with out repeating the same 3 digit number. M N O p Q R 000 0.198407405 PO3 FB2 001 0.207502916 CV3 003 002 0.984589896 CV3 003 0.715903627 FB2 ... 253 0.874352341 FB2 253 ... 345 0.345343556 FB2 345 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
HELP!
That tells me that either column M doesn't contain numeric numbers or column
O may contain entries like FB2 but might also have unseen characters like spaces. It's possible that FB2 is actually FB2<space. In the sample data you postd, the first thing I think of when I see numbers with leading zeros is these are not really numeric numbers but are TEXT numbers. Try this formula in some cell: =COUNT(M1:M216) What result do you get? If you get 0 then your numbers are text. -- Biff Microsoft Excel MVP "Bill" wrote in message ... {=COUNT(1/FREQUENCY(IF(O1:O216=R1,M1:M216),M1:M216))} Whether entered as an array or not, this formula returns 0. With this being 0, the second formula finds nothing because S1 is not greater than 0. I'm sure I missed something, but I just can't figure it out. "T. Valko" wrote: Try this... Assuming there are no empty cells in column M. The entries in column M are numbers and those with leading zeros have been formatted to display leading zeros. R1 = FB2 (or any other code from column O) Enter this array formula** in cell S1. This will return the count of unique numbers in column M that meet the criteria. =COUNT(1/FREQUENCY(IF(O1:O216=R1,M1:M216),M1:M216)) Enter this array formula** in cell R2: =IF(S10,MIN(IF(O1:O216=R1,M1:M216)),"") Enter this array formula** in R3 and copy down until you get blanks: =IF(ROWS(R$2:R3)<=S$1,MIN(IF((O$1:O$216=R$1)*(M$1: M$216R2),M$1:M$216)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bill" wrote in message ... I have the 3 columns of data in columns "m", "n", and "o" (No headings, data starts in row 1). There are 216 rows starting in row 1. From the example below, I will create in column "r", the heading FB2. Under the heading, I want to see each 3 digit number that corresponds to FB2, with out repeating the same 3 digit number. M N O p Q R 000 0.198407405 PO3 FB2 001 0.207502916 CV3 003 002 0.984589896 CV3 003 0.715903627 FB2 ... 253 0.874352341 FB2 253 ... 345 0.345343556 FB2 345 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
HELP!
They were entered as text. So is column "O", when Iswitched them to numeric,
column N equals 216, but I can't get a value for the count in Column "O" "T. Valko" wrote: That tells me that either column M doesn't contain numeric numbers or column O may contain entries like FB2 but might also have unseen characters like spaces. It's possible that FB2 is actually FB2<space. In the sample data you postd, the first thing I think of when I see numbers with leading zeros is these are not really numeric numbers but are TEXT numbers. Try this formula in some cell: =COUNT(M1:M216) What result do you get? If you get 0 then your numbers are text. -- Biff Microsoft Excel MVP "Bill" wrote in message ... {=COUNT(1/FREQUENCY(IF(O1:O216=R1,M1:M216),M1:M216))} Whether entered as an array or not, this formula returns 0. With this being 0, the second formula finds nothing because S1 is not greater than 0. I'm sure I missed something, but I just can't figure it out. "T. Valko" wrote: Try this... Assuming there are no empty cells in column M. The entries in column M are numbers and those with leading zeros have been formatted to display leading zeros. R1 = FB2 (or any other code from column O) Enter this array formula** in cell S1. This will return the count of unique numbers in column M that meet the criteria. =COUNT(1/FREQUENCY(IF(O1:O216=R1,M1:M216),M1:M216)) Enter this array formula** in cell R2: =IF(S10,MIN(IF(O1:O216=R1,M1:M216)),"") Enter this array formula** in R3 and copy down until you get blanks: =IF(ROWS(R$2:R3)<=S$1,MIN(IF((O$1:O$216=R$1)*(M$1: M$216R2),M$1:M$216)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bill" wrote in message ... I have the 3 columns of data in columns "m", "n", and "o" (No headings, data starts in row 1). There are 216 rows starting in row 1. From the example below, I will create in column "r", the heading FB2. Under the heading, I want to see each 3 digit number that corresponds to FB2, with out repeating the same 3 digit number. M N O p Q R 000 0.198407405 PO3 FB2 001 0.207502916 CV3 003 002 0.984589896 CV3 003 0.715903627 FB2 ... 253 0.874352341 FB2 253 ... 345 0.345343556 FB2 345 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
HELP!
Try this...
Select an empty that has never been used or been formatted. It can be any empty cell. Copy that empty cell: EditCopy Select the range of numbers in M1:M216 Then, EditPaste SpecialAddOK That will usually convert text numbers to numeric numbers. -- Biff Microsoft Excel MVP "Bill" wrote in message ... They were entered as text. So is column "O", when Iswitched them to numeric, column N equals 216, but I can't get a value for the count in Column "O" "T. Valko" wrote: That tells me that either column M doesn't contain numeric numbers or column O may contain entries like FB2 but might also have unseen characters like spaces. It's possible that FB2 is actually FB2<space. In the sample data you postd, the first thing I think of when I see numbers with leading zeros is these are not really numeric numbers but are TEXT numbers. Try this formula in some cell: =COUNT(M1:M216) What result do you get? If you get 0 then your numbers are text. -- Biff Microsoft Excel MVP "Bill" wrote in message ... {=COUNT(1/FREQUENCY(IF(O1:O216=R1,M1:M216),M1:M216))} Whether entered as an array or not, this formula returns 0. With this being 0, the second formula finds nothing because S1 is not greater than 0. I'm sure I missed something, but I just can't figure it out. "T. Valko" wrote: Try this... Assuming there are no empty cells in column M. The entries in column M are numbers and those with leading zeros have been formatted to display leading zeros. R1 = FB2 (or any other code from column O) Enter this array formula** in cell S1. This will return the count of unique numbers in column M that meet the criteria. =COUNT(1/FREQUENCY(IF(O1:O216=R1,M1:M216),M1:M216)) Enter this array formula** in cell R2: =IF(S10,MIN(IF(O1:O216=R1,M1:M216)),"") Enter this array formula** in R3 and copy down until you get blanks: =IF(ROWS(R$2:R3)<=S$1,MIN(IF((O$1:O$216=R$1)*(M$1: M$216R2),M$1:M$216)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bill" wrote in message ... I have the 3 columns of data in columns "m", "n", and "o" (No headings, data starts in row 1). There are 216 rows starting in row 1. From the example below, I will create in column "r", the heading FB2. Under the heading, I want to see each 3 digit number that corresponds to FB2, with out repeating the same 3 digit number. M N O p Q R 000 0.198407405 PO3 FB2 001 0.207502916 CV3 003 002 0.984589896 CV3 003 0.715903627 FB2 ... 253 0.874352341 FB2 253 ... 345 0.345343556 FB2 345 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
