Find month when criteria has been met (sum of project cost)

[chet645]

All, wondering if someone can help me with a formula. I need a formula that will calculate the month when total project cost has been spent (e.g. 500 in the table below). In the table below, the correct month is 5.

Month

0​

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

Project Cost​

​

100​

100​

100​

100​

100​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

Total Project Cost​

500​

​

​

​

​

​

​

​

​

​

​

​

Month of Completion​

5​

​

​

​

​

​

​

​

​

​

​

<tbody>
</tbody>

Thank you in advance for your help.

Chet

 

[Giordano Bruno]

Thanks Peter. Curiouser and curiouser. I tested my workbook on two other computers and each time, when it loads, both formulas show as arrays. When I change one to a normal formula, it doesn't work. When I put the same thumb drive back into my desktop computer, the first formula shows as normal (as it was saved). When I press F2 and Enter, it enters as a normal formula and gives the correct results. There are clearly dark forces at work here!

 

[Peter_SSs]

There are clearly dark forces at work here!

It seems that way!

 

[chet645]

See if this is what you want. I have added a few examples for checking.
Formula in B5 copied across.

Cost Spent

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Month	0	1	2	3	4	5	6	7	8	9	10	11
2	Project Cost		100	100	100	100	100	50	200	100
3
4	Total Project Cost	500	150	1000	780
5	Month of Completion	5	2		8

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:156px;"><col style="width:40px;"><col style="width:40px;"><col style="width:47px;"><col style="width:40px;"><col style="width:40px;"><col style="width:40px;"><col style="width:33px;"><col style="width:40px;"><col style="width:40px;"><col style="width:26px;"><col style="width:33px;"><col style="width:33px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B5 =IFERROR(MATCH(TRUE,INDEX(SUMIF(OFFSET($C$2,,,1,COLUMN($C2:$M2)-COLUMN($C2)+1),"<>")>=B4,,0),0),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you very much, Peter. This is a beautiful solution. Quick follow up question. My post was a simplified version of a more complex spreadsheet. The month row in my actual model is at the very top of the worksheet (70+ rows above), and I have given it a named range "INDEX_MONTH." If I wanted to modify your formula to reference the named range, is there an easy way to do it? Also, one other clarification, the month # in my spreadsheet is actually a month, not a month #. I am somewhat of a novice, but I believe your formula returns a column #, and I need it to pull an actual date? Again, I really appreciate your help.

Regards,

Chet

 

[Peter_SSs]

Try this change for the B5 formula in my layout.

=IFERROR(INDEX(INDEX_MONTH,MATCH(TRUE,INDEX(SUMIF(OFFSET($[size=+1]B[/size]$2,,,1,COLUMN($C2:$M2)-COLUMN($C2)+1),"<>")>=B4,,0),0)),"")

The red [size=+1]B[/size] should be whatever is the first column in your INDEX_MONTH named range. So if that named range started in column C then make that red column ref a C

 

[chet645]

Try this change for the B5 formula in my layout.

=IFERROR(INDEX(INDEX_MONTH,MATCH(TRUE,INDEX(SUMIF(OFFSET($[SIZE=+1]B[/SIZE]$2,,,1,COLUMN($C2:$M2)-COLUMN($C2)+1),"<>")>=B4,,0),0)),"")

The red [SIZE=+1]B[/SIZE] should be whatever is the first column in your INDEX_MONTH named range. So if that named range started in column C then make that red column ref a C

Once again. Thanks, Peter! I will give it a try. Really appreciate your help.

Kindest Regards,
Chet

 

[Tom.Jones]

There are clearly dark forces at work here!

It seems that way!

In Office 365 Insider you no longer need to enter a matrix formula with CSE. A simple ENTER is enough.

 

[Giordano Bruno]

Hi Tom. Thanks so much for that. I'm very grateful for your post. Yes, that's the difference. I shall clearly have to be careful how I use this computer if I ever distribute workbooks. Nice to know that neither I nor my computer are going mad.

 

[Peter_SSs]

In Office 365 Insider you no longer need to enter a matrix formula with CSE. A simple ENTER is enough.

Ah, I'm not in the Insiders program and thought the simple Enter only applied to the 'new' array formulas being released there.