Forum Discussion
Trying to make my Stock Spreadsheet work
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.
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? )
Minghao,
That is as Matt explained.
1/(A2='Transactions(SG)'!$C$2:$C$24)
is the filter which returns 1 if the company name matches, an error otherwise. Multiplying on date and have the list of dates for that company with errors for other companies. AGGREGATE returns the latest date ignoring all errors. Similar way you may add more filters (e.g. for Buy/Sell).
For better understanding you may check how the formula works clicking in ribbon on Formula->Evaluate formula calculating it step by step.
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.
I still do not understand why there is a need for minus ROW('Transactions(SG)'!$J$2:$J$24)+1) in the formula.
I realise that after the SUMPRODUCT(MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24))) gives me the highest Prev row, the portion above is basically -2+1. Could I just enter a generic -1 instead of the formula above?
