Forum Discussion
FINDING TOTAL NUMBER OF DAYS
On the basis of illustration by you above and what I've realized, trying to solve the issue, check the attached file and let me know whether is working or not!!
I've examined both the sheets and came to the conclusion that 12-01 is summary of MAS, if so there is no match between source data (MAS) and the summary (12-01),,, let me say Y,,, lowest for A is MAS is 116.74 & highest is 119.23 so unable to understand that how U got values in Row 2 of 12-01,,, and the date 12-1 in G2 is clueless !!
The exercise you are trying to accomplice are not properly described & reflects a complicated questions!!
So my suggestions are,,
Show us Few Source data and the Expected results then finding difference of days will become logically much easier ,, for example this is what "#1 - Find if A2 is in OMAS= or not. If not, then leave the cell blank. If it is, then:"
What I've realized that, you are trying to fine the lowest and highest of each symbol, also the difference of days between lowest and highest and possibly the difference between the lowest and highest,,, if so pleas reply this post.
Thanks for replying Rajesh-S. I think that may be my biggest problem...not doing a very good job of describing what I want. I just found out that OMAS- file was incorrect, so I corrected it and uploaded it in my original post, so if you downloaded my original file, please download the updated file. I will try to explain what I am trying to accomplish:
in cell K2 of 12-01- :
SYMBOL = A; H2 is less than I2, so if B2+1 (116.36 or higher) is found in Column D in OMAS-, which it is in cell D6 (116.44), I take the date in G6 and subtract G1 from 12-01-, thus 12/07 - 12/01 = 6 days, which goes in K2.
Does this make it any clearer? Please advise me of which part you are having difficulty understanding and I will try my best to explain it. Also be advised that the original OMAS file is over 287K and the 12-01 file is 676 rolls. I don't know if that will make any difference or not.On the basis of illustration by you above and what I've realized, trying to solve the issue, check the attached file and let me know whether is working or not!!
Thanks a lot for spending your time trying to solve my problem. I was trying to use helper columns also, but I just couldn't get my formulas to work correctly. You ALMOST understand what I was trying to accomplish, but not quite:
L2 is correct. If H2 is lower than I2, I want to buy stock, BUT I want to see how many days it took for the stock to reach benchmark, i.e., 116.36 = 1, 117.36 = 2, 118.36 = 3, etc. Once the stock got above 116.36, which occurred on 12/7, I want to subtract 12/7-12/1 (G2), which is 6. Once it got above 117.36, which it did on 12/20, it would be 9 (G9-G2), etc.
I like what you did in O2, =IFERROR(DATEDIF($G$2,N2,"d"),""). I'm going to see if I can find my old files with my helper columns and see if I can get it to work with them. If you are still confused, please do not hesitate to ask me.
Again, thanks so much for taking time out of your day to help.
Skytalker Played around with Power Query a bit. Although your example looks for 15 different increases/decreases (from 1$ to 15$), I created a set of queries that gives only the first three. If this is indeed what you want, you can expand the "mySelection" query to add 12 more calculations.
To keep it all a bit easier to work with I copied the OMAS table into the 12-01 workbook.
The end result looks like this:
Results are grouped depending on whether you want to count up (+1,+2,+3) or down (-1,-2,-3) and the number of days is always given as a positive. So it's not exactly what you asked for, but in principle its the same.
Now, I did not try to make it beautiful. Just wanted to demonstrate that it might work with Power Query. Perhaps a real PQ wizard can achieve a better result. But that's not me.
- Thanks so much Riny. I will check it out. I tried using Power Query and couldn't get it to work, but you seemed to have gotten further than I did. I will see if I can improve on your work. Thanks again.
