I’m creating a trading performance tracker in Google Sheets. My data is organized with dates in column A, weekdays in column B, and profit/loss amounts in column C.
I need to create a summary table that displays the top 3 highest profits and bottom 3 lowest losses, along with their corresponding dates. The final output should look something like this:
Best 3 P/L Date Worst 3 P/L Date
35000 5 Jun 23 -45000 2 May 24
28000 18 Jul 24 -38000 12 Jul 24
18000 3 Apr 24 -82000 20 Jan 24
What formulas or functions should I use to automatically generate this summary table?
just use QUERY with ORDER BY. try =QUERY(A:C,"SELECT A,C WHERE C is not null ORDER BY C DESC LIMIT 3") for top 3, or swap DESC to ASC for bottom 3. way easier than dealing with INDEX/MATCH.
You could build complex formulas with LARGE, SMALL, INDEX, and MATCH functions, but you’ll hate yourself later when your data changes and everything breaks.
I had the same problem tracking API performance metrics. Started with Google Sheets formulas but ended up spending more time fixing broken references than actually analyzing data.
Just automate the whole thing instead. Set up a workflow that pulls your trading data, processes it automatically, and generates those summary tables without any manual formula work.
Connect Google Sheets directly to automation workflows that:
Monitor your data for changes
Sort and rank profit/loss values instantly
Update summary tables in real time
Send alerts when you hit new highs or lows
No more formula errors or range updates. The system handles everything automatically and gives you consistent results.
This scales perfectly when your trading data grows from dozens to thousands of entries. You can easily add new metrics or change formats without rebuilding formulas.
LARGE and SMALL functions are perfect for this. For your top values, put =LARGE(C:C,1), =LARGE(C:C,2), =LARGE(C:C,3) in different cells. To get the matching dates, use =INDEX(A:A,MATCH(LARGE(C:C,1),C:C,0)) and adjust for the 2nd and 3rd highest. Just swap LARGE with SMALL for the lowest values. I use this setup for monthly sales reports - works great with duplicates, though you might need helper columns if P/L amounts are identical. One heads up: when your data grows, you’ll have to update references or switch to dynamic ranges. Also throw some conditional formatting on your summary table - green for gains, red for losses makes it way easier to read.