FINDING TOTAL NUMBER OF DAYS

[Skytalker]

I have actually submitted this problem to 2 other forums, but haven't had any luck solving it. If it is unsolvable, can someone please let me know. When I began working on this, I thought it was going to be easy...just a matter of a few IF formulas, but I couldn't figure it out. I am trying to learn Excel and I took this on as a learning exercise. Although I have learned quite a bit while researching the problem, I still haven't solved it. I thought I had come close using Arrays, but my laptop crashed and I lost my formula, along with 10 pages of other formulas that did not work. I keep track of each formula I try and the results. I have explained what I am attempting to accomplish in the file 12-01-. I am looking for ANY ideas how to figure it out. Thanks in advance.

P.S. I am using Excel 2019.

[#2]

@Skytalker 

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.

[#3]

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.

[#4]

@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.

[#5]

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.

[#6 · Accepted solution]

@Skytalker 

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!!

[#7]

@Rajesh_Sinha 

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.

[#8]

@Skytalker 

I was able to find some earlier work that I had thought I had lost when my laptop crashed when I dropped it, but I was able to find it and rebuild it. Anyway, I hope you can understand what I am trying to accomplish and can help.

[#9]

@Skytalker 

Glad to help you,,, I've tried my best to understand data illustration and your need and applied the possible method/formula. Furthermore I would like to suggest create one small but meaningful compact sample data and below it show me your expected result, I'll try one more time to fix it, keep asking ☺

[#10]

Rajesh, Did you attach a file to show me what you accomplished?

[#11]

Yes I did,, check my post above the file
0MAS-.xlsb has been attached !!

[#12]

@Rajesh_Sinha 

I checked the file you sent but I don't think you understand what I am trying to accomplish. Did you see my post that is 3 posts above your last post? I tried to explain what I was looking for. If possible, can you let me know if it makes what I need accomplished a little easier to understand. Thanks again for at least trying. I am trying to see if a mesh what you did into what I did, but I am still not doing very well. I just don't know enough about excel to get it done, but I will keep trying.

[#13]

@Skytalker 

I have got part of my problem figured out using helper columns, but I am still having problems. I would still like to figure out a way to combine all formulas into 1, but I guess I will just have to take what I can get. The problem I am having is highlighted in yellow, such as K7, L7, etc.

The first problem is with the formula in K7: {=IF($H7>$I7,INDEX($C8:$C$50,MATCH(TRUE,$C8:$C$50<$J7,0)),IF($H7<$I7,INDEX($D8:$D$50,MATCH(TRUE,$D8:$D$50>$J7,0))))}

This formula is based on which cell is higher, H or I. If H is higher, it takes the value in J7 and looks down column C (LOW) and finds the next lower number. If I is higher, it takes the value in J7 and looks down column D (HIGH) and finds the next higher number.

I was trying to come up with a formula based on the following factors:

1 - What is in column A (SYM)

2 - Whether H or I is higher. This determines what goes in J7, which goes in K7, which determines the number we look up in column C or D, which determines what date goes in L7, which we will subtract from G7.

The problem is that at K7, we are looking for a value in column C, which is lower than J7 (115.40), but there aren't any lower, until we come to C18, which has a value of "AA" in column A.

I am trying to figure out a way to do the index/match that ONLY uses the value in column A as the limit which the formulas searches in column C or D, instead of 50 in $C8:$C$50. I want it to stop at row 18 because "AA" is the value, instead of "A". Although I can manually change $C8:$C$50 to $C8:$C$17, I don't want to do that over 3000 times, which is constantly changing. If I can figure this out, I believe it will fix the rest of the errors.

Of course, if anyone can figure how to combine these formulas, I would be VERY happy, as I have been trying to figure this out for months! I have to use 3 helper columns to find each value that goes under the # of days, i.e., 1,2,3,etc. That means an extra 30 columns! 

If ANYONE is willing to try to help but are confused about what I am trying to accomplish, please ask me and I will strive to explain better.

[#14]

@Skytalker 

Should anyone care, A very smart individual, BEBO021999, from Vietnam, was able to apparently solve this problem with the following formula: 

=IFERROR(INDEX($G:$G,AGGREGATE(15,6,ROW($A3:$A$13)/($A3:$A$13=$A2)/($D3:$D13>=$B2+IF($I2>=$H2,P$1,-P$1)),1))-$G2,"")

I am checking it with my files to see if works correctly, but so far it has done everything I was asking for. This is from another forum at: https://www.excelforum.com/excel-formulas-and-functions/1331382-number-of-days-it-took-a-stock-to-ri...

Thanks to ANYONE who tried to work on a formula that did what I requested.