Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Range Limitation
Hello. Thanks for reading my question. My problem is directly related to
Excel 2003's column limitation of 256. Situation: I have the following array formula in a cell, which counts the number of numerical entries that occur three times in the range from A1:IV1 .... =SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)=3,1,0))/3. The formula is replicated downward for 1,000 rows. This works fine. I have several sets of these one thousand blocks of numbers (separated by blank rows), and therein comes my pain. The second block of cells begins at A2000. I now need to count two rows to see how many "three occurrences" there are in ( A1:IV1, A2000:IV2000........B2:IV2,A2001:IV2001......) . In other words, I want the formula to 'see' these two ranges as one, if possible. I cannot simply apply the formula to the second block of cell rows because, as an example, a number might occur "two times" in A1:IV1 and "one time" in A2000:IV2000 and would not get counted in the sum. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Range Limitation
Have you tried it like this? :
=SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)+COUNTIF($A2000: $IV2000,$A2000:$IV2000)=3,1,0))/ 3 Hope this helps. Pete On Dec 21, 7:55*am, Roger H. wrote: Hello. Thanks for reading my question. My problem is directly related to Excel 2003's column limitation of 256. Situation: I have the following array formula in a cell, which counts *the number of numerical entries that *occur three times in the range from A1:IV1 .... * =SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)=3,1,0))/3. The formula is replicated downward for 1,000 rows. This works fine. I have several sets of these one thousand blocks of numbers (separated by blank rows), and therein comes my pain. The second block of cells begins at A2000. I now need to count two rows to see how many "three occurrences" there are in ( A1:IV1, A2000:IV2000........B2:IV2,A2001:IV2001......) . In other words, I want the formula to 'see' these two ranges as one, if possible. I cannot simply apply the formula to the second block of cell rows because, as an example, a number might occur "two times" in A1:IV1 and "one time" in A2000:IV2000 and would not get counted in the sum. Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Range Limitation
Yes. I had tried that particular approach, Pete. But it did not count all the
valid occurrences in rows where there were only "one" count of a number occuring three times. I have all the rows conditionally formatted so that they will be easy to spot --- ( Formula is ) : =COUNTIF($A1:$IV1,A1)=3. Maybe I'll have to settle for an approximation with what formulas I have. Thank you. "Pete_UK" wrote: Have you tried it like this? : =SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)+COUNTIF($A2000: $IV2000,$A2000:$IV2000)=3,1,0))/ 3 Hope this helps. Pete On Dec 21, 7:55 am, Roger H. wrote: Hello. Thanks for reading my question. My problem is directly related to Excel 2003's column limitation of 256. Situation: I have the following array formula in a cell, which counts the number of numerical entries that occur three times in the range from A1:IV1 .... =SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)=3,1,0))/3. The formula is replicated downward for 1,000 rows. This works fine. I have several sets of these one thousand blocks of numbers (separated by blank rows), and therein comes my pain. The second block of cells begins at A2000. I now need to count two rows to see how many "three occurrences" there are in ( A1:IV1, A2000:IV2000........B2:IV2,A2001:IV2001......) . In other words, I want the formula to 'see' these two ranges as one, if possible. I cannot simply apply the formula to the second block of cell rows because, as an example, a number might occur "two times" in A1:IV1 and "one time" in A2000:IV2000 and would not get counted in the sum. Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Range Limitation
You're welcome, Roger.
Pete On Dec 21, 11:22*am, Roger H. wrote: Yes. I had tried that particular approach, Pete. But it did not count all the valid occurrences in rows where there were only "one" count of a number occuring three times. I have all the rows conditionally formatted so that they will be easy to spot --- ( Formula is ) : =COUNTIF($A1:$IV1,A1)=3.. Maybe I'll have to settle for an approximation with what formulas I have. Thank you. "Pete_UK" wrote: Have you tried it like this? : =SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)+COUNTIF($A2000: $IV2000,$A2000:$IV2000)=3*,1,0))/ 3 Hope this helps. Pete On Dec 21, 7:55 am, Roger H. wrote: Hello. Thanks for reading my question. My problem is directly related to Excel 2003's column limitation of 256. Situation: I have the following array formula in a cell, which counts *the number of numerical entries that *occur three times in the range from A1:IV1 .... * =SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)=3,1,0))/3. The formula is replicated downward for 1,000 rows. This works fine. I have several sets of these one thousand blocks of numbers (separated by blank rows), and therein comes my pain. The second block of cells begins at A2000. I now need to count two rows to see how many "three occurrences" there are in ( A1:IV1, A2000:IV2000........B2:IV2,A2001:IV2001......) . In other words, I want the formula to 'see' these two ranges as one, if possible. I cannot simply apply the formula to the second block of cell rows because, as an example, a number might occur "two times" in A1:IV1 and "one time" in A2000:IV2000 and would not get counted in the sum. Thank you.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Range Limitation
Not sure I understand what you want but somehting like this might work:
=SUMPRODUCT(--(FREQUENCY((A1:G1,A5:G5),(A1:G1,A5:G5))=3)) -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... You're welcome, Roger. Pete On Dec 21, 11:22 am, Roger H. wrote: Yes. I had tried that particular approach, Pete. But it did not count all the valid occurrences in rows where there were only "one" count of a number occuring three times. I have all the rows conditionally formatted so that they will be easy to spot --- ( Formula is ) : =COUNTIF($A1:$IV1,A1)=3. Maybe I'll have to settle for an approximation with what formulas I have. Thank you. "Pete_UK" wrote: Have you tried it like this? : =SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)+COUNTIF($A2000: $IV2000,$A2000:$IV2000)=3*,1,0))/ 3 Hope this helps. Pete On Dec 21, 7:55 am, Roger H. wrote: Hello. Thanks for reading my question. My problem is directly related to Excel 2003's column limitation of 256. Situation: I have the following array formula in a cell, which counts the number of numerical entries that occur three times in the range from A1:IV1 .... =SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)=3,1,0))/3. The formula is replicated downward for 1,000 rows. This works fine. I have several sets of these one thousand blocks of numbers (separated by blank rows), and therein comes my pain. The second block of cells begins at A2000. I now need to count two rows to see how many "three occurrences" there are in ( A1:IV1, A2000:IV2000........B2:IV2,A2001:IV2001......) . In other words, I want the formula to 'see' these two ranges as one, if possible. I cannot simply apply the formula to the second block of cell rows because, as an example, a number might occur "two times" in A1:IV1 and "one time" in A2000:IV2000 and would not get counted in the sum. Thank you.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Range Limitation
After readng your post again, I think the formula I suggested will do what
you want. =SUMPRODUCT(--(FREQUENCY((A1:G1,A5:G5),(A1:G1,A5:G5))=3)) It treats (A1:G1,A5:G5) as a single range. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Not sure I understand what you want but somehting like this might work: =SUMPRODUCT(--(FREQUENCY((A1:G1,A5:G5),(A1:G1,A5:G5))=3)) -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... You're welcome, Roger. Pete On Dec 21, 11:22 am, Roger H. wrote: Yes. I had tried that particular approach, Pete. But it did not count all the valid occurrences in rows where there were only "one" count of a number occuring three times. I have all the rows conditionally formatted so that they will be easy to spot --- ( Formula is ) : =COUNTIF($A1:$IV1,A1)=3. Maybe I'll have to settle for an approximation with what formulas I have. Thank you. "Pete_UK" wrote: Have you tried it like this? : =SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)+COUNTIF($A2000: $IV2000,$A2000:$IV2000)=3*,1,0))/ 3 Hope this helps. Pete On Dec 21, 7:55 am, Roger H. wrote: Hello. Thanks for reading my question. My problem is directly related to Excel 2003's column limitation of 256. Situation: I have the following array formula in a cell, which counts the number of numerical entries that occur three times in the range from A1:IV1 .... =SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)=3,1,0))/3. The formula is replicated downward for 1,000 rows. This works fine. I have several sets of these one thousand blocks of numbers (separated by blank rows), and therein comes my pain. The second block of cells begins at A2000. I now need to count two rows to see how many "three occurrences" there are in ( A1:IV1, A2000:IV2000........B2:IV2,A2001:IV2001......) . In other words, I want the formula to 'see' these two ranges as one, if possible. I cannot simply apply the formula to the second block of cell rows because, as an example, a number might occur "two times" in A1:IV1 and "one time" in A2000:IV2000 and would not get counted in the sum. Thank you.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Range Limitation
Yes. It worked. I froze the column where I had your formula and and panned
the rest of the spreadsheet past it with the right arrow. Each formatted "three occurrence" number lined up with where the formula was saying there was an occurrence. Thanks again to the two of you gentlemen! ( My spreadsheet is beautiful again ) "T. Valko" wrote: After readng your post again, I think the formula I suggested will do what you want. =SUMPRODUCT(--(FREQUENCY((A1:G1,A5:G5),(A1:G1,A5:G5))=3)) It treats (A1:G1,A5:G5) as a single range. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Not sure I understand what you want but somehting like this might work: =SUMPRODUCT(--(FREQUENCY((A1:G1,A5:G5),(A1:G1,A5:G5))=3)) -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... You're welcome, Roger. Pete On Dec 21, 11:22 am, Roger H. wrote: Yes. I had tried that particular approach, Pete. But it did not count all the valid occurrences in rows where there were only "one" count of a number occuring three times. I have all the rows conditionally formatted so that they will be easy to spot --- ( Formula is ) : =COUNTIF($A1:$IV1,A1)=3. Maybe I'll have to settle for an approximation with what formulas I have. Thank you. "Pete_UK" wrote: Have you tried it like this? : =SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)+COUNTIF($A2000: $IV2000,$A2000:$IV2000)=3Â*,1,0))/ 3 Hope this helps. Pete On Dec 21, 7:55 am, Roger H. wrote: Hello. Thanks for reading my question. My problem is directly related to Excel 2003's column limitation of 256. Situation: I have the following array formula in a cell, which counts the number of numerical entries that occur three times in the range from A1:IV1 .... =SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)=3,1,0))/3. The formula is replicated downward for 1,000 rows. This works fine. I have several sets of these one thousand blocks of numbers (separated by blank rows), and therein comes my pain. The second block of cells begins at A2000. I now need to count two rows to see how many "three occurrences" there are in ( A1:IV1, A2000:IV2000........B2:IV2,A2001:IV2001......) . In other words, I want the formula to 'see' these two ranges as one, if possible. I cannot simply apply the formula to the second block of cell rows because, as an example, a number might occur "two times" in A1:IV1 and "one time" in A2000:IV2000 and would not get counted in the sum. Thank you.- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Range Limitation
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Roger H." wrote in message ... Yes. It worked. I froze the column where I had your formula and and panned the rest of the spreadsheet past it with the right arrow. Each formatted "three occurrence" number lined up with where the formula was saying there was an occurrence. Thanks again to the two of you gentlemen! ( My spreadsheet is beautiful again ) "T. Valko" wrote: After readng your post again, I think the formula I suggested will do what you want. =SUMPRODUCT(--(FREQUENCY((A1:G1,A5:G5),(A1:G1,A5:G5))=3)) It treats (A1:G1,A5:G5) as a single range. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Not sure I understand what you want but somehting like this might work: =SUMPRODUCT(--(FREQUENCY((A1:G1,A5:G5),(A1:G1,A5:G5))=3)) -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... You're welcome, Roger. Pete On Dec 21, 11:22 am, Roger H. wrote: Yes. I had tried that particular approach, Pete. But it did not count all the valid occurrences in rows where there were only "one" count of a number occuring three times. I have all the rows conditionally formatted so that they will be easy to spot --- ( Formula is ) : =COUNTIF($A1:$IV1,A1)=3. Maybe I'll have to settle for an approximation with what formulas I have. Thank you. "Pete_UK" wrote: Have you tried it like this? : =SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)+COUNTIF($A2000: $IV2000,$A2000:$IV2000)=3*,1,0))/ 3 Hope this helps. Pete On Dec 21, 7:55 am, Roger H. wrote: Hello. Thanks for reading my question. My problem is directly related to Excel 2003's column limitation of 256. Situation: I have the following array formula in a cell, which counts the number of numerical entries that occur three times in the range from A1:IV1 .... =SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)=3,1,0))/3. The formula is replicated downward for 1,000 rows. This works fine. I have several sets of these one thousand blocks of numbers (separated by blank rows), and therein comes my pain. The second block of cells begins at A2000. I now need to count two rows to see how many "three occurrences" there are in ( A1:IV1, A2000:IV2000........B2:IV2,A2001:IV2001......) . In other words, I want the formula to 'see' these two ranges as one, if possible. I cannot simply apply the formula to the second block of cell rows because, as an example, a number might occur "two times" in A1:IV1 and "one time" in A2000:IV2000 and would not get counted in the sum. Thank you.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is the limitation of formula memory for Office XP - Excel XP? | Excel Discussion (Misc queries) | |||
Formula Cell color limitation | Excel Discussion (Misc queries) | |||
Formula Req'd - Autofilter limitation workaround | Excel Worksheet Functions | |||
Bypassing the 128MB formula memory limitation in 2002? | Excel Discussion (Misc queries) | |||
Excel Limitation | Excel Worksheet Functions |