Calculate percentage from a single Pivot Cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1606873%22%20slang%3D%22en-US%22%3ECalculate%20percentage%20from%20a%20single%20Pivot%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606873%22%20slang%3D%22en-US%22%3E%3CP%3EDoing%20some%20option%20trading%20and%20trying%20to%20analyze%20the%20data%20better.%20As%20part%20of%20this%20I'm%20trying%20to%20aggregate%20my%20Win%2FLoss%20percentage%20across%20days%2C%20weeks%2C%20months%2C%20strategies%2C%20etc..%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20put%20together%20a%20detailed%20spreadsheet%20and%20created%20a%20pivot%20table%20from%20that%20sheet.%20One%20of%20the%20columns%20in%20the%20original%20sheet%20is%20populated%20with%20W%20or%20L%20(win%20or%20loss).%20Sometimes%20a%20single%20trade%20gets%20broken%20up%20into%20different%20individual%20trades%20(different%20buy%20or%20sell%20prices)%2C%20and%20I%20manually%20adjust%20the%20W%2FL%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20W%2FL%20gets%20combined%20within%20a%20pivot%20table%20where%20I%20can%20see%20an%20aggregate%20set%20count%20of%20W%20and%20L%20in%20two%20columns.%20I'l%20then%20use%20that%20calculation%20to%20populate%20columns%20like%20month%2C%20time%20of%20day%2C%20etc.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20this%20info%20to%20compare%20was%20in%20two%20columns%20it%20would%20be%20easy%2C%20but%20since%20I%20want%20to%20evaluate%20the%20winning%20percentage%20based%20on%20values%20within%20the%20same%20column%20(Won%20vs.%20Lost)%20I%20can't%20figure%20it%20out.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20really%20want%20is%20to%20have%20a%20pivot%20column%20that%20calculates%20the%20Win%20percentage%20of%20the%20aggregated%20values.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20this%20is%20clear%2C%20but%20if%20not%20I%20can%20add%20more%20detail.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1606873%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608193%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20percentage%20from%20a%20single%20Pivot%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608193%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F768351%22%20target%3D%22_blank%22%3E%40PatAzz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20replies%20yet%20but%20I%20have%20a%20couple%20of%20ideas.%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20added%20two%20columns%20in%20my%20source%20sheet%20(one%20for%20Wins%2C%20one%20for%20Losses)%20but%20I%20can't%20figure%20out%20the%20syntax%20for%20a%20calculated%20field.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%22Count%20of%20Adj%20W%2FL%22%20%2F%20%22Count%20of%20Adj%20Wins%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20appreciated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPat%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Doing some option trading and trying to analyze the data better. As part of this I'm trying to aggregate my Win/Loss percentage across days, weeks, months, strategies, etc..  

 

I have put together a detailed spreadsheet and created a pivot table from that sheet. One of the columns in the original sheet is populated with W or L (win or loss). Sometimes a single trade gets broken up into different individual trades (different buy or sell prices), and I manually adjust the W/L column.

 

This W/L gets combined within a pivot table where I can see an aggregate set count of W and L in two columns. I'l then use that calculation to populate columns like month, time of day, etc. 

 

If this info to compare was in two columns it would be easy, but since I want to evaluate the winning percentage based on values within the same column (Won vs. Lost) I can't figure it out. 

 

What I really want is to have a pivot column that calculates the Win percentage of the aggregated values. 

 

Hopefully this is clear, but if not I can add more detail. 

1 Reply
Highlighted

@PatAzz 

No replies yet but I have a couple of ideas. 

I've added two columns in my source sheet (one for Wins, one for Losses) but I can't figure out the syntax for a calculated field. 

 

         "Count of Adj W/L" / "Count of Adj Wins"

 

Any help would be appreciated

 

Pat