Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bruce
 
Posts: n/a
Default Count number to reach a cumulative value

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   Report Post  
Domenic
 
Posts: n/a
Default

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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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   Report Post  
Domenic
 
Posts: n/a
Default

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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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   Report Post  
Domenic
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count the number of worksheets in a workbook Vincdc Excel Discussion (Misc queries) 7 January 18th 05 12:57 AM
Count number of Unique values Alan Excel Worksheet Functions 4 January 6th 05 09:05 PM
Count number of shaded cells Maddoktor Excel Discussion (Misc queries) 2 December 20th 04 09:35 PM
count number of occurences within a string Gabriel Excel Worksheet Functions 2 November 25th 04 05:17 PM
Count the number of words in a cell! Doom3 Excel Worksheet Functions 4 November 23rd 04 07:00 AM


All times are GMT +1. The time now is 12:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"