Lookup tied to a maximum sum value

[SLARRA]

I have a table of expected outputs by calendar day. I want to specify a target number of outputs (user entry, cell B1), then for any given start date, calculate the end date whose elapsed period (given that start date) will yield at least that target amount of outputs.

Example follows - I need a formula (prefer not to use VBA) that will calculate the stop dates, shown in red in Column E (note: for the sake of this example, I manually determined the appropropriate stop dates an inseted them in Column E).

Excel Workbook
	A	B	C	D	E	F
1	Target output:	4	Units
2
3
4	Expected Output	( Units by Date)		Date Range	to Achieve	Target Output
5
6	Date	Units		StartDate	StopDate	Units Yield
7	1/1/2012	0		1/1/2012	1/7/2012	4
8	1/2/2012	0		1/2/2012	1/7/2012	4
9	1/3/2012	0		1/3/2012	1/7/2012	4
10	1/4/2012	1		1/4/2012	1/7/2012	4
11	1/5/2012	0		1/5/2012	1/8/2012	5
12	1/6/2012	1		1/6/2012	1/8/2012	5
13	1/7/2012	2		1/7/2012	1/8/2012	4
14	1/8/2012	2		1/8/2012	1/12/2012	4
15	1/9/2012	0		etc
16	1/10/2012	1
17	1/11/2012	0
18	1/12/2012	1
19	1/13/2012	1
20	etc
Sheet1

Excel 2007

Cell Formulas
Range	Formula
F7	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D7,$A$7:$A$19,"<="&E7)
F8	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D8,$A$7:$A$19,"<="&E8)
F9	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D9,$A$7:$A$19,"<="&E9)
F10	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D10,$A$7:$A$19,"<="&E10)
F11	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D11,$A$7:$A$19,"<="&E11)
F12	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D12,$A$7:$A$19,"<="&E12)
F13	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D13,$A$7:$A$19,"<="&E13)
F14	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D14,$A$7:$A$19,"<="&E14)

Thanks in advance!

SDL

 

[SLARRA]

Still looking for some help, even if it's to say this is not easily do-able.

Thanks!

SDL

 

[shadow12345]

can you write out the logic for this? for example its target output divided by units of output?

I don't understand how you arrive at the dates column e. If you do that ill give it a go.

*I think col A and B show how many units can be produced each day, but in not sure how it needs to be worked out.

 

[SLARRA]

Thanks for the help!

The problem is pretty simple, but I suspect the solution (formula) may be difficult.

Simply put, I'm trying to determine, for any given start date, what stop date will ensure (at least) the target amount of output units have been produced.

I want to produce at least 4 units (the target amount, as shown in the yellow cell in my example posting). And, I have that table that shows the expected output by calendar day. So, if - for example - I started on 1/1, we can see where I would attain that target amount of outputs by 1/7, with 1 unit produced on 1/4, 1 more unit on 1/6, and the final 2 units on 1/7. To get the stop date, I sum down the expected outputs column, starting with the row associated with 1/1. Once that running sum reaches 4 (or first exceeds 4), I now know the first stop date that ensures the production of 4 (or more) units of output.

If I started production on 1/5, I'd have to work until 1/8 to achieve the target amount of outputs. In that case, I'd actually produce 5 units (that is, 1/8 is the first end date that satisfies or exceeds the target production of 4 units, in this case exceeding it by 1 extra unit - but that's OK).

Since what I really want is the associated stop dates (I entered those stop dates in red manually, but want a formula to figure them out for me), this is some sort of a constrained sum and associated lookup. But, I have no idea how to replicate my manual process with a formula.

Thanks for helping me!

SDL

 

[shadow12345]

Ok I think I understand what you are after... ill give it a go.

 

[T. Valko]

I have a table of expected outputs by calendar day. I want to specify a target number of outputs (user entry, cell B1), then for any given start date, calculate the end date whose elapsed period (given that start date) will yield at least that target amount of outputs.

Example follows - I need a formula (prefer not to use VBA) that will calculate the stop dates, shown in red in Column E (note: for the sake of this example, I manually determined the appropropriate stop dates an inseted them in Column E).

Excel Workbook
	A	B	C	D	E	F
1	Target output:	4	Units
2
3
4	Expected Output	( Units by Date)		Date Range	to Achieve	Target Output
5
6	Date	Units		StartDate	StopDate	Units Yield
7	1/1/2012	0		1/1/2012	1/7/2012	4
8	1/2/2012	0		1/2/2012	1/7/2012	4
9	1/3/2012	0		1/3/2012	1/7/2012	4
10	1/4/2012	1		1/4/2012	1/7/2012	4
11	1/5/2012	0		1/5/2012	1/8/2012	5
12	1/6/2012	1		1/6/2012	1/8/2012	5
13	1/7/2012	2		1/7/2012	1/8/2012	4
14	1/8/2012	2		1/8/2012	1/12/2012	4
15	1/9/2012	0		etc
16	1/10/2012	1
17	1/11/2012	0
18	1/12/2012	1
19	1/13/2012	1
20	etc
Sheet1

Excel 2007

Cell Formulas
Range	Formula
F7	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D7,$A$7:$A$19,"<="&E7)
F8	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D8,$A$7:$A$19,"<="&E8)
F9	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D9,$A$7:$A$19,"<="&E9)
F10	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D10,$A$7:$A$19,"<="&E10)
F11	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D11,$A$7:$A$19,"<="&E11)
F12	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D12,$A$7:$A$19,"<="&E12)
F13	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D13,$A$7:$A$19,"<="&E13)
F14	=SUMIFS($B$7:$B$19,$A$7:$A$19,">="&D14,$A$7:$A$19,"<="&E14)

Thanks in advance!

SDL

Try this...

Note that this will only work in Excel versions 2007 and later (too many nested functions for earlier versions).

Enter this array formula** in E7:

=INDEX(A$19:INDEX(A$7:A$19,MATCH(D7,A$7:A$19,0)),MATCH(TRUE,SUBTOTAL(9,OFFSET(B$7,MATCH(D7,A$7:A$19,0)-1,,ROW(INDIRECT("1:"&COUNT(A$19:INDEX(A$7:A$19,MATCH(D7,A$7:A$19,0)))))))>=B$1,0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Format as Date

Copy down as needed.

 

[shadow12345]

I can't do that without VBA.... Perhaps someelse could provide a user defined function for it so at least it only a code once.

Option Explicit
Sub SumTo()
    Dim lngLastRow As Long
    Dim lngLoopCtr As Long
    Dim lngSumTotal As Long
    lngSumTotal = 0
    lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
    For lngLoopCtr = 6 To lngLastRow Step 1
        lngSumTotal = lngSumTotal + Cells(lngLoopCtr, "B")
        If lngSumTotal >= Range("e1") Then
            MsgBox "The total is = " & lngSumTotal & " for cell 'A" & lngLoopCtr & "'."
            Exit For
        End If
    Next lngLoopCtr
End Sub

This seems to do the the evaluation at least... but perhaps some else can help with the rest (Im stuck)




**** See now someelse has it MUCH simplier than me *** are well I tried

 

[SLARRA]

Big thanks to both Shadow (for taking a whack at this, tho I haven't tried your VBA method yet and certainly can't finish the code since I'm VBA illiterate) and T.Valko/Biff for the formula solution that works!

I'm trying to digest what Biff's formula is doing - lotta stuff going on there! - but the main point is that it works!

Funny, I would have guessed that this kind of running-sum lookup would be a relatively common problem posed to Excel, yet we can see from the complexity of your solutions that it's a bit of a tough nut to crack.

Thanks, again, for the help - it is much appreciated.

 

[T. Valko]

Big thanks to both Shadow (for taking a whack at this, tho I haven't tried your VBA method yet and certainly can't finish the code since I'm VBA illiterate) and T.Valko/Biff for the formula solution that works!

I'm trying to digest what Biff's formula is doing - lotta stuff going on there! - but the main point is that it works!

Funny, I would have guessed that this kind of running-sum lookup would be a relatively common problem posed to Excel, yet we can see from the complexity of your solutions that it's a bit of a tough nut to crack.

Thanks, again, for the help - it is much appreciated.

You're welcome. Thanks for the feedback!