0

I have a table in excel. I want to create a pivot table where:

  1. Take the final value of capital left on that day if there are more than one trade on the same day.
  • In my example below, since there are 2 trade on 2019-07-22, the value in pivot table should be $4586.69 instead of $4617.80 or $9204.48.
  • Not sure why the value of capital left for 2019-07-16 is $9145.96 instead of $4632.55
  1. There is no $59494.22 at the first row of the pivot table (not sure why it appear).

I attached a screenshot below in case you don't understand what I mean. I'm new to pivot table and hope to get some advise, thanks!

enter image description here

0

1 Answer 1

1

In my opinion, I think this is a lot easier if you didn't have rows with blank dates. But here's one way to keep those rows and achieve what you need. This works as long as your trade numbers increment higher with dates and never repeat or overlap.

First, create two more columns in your table data.

Last_Trade_of_Day (to get the last trade number for each date)

    Cell E2 formula: =MAXIFS(B:B,C:C,[@Date])

Last_Trade_Amount (to get the Capital remaining for the last trade of that day)

    Cell F2 formula: =IFERROR(VLOOKUP([@[Last_Trade_of_Day]],B:D,3,FALSE),0)

Create your pivot table, using Trade as filter (de-select blank), Date as rows, and Last_Trade_Amount as Values. Right-click on the last_trade_amount cell and go to Value Field Settings. Change it to "average".

That's it...

enter image description here

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .