Forum Discussion
Array Formula, If, Index? How to make my formula work to get what I need?
I have 2 sheets. Sheet 1 is Transactions and Sheet 2 is stock summary.
Under Transactions, I have entries that look like
A B C D E F
1 Action StockName Vol Prev Row Prev Vol CumVol
2 Buy Stock 1 3000 0 0 3000
3 Sell Stock 1 3000 2 3000 0
4 Buy Stock 2 1000 0 200 200
Under Stock Summary, there is a cell that I would like to indicate the CumVol I have for a particular Stock.
A B
1 StockName Units
2 Stock 1 Formulae needed
In Bold is the formula I would like to enter. I am trying for a formula that will looking for all entries with "Stock 1" in Sheet "Transactions", Take the Max of Column "Prev Row" for those rows with "Stock 1" and Show "CumVol" in Cell B2. However, I do not know which formula works. I have tried a few formulas.
{=(IF(MAX((IF(StockName=B17,PrevRow))),CumVol))}.
The problem with my formulae is that using the eg above, it gives me 3000 instead of 0 that I am trying to achieve.
Hi Minghao,
Please try this formula and find it in the attached file:
=IF(ISNUMBER(MATCH(A2,Transactions!$B$2:B4,0)),INDEX(Transactions!$F$2:$F$4,MATCH(MAX(IF(A2=Transactions!B2:B4,Transactions!D2:D4)),Transactions!$D$2:$D$4,0)),"")
Regards
4 Replies
Hi Minghao,
Please try this formula and find it in the attached file:
=IF(ISNUMBER(MATCH(A2,Transactions!$B$2:B4,0)),INDEX(Transactions!$F$2:$F$4,MATCH(MAX(IF(A2=Transactions!B2:B4,Transactions!D2:D4)),Transactions!$D$2:$D$4,0)),"")
Regards
Really appreciate your help.
=IF(ISNUMBER(MATCH(B17,StockName,0)),INDEX(CumVol,MATCH(MAX(IF(B17=StockName,PrevRow)),PrevRow,0)),"NA") works.
For my understanding, I did not understand the need for the MATCH and used the formula below instead. Am I missing something?
=IF(ISNUMBER(MATCH(B17,StockName,0)),INDEX(CumVol,MAX(IF(B17=StockName,PrevRow))),"NA")
Hi,
You have to keep the second MATCH because it's responsible to deliver the correct row number for INDEX.
MAX gives the value itself, but MATCH gives the relative position of this value.
The relative position represents the required row number.
I hope this makes sense
Regards
