for range of cell = n, calculate sum of cell + n-1 previous cell

**TNLNYC** · 07-02-2013, 08:58 PM

I have a product that is purchased for a period of time ranging from 1 to 12 month. So I have a cell with the value that can be anything between 1 and 12.

I also have a table in a separate sheet that has 12 columns, each with a new instance of that product.

I want to track existing contracts (eg a contract that is running for 3 month would still show up in column 2 and 3 but then disappear in column 4.

I could create an array of 12 cells by 12 cells to account for each possibility but that seems to be a drag and I suspect I can do this via formula.

Given values
A in column 1
B in column 2
C in column 3
D in column 4
...
L in column 12

The array looks like this:

	1	2	3	4	..	12
1	A	B	C	D	..	L
2	A	A+B	B+C	C+D	...	K+L
3	A	A+B	A+B+C	B+C+D	...	J+K+L
4	A	A+B	A+B+C	A+B+C+D	...	I+J+K+L
..
12	A	A+B	A+B+C	A+B+C+D	...	A+B+C+D+E+F+G+H+I+J+K+L

Based on the choice I have on the first column, it would return the appropriate sum that would populate the field "existing customer contracts"

Any ideas?

PS: Here's an example of what I want to do: example.xlsx

Please Login or Register  to view this content.

**vlady** · 07-02-2013, 09:07 PM

Moderator's Note:

Welcome to the forum.

some tips:

Want to get answers quickly?
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Regards,
Vlaidmir

**TNLNYC** · 07-02-2013, 09:16 PM

OK. I added a file.

**mikeTRON** · 07-02-2013, 09:34 PM

Are you familiar with the offset function? It does exactly what you are wanting to do.

I have attached a possible solution. Let me know if you need an axplanation.

**mikeTRON** · 07-02-2013, 09:42 PM

Whoops, I attached it before saving. Basically you would need to adjust the relative references to get to this point.

I have attached the updated version.

I also wonder if you meant to say that in month 3 you only include month 1&2, not also 3 since they are new in that month, but that's for you to decide. I am showing you what I mean in the screenshot.

**TNLNYC** · 07-02-2013, 09:56 PM

Originally Posted by mikeTRON

Whoops, I attached it before saving. Basically you would need to adjust the relative references to get to this point.

I have attached the updated version.

I also wonder if you meant to say that in month 3 you only include month 1&2, not also 3 since they are new in that month, but that's for you to decide. I am showing you what I mean in the screenshot.

You are correct that I don't include them since they are new. Why the IFERROR here? Just curious (BTW, Thanks for the quick reply, it seems to do the trick

)

**mikeTRON** · 07-02-2013, 10:04 PM

Ok, so just change the formula to not include so many in the width of the offset (which should be the last number in the offset function).

As for the IFERROR, lets say you want to pull the last 6 months of data right. So in the first month summary, the formula will go up 4 rows then left 5 columns, but there may not be 5 columns to move left so it will give an error. So I said IF there is an eror, simply sum the values from month 1 to the current month. This way you wont see any REF errors like I was seeing as I changed the month rolling sum from 3 to 9.

**mikeTRON** · 07-02-2013, 10:13 PM

Here is the update version and I have duplicated it below without the IFERROR functionality to show you what I mean.Example_Updated-Solved.xlsx

**TNLNYC** · 07-02-2013, 10:16 PM

Thank you so much. I'm going to deconstruct this and figure out how to have it drive a model I'm working on using this.

**mikeTRON** · 07-02-2013, 10:20 PM

Let me know if you have any questions or if you want to post a version of the real format (without senstive data) so I can give you the updated formulas I might be able to do that.

for range of cell = n, calculate sum of cell + n-1 previous cell

LinkBack

Thread Tools

Rate This Thread

Display

for range of cell = n, calculate sum of cell + n-1 previous cell

Re: for range of cell = n, calculate sum of cell + n-1 previous cell

Re: for range of cell = n, calculate sum of cell + n-1 previous cell

Re: for range of cell = n, calculate sum of cell + n-1 previous cell

Re: for range of cell = n, calculate sum of cell + n-1 previous cell

Re: for range of cell = n, calculate sum of cell + n-1 previous cell

Re: for range of cell = n, calculate sum of cell + n-1 previous cell

Re: for range of cell = n, calculate sum of cell + n-1 previous cell

Re: for range of cell = n, calculate sum of cell + n-1 previous cell

Re: for range of cell = n, calculate sum of cell + n-1 previous cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions