Forum Discussion
Trying to make my Stock Spreadsheet work
I am trying to make my stock workbook by having 2 sheets. One where I enter transactions and one where I can see a stock summary.
I will enter information in my sheet "Transactions(SG)" and I would like to see the changes in the "Stock Summary(SG)"
I have a problem with column G and H in "Stock Summary(SG)". What I am trying to achieve for column G is to reflect the current units I have. This should come from "Transactions(SG)", after I find the latest entry of the stock in Transactions and then take the CumVol for that row. I cannot get a formula to reflect what I want.
For Column H, I want the same as Above but for the CumCost.
Can someone assist. Thanks
Try updating your formulas to these:
G2: {=IFERROR(INDEX('Transactions(SG)'!$J$2:$J$24,SUMPRODUCT(MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24)))-ROW('Transactions(SG)'!$J$2:$J$24)+1),"No Transactions")}
H2: {=IFERROR(INDEX('Transactions(SG)'!$O$2:$O$24,SUMPRODUCT(MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24)))-ROW('Transactions(SG)'!$O$2:$O$24)+1),"No Transactions")}
Please see attached file for additional reference. You may need to change the "No Transactions" portion of the formula to 0 to avoid error messages. This will happen if there is no transaction associated with the symbol.
9 Replies
Try updating your formulas to these:
G2: {=IFERROR(INDEX('Transactions(SG)'!$J$2:$J$24,SUMPRODUCT(MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24)))-ROW('Transactions(SG)'!$J$2:$J$24)+1),"No Transactions")}
H2: {=IFERROR(INDEX('Transactions(SG)'!$O$2:$O$24,SUMPRODUCT(MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24)))-ROW('Transactions(SG)'!$O$2:$O$24)+1),"No Transactions")}
Please see attached file for additional reference. You may need to change the "No Transactions" portion of the formula to 0 to avoid error messages. This will happen if there is no transaction associated with the symbol.
As variant if not to use array formula this works for G
=IFERROR( INDEX('Transactions(SG)'!$J$2:$J$24, MATCH(
AGGREGATE(14,6,1/(A2='Transactions(SG)'!$C$2:$C$24)*'Transactions(SG)'!$A$2:$A$24,1),
'Transactions(SG)'!$A$2:$A$24,0),
0), "No Transaction")similar for next column
Could you explain the
1/(A2='Transactions(SG)'!$C$2:$C$24)*'Transactions(SG)'!$A$2:$A$24 part of the formula? )
Thanks a lot. It works.
However, appreciate if you could explain to me how
SUMPRODUCT(MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24)))-ROW('Transactions(SG)'!$J$2:$J$24)+1) gives you the row of what I want. How does the SUMPRODUCT work here? I understand the MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24) which gives me the highest row that the name appears. but the rest I do not really understand.
I understand the MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24) which gives me the highest row that the name appears. but the rest I do not really understand.Pretty darn close..... The way the name is evaluated in the SumProduct() function is a 1 or 0 based on whether the value is a match. Then this 1 or 0 is multiplied by the row that this value appears on. So 1 x Row Number..... The higher the row number the higher the value, which is why the Max function is able to tell us which Row is the highest number row that also contains the matching name criteria.
