How to filter column with same unique match name and to obtain their average values

P

pankajgrover

Board Regular
Joined
Oct 27, 2022
Messages
157
Office Version
  1. 2021
Platform
  1. Windows
Here is my some stocks in excel. i want to filter my column "particular" to know average of shares by total / qty (sum of qty column) with matching same stocks. so i want 2 things, first avg of shares for each same name stocks and sum total number of same name stocks. Filter Result should come in last blank Column onwards.. how will do that . here is excel sheet. Thanks
ParticularQtyBuy/SellPricetotal
NSE SAIL 29 Aug 202425BUY135.73392.5
NSE GUJGASLTD 29 Aug 20245BUY643.53217.5
NSE TITAN 29 Aug 202411BUY3347.736824.7
NSE BHEL 29 Aug 202450BUY28714350
NSE BANKNIFTY 28 Aug 20241BUY49899.949899.9
NSE HEROMOTOCO 29 Aug 20241BUY5187.75187.7
NSE SAIL 29 Aug 202425BUY135.843396
NSE BHEL 29 Aug 202425SELL287.87195
NSE HDFCBANK 29 Aug 202411BUY1610.6517717.15
NSE M&M 29 Aug 20244BUY2651.610606.4
NSE M&M 29 Aug 20241SELL2650.752650.75
NSE PFC 29 Aug 20245BUY475.52377.5
NSE GUJGASLTD 29 Aug 202425BUY649.316232.5
NSE PFC 29 Aug 20241BUY476.85476.85
NSE SBIN 29 Aug 202425BUY800.320007.5
NSE BAJFINANCE 29 Aug 20245BUY6536.3532681.75
NSE SBIN 29 Aug 202450BUY80140050
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Sounds like you could make good use of pivot tables. Or am I getting it wrong?
What do you mean by "shares"?
 
Upvote 0
bobsan42 said:
Sounds like you could make good use of pivot tables. Or am I getting it wrong?
What do you mean by "shares"?
Click to expand...
shares means stocks. for example : Stock name Sail buy 25 @ 135, again Sail buy 15 @125 , then i want result Total Sail 40 with average price 131.25. This type of results i want .. thanks
 
Upvote 0
@Fluff hi, actually i trade in stocks so i have 10-15 stocks name which i trade frequently. For example i buy reliance stock qty 50@3000 and then again qty 25@2800. So i want in another sheet i can look total qty and average.. so results will be Name Reliance - qty 75 and average buy price - 2933.33 . for buy or sell i can manually choose drop down filter . Please look it above #1 excel post and guide me thanks.
 
Upvote 0
Please do not quote members in order to get their attention.
 
Upvote 0
Fluff said:
Please do not quote members in order to get their attention.
Click to expand...
okay i will not mention as you advise me. Actually i am not getting guidance from last 2-3 days so i mentioned your name. i was waiting, may be some answer my query, so i quote you. but i will take care of that . Also advise me if i did not get any answers from too long or several days . what i have option in forum to getting my answer . i am not vary familiar to this forum that's why i asking you.. Thanks
 
Upvote 0
If you do not get an answer then you can bump your thread, but we advise doing it no more than once every 24hours.
 
Upvote 0
Here's something to get you started:
Book1
ABCDEFGH
1ParticularQtyBuy/SellPricetotalOps CountQty SumAvg Price
2NSE SAIL 29 Aug 202425BUY135.703 392.50250135.77
3NSE GUJGASLTD 29 Aug 20245BUY643.503 217.50230648.3333333
4NSE TITAN 29 Aug 202411BUY3 347.7036 824.701113347.7
5NSE BHEL 29 Aug 202450BUY287.0014 350.00150287
6NSE BANKNIFTY 28 Aug 20241BUY49 899.9049 899.901149899.9
7NSE HEROMOTOCO 29 Aug 20241BUY5 187.705 187.70115187.7
8NSE SAIL 29 Aug 202425BUY135.843 396.00250135.77
9NSE BHEL 29 Aug 202425SELL287.807 195.00125287.8
10NSE HDFCBANK 29 Aug 202411BUY1 610.6517 717.151111610.65
11NSE M&M 29 Aug 20244BUY2 651.6010 606.40142651.6
12NSE M&M 29 Aug 20241SELL2 650.752 650.75112650.75
13NSE PFC 29 Aug 20245BUY475.502 377.5026475.725
14NSE GUJGASLTD 29 Aug 202425BUY649.3016 232.50230648.3333333
15NSE PFC 29 Aug 20241BUY476.85476.8526475.725
16NSE SBIN 29 Aug 202425BUY800.3020 007.50275800.7666667
17NSE BAJFINANCE 29 Aug 20245BUY6 536.3532 681.75156536.35
18NSE SBIN 29 Aug 202450BUY801.0040 050.00275800.7666667
Sheet1
Cell Formulas
RangeFormula
F2:F18F2=COUNTIFS($A$2:$A$18,A2,$C$2:$C$18,C2)
G2:G18G2=SUMIFS($B$2:$B$18,$C$2:$C$18,C2,$A$2:$A$18,A2)
H2:H18H2=SUMIFS($E$2:$E$18,$C$2:$C$18,C2,$A$2:$A$18,A2)/G2

However I think you should detach the dates from the item names.
 
Upvote 0
bobsan42 said:
Here's something to get you started:
Book1
ABCDEFGH
1ParticularQtyBuy/SellPricetotalOps CountQty SumAvg Price
2NSE SAIL 29 Aug 202425BUY135.703 392.50250135.77
3NSE GUJGASLTD 29 Aug 20245BUY643.503 217.50230648.3333333
4NSE TITAN 29 Aug 202411BUY3 347.7036 824.701113347.7
5NSE BHEL 29 Aug 202450BUY287.0014 350.00150287
6NSE BANKNIFTY 28 Aug 20241BUY49 899.9049 899.901149899.9
7NSE HEROMOTOCO 29 Aug 20241BUY5 187.705 187.70115187.7
8NSE SAIL 29 Aug 202425BUY135.843 396.00250135.77
9NSE BHEL 29 Aug 202425SELL287.807 195.00125287.8
10NSE HDFCBANK 29 Aug 202411BUY1 610.6517 717.151111610.65
11NSE M&M 29 Aug 20244BUY2 651.6010 606.40142651.6
12NSE M&M 29 Aug 20241SELL2 650.752 650.75112650.75
13NSE PFC 29 Aug 20245BUY475.502 377.5026475.725
14NSE GUJGASLTD 29 Aug 202425BUY649.3016 232.50230648.3333333
15NSE PFC 29 Aug 20241BUY476.85476.8526475.725
16NSE SBIN 29 Aug 202425BUY800.3020 007.50275800.7666667
17NSE BAJFINANCE 29 Aug 20245BUY6 536.3532 681.75156536.35
18NSE SBIN 29 Aug 202450BUY801.0040 050.00275800.7666667
Sheet1
Cell Formulas
RangeFormula
F2:F18F2=COUNTIFS($A$2:$A$18,A2,$C$2:$C$18,C2)
G2:G18G2=SUMIFS($B$2:$B$18,$C$2:$C$18,C2,$A$2:$A$18,A2)
H2:H18H2=SUMIFS($E$2:$E$18,$C$2:$C$18,C2,$A$2:$A$18,A2)/G2

However I think you should detach the dates from the item names.
Click to expand...
Hi thanks for reply. I also added insert date Col. before Col. A. Now average looks fine but when i filter date col. then H Col. avg price did not change according to visible rows filter. I think may be Subtotal type of formula should be used..
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Not able to correct filter data date wise, Kindly correct my formula...
Replies
1
Views
141
Anthony47
A
P
  • Question
How to obtain average values on columns filter
Replies
3
Views
179
StephenCrump
S
E
  • Solved
Count unique values with 2 conditions
2
Replies
12
Views
947
Fluff
Fluff
U
  • Solved
Index formula with matching - Can it look at rows and columns?
Replies
2
Views
333
usnapoleon
U
V
  • Solved
sum table based on rows and multiple years columns
Replies
2
Views
493
vampsthevampyre
V

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top