Pivot Table calculation (Count > 0) / (Total count)

Welcome,

It would really help if you posted some sample data.

Solution I'm thinking will not need any helper columns but do this work via SQL in between the source data and the pivot table.

Regards, Fazza

Sounds good. eg cut down data

Date WS AB QTY Entry Exit PL
080817 Y Y 2000 20.40 21.70 2600
080820 N Y 500 17.90 31.50 6800
080901 Y N 1000 20.00 19.50 -500
080904 N Y 1500 10.90 10.90 0

The PL is the column of interest. Have left out the helper columns

Thanks for your help

Well, what I've done seems to get normal pivot table functionality. There will be simpler ways if that isn't required. Let me know if this isn't right for you.

It is easier to set up from a separate workbook. But, after doing this the created new worksheet can be put back into the source file. So there is a little mucking around to set it up.

I'll assume the data is in the first row of the source worksheet.

So, save & close your source file. From a new file start to make a pivot table and take the external data source option. Hit 'get data', and then Excel files & OK. [Actually the steps til here can alternatively be done via menu data, import external data, new database query instead of the pivot table external data option.] Browse & select the data source file. If you get a message about no visilbe ranges hit the OK button and then 'options' and then select 'system tables' - for Excel file this corresponds to worksheet names - and OK. See the worksheet name and then hit the little + sign and select all the fields. They appear on the RHS. Hit next a few times and when you see finish take the second option to view or edit in MS Query and then hit 'finish'. MS Query should open. Now hit the SQL button and edit the text string (of the SQL) from what you see to add some extra text immediately prior to the word FROM. So it will be "SELECT quite a few things .PL FROM etc" The new bit to add, and you can copy from here, is
This must be inserted just before the FROM (and there must be a space or carriage return or other character before the FROM. That is why I have left a space above). Note the leading comma to separate the new items from the last item before you start. This will be PL

Exit the SQL entry window, and OK to the message about not being able to be represented graphically. Now hit the 'open door' icon to exit MS Query and then the 'finish' button to have the new PT in the worksheet.

Arrange the PT fields how you want. Put the new fields of TradeCount and ProfitableTradeCount in as data fields. Add a calculated field SuccessRate as=ProfitableTradeCount/TradeCount

Now open the original data file and move the new worksheet into it.

There are other ways to do get the same or similar results, just depends on exactly what is required.

Regards, Fazza