-->
🏠 🔍
SHAREOLITE

Calculate proper Weighted Average buy and sell values using Sumproduct in Excel

When trading in stocks, we rarely buy all shares at the same price. You may accumulate shares at different price levels over time, or sell in multiple lots. To know your true average cost or average selling price, you need to calculate a weighted average , not just a simple average.

Excel’s SUMPRODUCT function makes this calculation simple and accurate.

Why Not Simple Average?

Suppose you buy:

  • 10 shares of Infosys @ ₹1,500

  • 20 shares of Infosys @ ₹1,600

A simple average would be:
(1500+1600)÷2=1,550(1500 + 1600) ÷ 2 = ₹1,550

But this is wrong, because you didn’t buy equal quantities. 

 


Correct method is weighted average, which considers both price and quantity.

Manual Calculation of Weighted Average Buy Price

Formula:

Average Buy Price=(Quantity×Price)Quantity\text{Average Buy Price} = \frac{\sum (\text{Quantity} × \text{Price})}{\sum \text{Quantity}}

Using the example:

  • Buy 10 shares × ₹1,500 = ₹15,000

  • Buy 20 shares × ₹1,600 = ₹32,000

Total Investment = ₹15,000 + ₹32,000 = ₹47,000
Total Quantity = 10 + 20 = 30

Average Buy Price=47,00030=1,566.67

Using SUMPRODUCT in Excel

Suppose you record your trades like this:

Quantity Price
10 1500
20 1600
15 1550

Formula for average buy price:

=SUMPRODUCT(A2:A4, B2:B4) / SUM(A2:A4)

Explanation:

  • SUMPRODUCT(A2:A4, B2:B4) → multiplies each quantity × price and adds them.

  • SUM(A2:A4) → adds up total quantity.

  • Division gives weighted average price.

📈 Example: Average Selling Price

If later you sell shares at different prices, you can apply the same logic.

Quantity Sold Price
5 1700
10 1750
5 1600

Formula in Excel:

=SUMPRODUCT(A2:A4, B2:B4) / SUM(A2:A4)

Result → Weighted average selling price.

Why This Matters

  • Helps you know your exact cost per share for profit calculation.

  • Useful when filing taxes, as capital gains depend on accurate cost basis.

  • Eliminates mistakes of using simple averages.

Trade monk sacred equation of Trading Mastery , Profit = Edge × Discipline × Detachment

In the world of trading, profits are often misunderstood as luck, timing, or sheer market genius. But true professionals know this: Profit is not random. It is earned. And if there were a spiritual equation to capture this sacred art, it would be:

Profit = Edge × Discipline × Detachment

This is not just a formula , it is a philosophy, a path, and a mirror. Let’s go deeper into each of these components and understand why, unless all three multiply together, the outcome remains zero. 

Edge – The Truth You Trade

An edge is your mathematical, structural, or psychological advantage in the market. It is not a guarantee , but over time, it tilts probability in your favor.

What is an Edge?

  •  A well-defined setup that has positive expectancy over a large sample size.
  •  A deep understanding of market structure, volume behavior, price action, or statistical anomalies.
  •  A unique insight or intuition born from your hard-earned screen time.

"Edge is not finding magic. It is knowing something so well, that even randomness becomes familiar." 

Without an Edge , you are gambling. No amount of discipline or emotional mastery can save a system that does not work. You must refine, test, fail, and evolve , until your edge becomes your dharma


 

Discipline – The Bridge Between Knowing and Doing

You may have an edge. But the market doesn’t pay you for what you know , It pays you for what you do with what you know. Discipline is your ability to:

  •  Follow your system with precision.
  •  Respect your stop-loss, position size, and setup rules — even when emotions surge.
  •  Say NO to noise, FOMO, revenge, overtrading, and ego.
  •  Show up daily, journal your trades, and be a student of your own behavior. 

"Discipline is not restriction. It is the ultimate freedom — from chaos, from self-sabotage, from illusion." 

Even a great edge fails without discipline. This is why so many traders with brilliant systems lose money — they haven’t mastered themselves.

Detachment – The Flow State of Profit

This is where the spiritual layer unfolds. Detachment does not mean disinterest. It means trading with full focus but zero attachment to the outcome. When you are detached , 

  •  You stop needing every trade to work.
  •  You release the pressure to “make back losses.”
  •  You trust the probability edge and accept variance.
  •  You allow flow — not fear, greed, or ego — to guide your decisions.

"Detachment is the art of placing a trade… and letting go the moment it is placed."

It is this stillness — this space between effort and expectation — where true profit grows. Not just financial profit, but the profit of peace, mastery, and clarity.

Notice it’s not Profit = Edge + Discipline + Detachment. It’s Edge × Discipline × Detachment. Because if any one of them is zero — You have no edge? You are lost in randomness. You have no discipline? You burn your edge.  You have no detachment? You distort execution with emotion.

In any of these, the product is zero. But when all three are present, even modestly — they compound. And the result is consistent, conscious profitability.To truly embrace this equation is to shift from the noisy world of quick wins to the timeless path of the Trading Sage . Seek your edge with the curiosity of a scientist.Honor discipline like a warrior obeying the code. And walk with detachment like a monk in a marketplace. 

Repeat after each trade - Did I trade with Edge ? Did I trade with Discipline?Did I trade with Detachment? If yes — you are already winning.If no — pause, reflect, and recalibrate.Because in the end , The greatest profit is not what you earn from the market, but who you become through it. 

Disclaimer: The information provided in this article is for educational purposes only and should not be construed as financial advice. The content is based on publicly available information and personal opinions. Readers are encouraged to conduct their own research and consult with a qualified financial advisor before making any investment decisions. The author and publisher are not responsible for any financial losses or damages incurred as a result of following the information provided in this article. 

–>