Help with very long repetitive formula

[senior_dolor]

Hello everyone,

First question here though I've been using tips & tricks from the forum for many years now.

In the picture below you can see a very long formula that is essentially the same thing several times, with the red areas being the only changes. The question is if there's a way to rewrite the formula without having to use all these lines, but also to be able to adjust the number of repetitions based on the value of another cell. In theory this can be done manually for a rather large number of lines but you can imagine that it's not practical, customisable and definitely not good practice.

Tried using arrays in the red areas but no avail. Seems I'm doing something very wrong.

Thanks in advance for your help.

[/URL]
upload image
[/IMG]

 

[Dryver14]

Your index and match seem to be referring to single cells! Whats the reason for this ie: what are in those cells

 

[StephenCrump]

Welcome to the Forum!

Yes, your formula certainly can be simplified. But can you do a couple of things to help ....

1. Please post a screenshot so we can see where you are using this formula and the results you are getting, plus the values of the cells being referenced in the formula. See Part B here for ways you can post a screenshot.

https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

2. Copy/paste your formula into the thread - no-one will want to key this from scratch based on an image.

 

[senior_dolor]

Your index and match seem to be referring to single cells! Whats the reason for this ie: what are in those cells

The formula is meant to be dragged along a row so this array will also be dragged by keeping its "beginning" (left cell) unchanged; that the leftmost cell of the row. The formula works as it is supposed to (checked the calcs manually), my question is if there is a shorter and more flexible way to write it. Any ideas highly appreciated!

 

[senior_dolor]

That a screenshot of my spreadsheet. The formula I want to fix is at the yellow line. It's supposed to calculate the aggregate installments of the sale of various condos along different periods. For each condo there's a downpayment (calculated) and a pattern of installments. To calculate the installments for each individual condo is easy, but to do so with a larger number of them in various periods is another story. Managed to do so with the repetitive formula but it has to be dome manually and for a certain number of condos, which I want to have as a variable since this is a generic model. All the cells to the left are inputs (plus some other not shown like price inflation etc)...

[/URL]
image uploader
[/IMG]


To make it easier for anyone who wants to give it a shot...

=(iferror(index($n31:n31,0,match(1,$n26:n26,0)-$a31),0)/$i31*$a32)*if(or(n$2<=($a$9+$a21*0)-$a31,n$2>($a$9+$a21*0)-$a31+$h32*$l32),0,if(mod((n$2-(($a$9+$a21*0)-$a31)),$l32)<>0,0,1))
+(iferror(index($n31:n31,0,match(2,$n26:n26,0)-$a31),0)/$i31*$a32)*if(or(n$2<=($a$9+$a21*1)-$a31,n$2>($a$9+$a21*1)-$a31+$h32*$l32),0,if(mod((n$2-(($a$9+$a21*1)-$a31)),$l32)<>0,0,1))
+(iferror(index($n31:n31,0,match(3,$n26:n26,0)-$a31),0)/$i31*$a32)*if(or(n$2<=($a$9+$a21*2)-$a31,n$2>($a$9+$a21*2)-$a31+$h32*$l32),0,if(mod((n$2-(($a$9+$a21*2)-$a31)),$l32)<>0,0,1))
+(iferror(index($n31:n31,0,match(4,$n26:n26,0)-$a31),0)/$i31*$a32)*if(or(n$2<=($a$9+$a21*3)-$a31,n$2>($a$9+$a21*3)-$a31+$h32*$l32),0,if(mod((n$2-(($a$9+$a21*3)-$a31)),$l32)<>0,0,1))

 

[StephenCrump]

You're not making it easy to help. If you don't post a screenshot, rather than an image, you're expecting us to re-key all your data.

At minimum, perhaps you could tell us which cells this image represents, including where the column spacing isn't clear from the image, e.g. it looks like 12 (installments) and 2 (months) are parameters in particular cells, but it's not clear which cells these are? Please also let us know the values of the other cells used in your formulae and not shown in the screenshot, so that we can replicate your results.

How long is a period, and why are there two columns for each period 1,1,2,2, ... etc. Presumably this is really 0.5, 1.0, 1.5 and 2.0 periods (based on the price inflation shown). And presumably sales can also happen at mid-period?

Presumably your formula results are those highlighted in yellow? Are you happy that these numbers are correct? The progression looks anomalous to me based on the data posted.

 

[senior_dolor]

Sory for this Stephen, thought it was only a matte rof formula syntax.

Please see below a screenshot of the relevant input cells and part of the spreadsheet (too big to capture the whole thing).

[/URL][/IMG]

The light orange cells will be hidden and are being used to help me do some intermediate calculations. The 1,1,2,2s you referred to relate to the condo sales and are being used by MATCH in order to identify the period each sale occurs, as this is the base for all the calculations that follow (downpayment, installments, sales costs etc.). By taking into consideration the downpayment and the number of installments of the remaining amount I then want to calculate the income from installements in the future periods. The challenge is that there is no pattern between sales timing (e.g. x units per y months) or payment pattern (i% of downpayment and n number of installments every m months). You understand that seeing a pattern is more a matter of coinsidence between the inputs rather than a norm.

As I said the formula as it is now produces the correct result but it's too cumbersome and has to be repeated many times.

Just a note: the first part of the formula (before the * sign) refers to the amount of the installment and the second on the timing. It is essentially a SUMPRODUCT.

Thanks for your time!

 

[StephenCrump]

Thanks for posting back. Have a look at the link in Post #3 some time. It will allow you to paste a screenshot (like mine below) that others can simply copy/paste into a workbook. Much quicker!

There is still too much detail in your image for me to try to replicate, so let's distil this down.

The challenge is that there is no pattern between sales timing (e.g. x units per y months) or payment pattern (i% of downpayment and n number of installments every m months).

As long as there is a single payment pattern for all downpayments, this shouldn't be a problem. In essence, I think you're trying to do something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Delay	0	periods
2	Spacing	2	periods
3	Expiry	3	times
4
5	Time	0	1	2	3	4	5	6	7	8	9	10
6	Increments	1		3	7		11	19
7
8	Results:
9	Time (t)	0	1	2	3	4	5	6	7	8	9	10	11	12
10	Results	1	0	4	7	4	18	22	18	19	11	19	0	0
11
12	In detail:	1		1		1
13				3		3		3
14					7		7		7
15							11		11		11
16								19		19		19
17
18	TOTAL	1	0	4	7	4	18	22	18	19	11	19	0	0
Sheet1

The result 22 highlighted in H18, for example, is =SUM(H12:H16)

But an easier way to get the number is to realise that it must equal:
1. The number 2 periods ago in F18 = 4, plus
2. The new increment that has just started, H6 = 19, less
3. The increment that has just ceased, B6=1
--> 22.

Using this approach, a general solution is:

B10: =IF(t>=tStart+Spacing,INDEX(Results,t+1-tStart-Spacing))+IF(t-Delay>=0,IFERROR(INDEX(Increments,t+1-Delay),0),0)-IF(t-Delay-Expiry*Spacing>=0,IFERROR(INDEX(Increments,t+1-Delay-Expiry*Spacing),0),0)

where:

Delay: =B1
Spacing: = B2
Expiry: =B3
tStart (B9) MIN(0,Delay)
B10: =1+B9
t: = B9:N9
Results: =B10:N10

I don't know if you need the results adjusted for inflation, but this could be done fairly easily if required.

I have attached the file here: https://app.box.com/s/6mkvfdwdedllmesjcrzyyzs3loigdexc