Function for a given number of various subtractions?

[amwilson97]

I need to create a function that will allow me to subtract a number P1 from an initial value, followed by this P1 being subtracted from the result RP1 times. Once P1 has been subtracted RP1 times, P2 must be subtracted RP2 times, and then P3 subtracted RP3 times until 0, which it will do as the data is based on a payment scheme. The problem I am having is creating a general formula which allow me to do this for any initial value, P1,P2,P3, RP1,RP2 and RP3. I can't figure out how to perform the function using the cell above rather than the initial value every time, and I have no idea how to perform each subtraction the required number of times. I am very new to this so I'm sure this must seem very straightforward to you people! If anyone could help create me a formula that would be very appreciated, thanks

 

[Eric W]

Welcome to the board!

I'm a little unclear on your question, but I think I have enough to take a shot at it:

	A	B	C	D	E
1	Balance		Payment	Count
2	1000		100	5	0
3	900		75	4	5
4	800		25	8	9
5	700
6	600
7	500
8	425
9	350
10	275
11	200
12	175
13	150
14	125
15	100
16	75
17	50
18	25
19	0
20	0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula A3 =MAX(0,A2-INDEX($C$2:$C$4,MATCH(ROW()-3,$E$2:$E$4))) E3 =E2+D2 E4 =E3+D3 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Put your balance in A2, the payment amounts in C2:C4, and the number of times for each payment in D2:D4. The numbers in E2:E4 are running totals of the payments. Now put the formula in A3 and drag down. Change the references in the formulas to match your sheet. The E column can in fact be incorporated into the formula, but it would make it a lot more complicated. We also may need to adjust the formula based on what row you start in.

Let us know if this helps.

 

[amwilson97]

Hi, thanks for the quick reply. This works perfectly for one set of data, but i need to create a template that can be extended to any variety of starting amount, payments and repetitions of each payment. ideally, if I could put the data for this in A1:A7 for example, so each column represents one set of data, so column B could then be separate and so on that would help. I don't see a way to incorporate your column E into this though, unless there is a way to hide this?

Column A
Initial amount
Payment 1
repetitions of P1
Payment 2
Repetitions of P2
Payment 3
Repetitions of P3

Initial Value
Balance after Payment 1
etc
etc
etc
Until 0

This kind of represents what I need, so the formula can be extended across to new columns and the payment scheme be automatically shown for new sets of data. Is this possible?
Thanks

 

[amwilson97]

Oh also, is there a way to allow the first payment to occur at any point in column A rather than only being able to use A2? Each payment scheme may start on a different month so one might start in A2 but another might need to start in B6, sorry if this overcomplicates things, this would just be so uselful if I could make the template work.

 

[Eric W]

Column E could theoretically be incorporated into the formula, but there are bigger issues with your proposed layout. If you want everything in one column, I think a formula solution is out. I could write a macro to do that. You'd enter the data in the format you describe, select the cell with the balance, then call the macro, and the macro would put the running balances lower down in the same column.

If you want formulas, I'd strongly recommend 3 columns. Something like:

	K	L	M
1	Start		Balances
2	1000		1000
3	Payments	Counts	900
4	100	5	800
5	75	4	700
6	25	8	600
7			500
8			425
9			350
10			275
11			200
12			175
13			150
14			125
15			100
16			75
17			50
18			25
19			0
20			0
21			0
22			0

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

You'd put your parameters in K:L (or whatever), and then put the formula in M and drag down. Not too friendly though. I'd probably try the macro. Let me know what you think, and which way you want to go, and I'll write something up.

 

[amwilson97]

Ok, I have no clue how to use macros so if you think that's the best way to go, I would be eternally grateful if you could that. Its up to you
Just one thing, I've just realised ill need two more payment types at the end that will always be taken off once each, and this will always result in the balance reaching 0 at the end.
So, if i can put months in column A, starting at Jan-2012 so each row represents a new month, then each column represents a payment scheme, with 5 types of payments with varying repetitions except for the final two which each only have one rep, that would be awesome. No rush though man honestly I'm extremely grateful

 

[Eric W]

Try this.

Open a COPY of your workbook. Press Alt-F11 to open the VBA editor. From the menu, click Insert > Module. On the sheet that opens, paste this code:

Sub PaymentMaker()
Dim MyData As Variant, MyDict As Object, i As Long, ctr As Long, j As Long

    If MsgBox("Are you sure that the selected cell is on the starting balance?", vbOKCancel, _
               "Payment calculator") = vbCancel Then Exit Sub
    
    MyData = Range(ActiveCell, ActiveCell.End(xlDown)).Value
    Set MyDict = CreateObject("Scripting.Dictionary")
    MyDict.Add 1, MyData(1, 1)
    ctr = 1
    For i = 2 To UBound(MyData) Step 2
        For j = 1 To MyData(i + 1, 1)
            MyData(1, 1) = MyData(1, 1) - MyData(i, 1)
            ctr = ctr + 1
            MyDict.Add ctr, MyData(1, 1)
        Next j
    Next i
    
    ActiveCell.Offset(UBound(MyData) + 3).Resize(MyDict.Count) = WorksheetFunction.Transpose(MyDict.items)
    
End Sub

Go back to your workbook. Somewhere on your sheet, select a cell, and enter the parameters like you said, for example:

	G
8	1200
9	100
10	5
11	75
12	4
13	25
14	8
15	150
16	1
17	50
18	1

<tbody>
</tbody>

Sheet5​

Initial amount, payment 1, # of times of payment 1, payment 2, # of times of payment 2, etc. You can make this list as long as you want, as long as it's in this format. If you have single payments, just put 1 for the number of times.

Now select the cell with the Initial amount, G8 in this example. Press Alt-F8, select PaymentMaker, and click Run. You should get this:

	G
8	1200
9	100
10	5
11	75
12	4
13	25
14	8
15	150
16	1
17	50
18	1
19
20
21
22	1200
23	1100
24	1000
25	900
26	800
27	700
28	625
29	550
30	475
31	400
32	375
33	350
34	325
35	300
36	275
37	250
38	225
39	200
40	50
41	0

<tbody>
</tbody>

Sheet5​

Also, press Alt-F8 again. Select PaymentMaker, but this time click the Options... button. Where it says Shortcut key, you can put a letter in the box after Ctrl+. If you put a in that box, and click OK, you can now run this macro by just clicking Ctrl+a, you don't need to go through the Alt-F8 menu anymore.

Let me know how this works.

 

[amwilson97]

When I try to press alt-f8 and then choose the macro, i get an error saying Run Time error '9': Subscript out of range??

 

[amwilson97]

Wait no it has worked now because I'd put headers in the leftmost column which seemed to be screwing around with the code. IS there a way I can have these in, and so I can choose where to start the payments from, for example in August 2013, rather than Jan 2012?

 

[Eric W]

This is designed to do one column at a time. You put your parameters in a column, starting at B2, or G8. Then SELECT the cell with the starting balance in it, like G8. Then run the macro.

If you want to do multiple columns at a time, I'm not sure how to choose them. I could look at the column headers, and if it's non-empty, look for the lowest block of numbers in that column and run the routine based on that. But that seems pretty risky. You'd need a better way to identify the starting parameters in each column.