Excel Pivot table calculated field

  • Thread starter Thread starter sjc5575
  • Start date Start date
S

sjc5575

Hi

i have a list of share prices and dates - i have used a pivot table to
show me the maximum share price for that share - i need help in trying
to calcualte the date on which this share price occured - can anyone
help.

columns in excel are

Stock name, date, share price, volume

regards

simon
 
Hi

You don't need a Pivot Table.
Enter the Stock you are interested in in E2 and enter this formula in F2
=SUMPRODUCT(($A$2:$A$100=$E1)*
($C$2:$C$100=MAX($C$2:$C$100))*$B$2:$B$100)
Format the cell as Date
 
Roger explained how to calculate the date outside the pivot table.

To do this in a pivot table:

Put stock name and date in the Row area of the pivot table
Put Share Price in the Data area
In the pivot table, right-click the field button for Date
Click on Field Settings
Click the Advanced button
Under Top 10 AutoShow, click On
Select Top 1 from the Show settings
For Using Field, select Sum of Share Price
Click OK

The pivot table should show the date with the highest share price for
each stock.
 
Hi,

Minor modification to the formula, since the stock went into E2, not E1:

=SUMPRODUCT(($A$2:$A$100=$E2)*
($C$2:$C$100=MAX($C$2:$C$100))*$B$2:$B$100)

Alternatively you can use:

=INDEX(B2:B26,MATCH(MAX(IF(A2:A26=F1,C2:C26,)),C2:C26,0))

This formula is array entered and assumes Stock symbols or names are in
column A, Dates in B, amounts in C. and you enter the stock of interes in
F1. To array enter a formula press Shift+Ctrl+Enter instead of Enter.
 
Hi,

Minor modification to the formula, since the stock went into E2, not E1:

=SUMPRODUCT(($A$2:$A$100=$E2)*
($C$2:$C$100=MAX($C$2:$C$100))*$B$2:$B$100)

Alternatively you can use:

=INDEX(B2:B26,MATCH(MAX(IF(A2:A26=F1,C2:C26,)),C2:C26,0))

This formula is array entered and assumes Stock symbols or names are in
column A, Dates in B, amounts in C.  and you enter the stock of interesin
F1.  To array enter a formula press Shift+Ctrl+Enter instead of Enter.

--
Thanks,
Shane Devenshire







- Show quoted text -
Click to expand...

Thanks Everyone, all your comments have been really useful.

regards

Simon
 
Back
Top