Formula help for creating portfolio

I am trying to create automated table which should updated basis the raw data entry. I have attached the workbook for working, I need help in getting formulae for YELLOW highlighted cells.

Also for the stock name, which should not be repeated from the rows above them.

It would be great help for me. Thanks!

Rushi.

Try this with two helper columns to simplify the formula

Helper 1 marks the unique Stock line with a row number or zero

Formula:

Please Login or Register  to view this content.

Helper 2 gives you the row number for unique row number

Formula:

Please Login or Register  to view this content.

Use that row number to access the Stock name

Formula:

Please Login or Register  to view this content.

USE SUMIF or SUMPRODUCT for the other formulas

See the file

Thanks Jose for such a quick reply!

I have few points, It would be great if you could address those:
1. In your solution in column I (i.e. Helper_2) from row number 44 formula shows error #NUM!. Will it create any problems as the number of entries will increase?
2. Column L should add only +ve of the buy values, but here it adds both & at the end the sum shows "0" if I had sold those stocks. Is it possible to add only +ve number & similarly in column P it should add only -ve values?
3. Similarly for SUMPRODUCT, suggest formula if I want to take only SUMPRODUCT of the only +ve buy quantity & corresponding buy rate. Also same for SUMPRODUCT of -ve buy quantity & & corresponding buy rate (be it -ve number).

Thanks,
Rushi.

Thanks AVK for your help. I will go through the sheet, Will come back to you if anything is required.

Hello @avk,

Everything is fine in the solutions what you have proposed except column M & column Q. This are calculating simple average rates whereas I need Weighted Aaverage ((+ve buy quantity*Rate)/total +ve buy quantity). Similarly for the -ve buy quantities as well.

Thanks for your time and efforts, really appreciated.
Rushi.

Hi
You can avoid that with this formula in I2 (Helper_2)

Formula:

Please Login or Register  to view this content.

@avk,

Please help me with Weighted Average.

Thanks

See if this is what you want in M3 and drag down

Formula:

Please Login or Register  to view this content.

By the way what means +ve?

=IFERROR(SUMPRODUCT(($B$2:$B$182=$K3)*($D$2:$D$182>0),$D$2:$D$182*$E$2:$E$182)/SUMIFs($B$2:$B$182,$K3,$D$2:$D$182,">0"),0)