Can't use Running Total Field in Pivot Table Calculated Field

[MikeH2]

I know I'm going to feel really dumb about this when I find the answer - but here goes.

Problem: I've got a database that consists all of my stock transactions for the last 20 years. The fields include: date, account, stock, activity, number of shares and cost per share.

I've used a Pivot Table to create a series of columns for each stock that consists of the number of shares transacted on a given date, the running total of those shares and the cost/share on that date.

I want to calculate the market value of the stock on that date by using the product of the running total of stock shares multiplied by the cost per share on that date.

HOWEVER

When I try to insert a calculated field in the Pivot Table, the running total does not show up in the list of fields that I can select from. When I try to type in the name of the running total field, I get a notice that says "the formula you typed contains an error".

Any ideas?

Thanks
MikeH2

 

[Andrew Poulsom]

What is the name of the running total field? I don't think you will be able to calculate the market value in a calculated fireld because the price won't be weighted by the volume Why don't you add a value field to your source data and include that in your pivot table?

 

[MikeH2]

Arrgh!!

I knew I'd end up feeling like a dope - again.

Went back and added the Market value to my database calculations - then could extract and summarize it in the pivot table.

Problem is solved - and thanks

HOWEVER

I still wonder why it doesn't seem like you can reference a running total in the pivot table's calculated field.

Thanks again for your help.

MikeH2

 

[MikeH2]

Just realized that I declared victory too quickly. The Market Value on a given date is not the Rolling Total of the individual transactions for a given security - it is the product of the most recent quote times the rolling total of the number of shares. I'm back to my original problem.

 

[Andrew Poulsom]

In your source data you can use SUMIF to return the total transactions for a given share and multiply it by the latest price to get the market value.

 

[MikeH2]

Worked like a charm. Guess I'm too fixated on solving this via Pivot Tables.
"To a carpenter, every problem has a hammer as part of the solution".
Thanks again
MikeH2

 

[RosalynA]

Worked like a charm. Guess I'm too fixated on solving this via Pivot Tables.
"To a carpenter, every problem has a hammer as part of the solution".
Thanks again
MikeH2

Me too . I was trying to do this in the pivot table whereas the underlying data with a combination of Sumifs (i need a few more filtering criteria) and calculations allowed me to quickly see when total projects hours have gone negative.