May 01 2018
03:33 AM
- last edited on
Jul 25 2018
11:53 AM
by
TechCommunityAP
May 01 2018
03:33 AM
- last edited on
Jul 25 2018
11:53 AM
by
TechCommunityAP
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.
May 01 2018 05:17 AM
SolutionHi 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
May 01 2018 07:57 AM
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")
May 01 2018 08:30 AM
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
May 01 2018 08:39 AM
Thanks. It makes sense
May 01 2018 05:17 AM
SolutionHi 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