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.
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?
Hello-
You are correct you may use the -1, as such:
={IFERROR(INDEX('Transactions(SG)'!$J$2:$J$24,SUMPRODUCT(MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24)))-1),"No Transactions")}
I used an old formula from another workbook and updated it to fit your references. Not sure, but I believe the portion you are asking to omit was used to offset different data structures. i.e. the header rows may have started from different rows so there was a need to adjust the Index reference.