Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
How can I count the number of months it takes to reach a total of 100,000.
This would in a row count fashion from left to right. Eg. Cells A1:A7 contain sales by month. Cell A8 has a count of the number of months where the cumulative sum is equal to or greater than 100,000. So; 10000, 10000, 40000, 35000, 10000, 10000, 10000 The count would be 5 Also if 0, 0, 0, 50000, 50000, 40000,40000, 40000 The count would be 2 as it ignore 0's to the left of the first value. Bruce |
#2
![]() |
|||
|
|||
![]()
Try the following array formula...
=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,,,SMALL(IF(A1:A7< 0,ROW(A1:A7)-CELL("row ",A1)+1),ROW(INDIRECT("1:"&COUNTIF(A1:A7,"0"))))) )=100000,0) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , "Bruce" wrote: How can I count the number of months it takes to reach a total of 100,000. This would in a row count fashion from left to right. Eg. Cells A1:A7 contain sales by month. Cell A8 has a count of the number of months where the cumulative sum is equal to or greater than 100,000. So; 10000, 10000, 40000, 35000, 10000, 10000, 10000 The count would be 5 Also if 0, 0, 0, 50000, 50000, 40000,40000, 40000 The count would be 2 as it ignore 0's to the left of the first value. Bruce |
#3
![]() |
|||
|
|||
![]()
I think, confirmed with control+shift+enter...
=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A7)-CELL("Row",A1)+1))=100000,0) would suffice. Domenic wrote: Try the following array formula... =MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,,,SMALL(IF(A1:A7< 0,ROW(A1:A7)-CELL("row ",A1)+1),ROW(INDIRECT("1:"&COUNTIF(A1:A7,"0"))))) )=100000,0) ...confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , "Bruce" wrote: How can I count the number of months it takes to reach a total of 100,000. This would in a row count fashion from left to right. Eg. Cells A1:A7 contain sales by month. Cell A8 has a count of the number of months where the cumulative sum is equal to or greater than 100,000. So; 10000, 10000, 40000, 35000, 10000, 10000, 10000 The count would be 5 Also if 0, 0, 0, 50000, 50000, 40000,40000, 40000 The count would be 2 as it ignore 0's to the left of the first value. Bruce |
#4
![]() |
|||
|
|||
![]()
Hi Aladin!
Actually, the OP asked to ignore zero values. So I think my formula may be required. Although, while the OP did say that zero values prior to the first non-zero value should be ignored, it didn't say whether subsequent zero values should be ignored as well. So maybe my formula may not be appropriate. :) In article , Aladin Akyurek wrote: I think, confirmed with control+shift+enter... =MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A7)-CELL("Row",A1)+1))=100000,0) would suffice. |
#5
![]() |
|||
|
|||
![]()
I see I overlooked that...
Still less costly, therefore worth posting: Not to count empty or zero-valued cells before the first positive value in an otherwise numeric range: =MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A7)-CELL("Row",A1)+1))=100000,0)-MATCH(TRUE,A1:A70,0)+1 or, if needed, to exlcude text values: =MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A7)-CELL("Row",A1)+1))=100000,0)-MATCH(1,ISNUMBER(A1:A7)*(A1:A70),0)+1 The formulas still need to be confirmed with control+shift+enter. Domenic wrote: Hi Aladin! Actually, the OP asked to ignore zero values. So I think my formula may be required. Although, while the OP did say that zero values prior to the first non-zero value should be ignored, it didn't say whether subsequent zero values should be ignored as well. So maybe my formula may not be appropriate. :) In article , Aladin Akyurek wrote: I think, confirmed with control+shift+enter... =MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A7 )-CELL("Row",A1)+1))=100000,0) would suffice. |
#6
![]() |
|||
|
|||
![]()
Nice! :)
In article , Aladin Akyurek wrote: I see I overlooked that... Still less costly, therefore worth posting: Not to count empty or zero-valued cells before the first positive value in an otherwise numeric range: =MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A7)-CELL("Row",A1)+1))=100000,0)- MATCH(TRUE,A1:A70,0)+1 or, if needed, to exlcude text values: =MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A7)-CELL("Row",A1)+1))=100000,0)- MATCH(1,ISNUMBER(A1:A7)*(A1:A70),0)+1 The formulas still need to be confirmed with control+shift+enter. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Count the number of worksheets in a workbook | Excel Discussion (Misc queries) | |||
Count number of Unique values | Excel Worksheet Functions | |||
Count number of shaded cells | Excel Discussion (Misc queries) | |||
count number of occurences within a string | Excel Worksheet Functions | |||
Count the number of words in a cell! | Excel Worksheet Functions |