Formulas' on Dynamic Range

**knocks420** · 08-07-2012, 09:40 PM

Lets say I have a two columns, one with data, another with a simple function:
1 =A1
2 =B1+A2
3 =B2+A3
4 =B3+A4
5 =B4+A5

Now lets say I want to make column1 dynamic. How can I get the formulas in Column2 to also be dynamic? Other then stretching the formulas down to fill the entire column?

Basically I have a pivot table with a stock price and I want to make a cumulative line chart but the amount of data can differ depending on the stock.

Thanks so much!

**DBY** · 08-08-2012, 08:39 AM

Hello
As you're using Excel 2007, then convert your range of data into a Table, the formulas will automatically be added when you add a new row of data. Also, if you reference the Table as the Pivot source it too will be dynamic.

Hope this helps.
DBY

**knocks420** · 08-08-2012, 02:51 PM

Another question. I have a set of data, dates and P&L. I created a pivot table and have the following:

Date, Sum of P&L,
5/1 $100
5/2 -$200
5/4 $100

I would like to add a 3rd column to create a cumulative P&L or equity curve. I would like to create another column IN THE PIVOT TABLE to create an equity curve:

Equity Curve
= $100
= $100 + -$200
= 100 + -$200 + 100

Etc and ultimately chart this...

Hopefully this makes sense?

Thanks

**knocks420** · 08-08-2012, 09:26 PM

Aug. 08 21.23.jpg

\1

I would like to create what is in ColumnQ automatically and for varying lengths of the pivot table to ultimately chart.

**Ace_XL** · 08-09-2012, 02:39 AM

For cumulative sums use

=IF(P1="","",SUM($P$1:P1)) and drag down, where Column P holds your individual values

The formula will change to
For Row 2 =IF(P1="","",SUM($P$1:P2))
For Row 3 =IF(P1="","",SUM($P$1:P3)) and so on

**knocks420** · 08-09-2012, 08:40 AM

Thanks, though it should be dynamic to the pivot table. I want to apply the formula to the contents of the pivot table and have it automatically resize with the pivot table. Make sense? Dragging not what I'm trying to do.

**Ace_XL** · 08-09-2012, 10:45 AM

You could copy this to 500 rows or 100 rows or more depending on what your expected pivot table length will be.

Once yourefresh the pivot table with updated contents =IF(P1="","", will ensure that if there are no contents in the pivot table it will return a blank, so you dont need to recreate or retype the formula .

Does that make sense?

**knocks420** · 08-09-2012, 10:56 AM

That is exactly what I do NOT want to do, but I got some assistance and found the solution.

In the pivot table, Add the value column AGAIN (see above picture) then in the Show Value As I set to "Running Total In" and Date

**Cutter** · 08-09-2012, 02:34 PM

@ knocks420

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.