Trouble with formula when updating range - I'm 'losing' data. Any ideas?

Hi all,

I received great help in an old thread of mine to create an Excel sheet that uses the frequency function to count the number of runs in a data set.

Initially, it was set up for the predefined range, A9:1596, and worked just fine. I count the number of days and summarize to make sure my calculated values equal what's actually in the set.

Today, I wanted to update the sheet for say A9:A10000, such that I can refresh the sheet with new data and always make sure the sheet calculates newly added data.

But, for some reason I get an error when doing this and I "lose" 1 run, i.e., I get 15 runs of 5 instead of 16. Furthermore, it seems to happen only for the value 5, for some reason. That is cell D13 in the attached sheet.

I'm attaching the sheet as it is when correct. I'm not too familiar with this function, but I've tried analyzing the formulas using both approaches and I can't seem to figure out the difference or what's wrong? For the negative consecutive days, it does not seem to be a problem...?

To clarify, I try to change this to A9:A1596 to A9:A10000, but it does not work.

Any ideas?

The issue is a value of 0 (zero) is included in a "positive" run.


=SUM(IF(FREQUENCY(IF(A$9:A$1597>=0,ROW(A$9:A$1597)),IF(A$9:A$1597<0,ROW(A$9:A$1597)))=D14,1))

If you change 1596 to 1597 then 5 count becomes 15 BUT 6 count becomes 9: this is because 1592:1596 are all positive giving a count of 5 BUT if the range is extended by 1 (1597) this count becomes 6.

Use "Formulas"==>"Evaluate formula" and you see this happening.

Thanks, John.

It seems as if the range is extended by 2 or more, those 'positives' are not counted?

Regardless, would you have a suggestion for how I can accomplish what I mentioned in my first post, i.e., having the formula applicable for a larger (dynamic) range?

Or do I simply need to update the formula for the precise range whenever I update sheet with new data (giving more/less days)?

I suppose a solution for what I asked in post #3 would be to use the OFFSET function as I was introduced to in this thread.

I could then:

1. Count the number of cells in A:A containing numbers. Let's say this is 1588 as it is now and this is counted in cell B5.

2. Use the OFFSET function and reference cell B5.

Or is there an easier way?

Thanks, shg,

I have to admit I don't quite understand how to implement that. Would it be too much to ask if you could type it in on the sheet I uploaded?

If not, no worries. I think my OFFSET solution could work also.

Hi, shg,

I really appreciate it, but have to say I didn't get it to work. It might be that I don't get the formula quite right since I'm in Norway and can't copy and paste directly. I don't quite get what it's supposed to accomplish either, which makes it harder for me to understand, I guess.

It's the first time I use/attempt to use Name Manager.

See the attached:

Formulas==>Name Manager

You will Shg's definition for named range called "Change" i.e,

=INDEX(Sheet1!$A:$A, ROW(Sheet1!$A$8) + 1):INDEX(Sheet1!$A:$A, MATCH(9E307, Sheet1!$A:$A))

In E9

=SUM(IF(FREQUENCY(IF(Change>=0,ROW(Change)),IF(Change<0,ROW(Change)))=D9,1))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy down

NOTE: should it be ">=" or just ">" ?

Thanks a lot, John. Really appreciate the help. You guys are very helpful.

The thing is, I don't quite understand what you've done, but maybe I should just accept is as a solution and let it be with that.

Anyway, it's now set up such that the range under Change (A9:Axxxxxx) can be as large as I want (within practical boundaries of course)? In other words, if I now put in 10.000 days of data, it should work just fine?

I note what you're saying with regards to the >=0. One could argue that perhaps the zero shouldn't be counted, so I do kind of agree with that. Maybe I should change it to simply >0.

Thanks again!

Actually, solving this sheet raised another question.

All the cells under Change (A9) represent a single day in the stock market. As such, I could add the date for each day in column B9.

Would there be a (easy) way to be able to find these runs of data in Excel?

Runs of 1, 2, 3 or even 4 in a row might not be THAT interesting since they happen quite often. But, it would be interesting to for example be able to ask and locate where in my data set these runs happened?

For example, there have been 2 runs of 8, i.e., 8 days of positive prices in a row. Could I be able to find when this occurred?

Thanks for any help. Even if that help is confirmation that this would be a hard task to accomplish.

Hi, JeteMC,

Thank you VERY much! This is fantastic. And exactly what I wanted.

But having looked at it, I'm curious if there is an error somewhere or if I misread something?

For example, choosing value "3" in cell F 21 for 7 consecutive positive days, I get 5 dates, i.e., 5 runs. And they do appear to be correct by looking them up manually.

But in my other table, it say that there are only 3 occurences of 7 consecutive positive days.

Any ideas...?

Originally Posted by JeteMc

My visual inspection, aided by the conditional formatting in column D, is the same as yours, there are 5 runs of seven consecutive days of positive values in column A.
I would note that using ">=0" will bring that number to 6.
Putting the following into cell F15 yields the same values: =COUNTIFS(D$9:D$1596,E15)
I hope that is helpful.
Let us know if you have any questions.

Hi again, Jete,

Yes, it seems like that is correct, assuming your formula/count in column D is correct? Could it be that since your formula counts 'backwards' it misses some of the end points in the set?

Or are you suggesting that my other formula for counting runs is incorrect? That formula do add up all the days correctly, so I'd imagine it to be correct, but all of this is a little above my head to be honest.

Originally Posted by JeteMc

My feeling is that a count of the number runs of 7 consecutive positive values will be the same regardless of whether the dates increase as you ascend or descend the column. For example the first run of 7 is from row 353 (1) to row 359 (7). If the numbers in column D are reversed it is still a run of seven.
I am not sure that I understand the part about adding up all of the days correctly.
I would encourage visual inspection of some of the other values, as in changing F21 to eight (yields two dates) and then six (yields 14 dates).
Let us know if you have any questions.

Hi, Jete,

Appreciate your patience.

1. With adding up all days correctly, what I mean is that I've done some arithmetic to make sure that my count of runs add up correctly:

a. In B3 I've counted all positive days.

b. Then, in column H9-H18 I've summarized all the runs. For instance 3 runs of 8 sum up to 24 days.

c. Summarizing column H, I get 871 days which is what I have in B3 as well.

This has led me to believe that the formula and count is correct.


But, the way you are counting it does not seem to align with that? So, not sure which is right or wrong?

Hi, Jete,

Thanks again for your continued patience and assistance.

It's now indeed the same in your formula and in my original table, but it does not seem to be correct for runs less than 5...?

5 positive consec. days = 16 on both, but I choose 4 consec. days, I get 22 days with your formula and 38 runs with mine.

Any ideas...?

Best regards.

Great! Now it works just perfect. Thanks a lot, JeteMc.

Hi again, JeteMC,

I was trying to copy this sheet to another workbook of mine in order to have every related data in the same workbook.

However, this leads to Excel being veeeeery slow whenever I try to manipulate any of the data in this sheet and if I try to save the workbook, Excel actually freezes and crashes. I've tried re-booting, etc., but it doesn't help.

So, I decided to just stick with the original sheet.

However, it seems like I'm getting the same issues trying to manipulate the sheet in the original workbook?

What I mean is that I'm trying to delete both Change/Date in column A/B to refresh it with new data. Just doing this or rather trying to do it makes Excel freeze and I have to "kill it".

Furthermore, I was hoping to have data in Change and Date linked to similar named columns in sheet "Data", but I haven't been able to get to that step yet.

Any ideas on what's going on here?

Thanks in advance.

PS: I also extended the rows of data all the way down to 5000.

Attaching the updated sheet.

Hi, JeteMC,

Thanks a lot!

2500 rows would probably be okay, although I'd prefer to have 5000 and even more. Sadly, I'm still not able to save this sheet. I tried saving it without any additional changes.

Are the formulas on this sheet particularly computer intensive...? I have many other sheets at 5000 rows. At least one of them slightly complex also.

Maybe I'll just have to stick with the old sheet and update manually (the idea now is that when I update raw data with copy and paste from another sheet, my other sheet(s) will update accordingly). But will I run into problems also even if I extend to 5000 rows on the old sheet and no other changes...?

As for computer, my current one is slightly old and I'm migrating to a new one in a few days. But this seems to be a little beyond just computer power. Or maybe not.

Hi, JeteMC,

We could do that.

But, to be clear, I am using Excel 2016 at the moment, so should be fairly up to date, I think?

Hi, JeteMc,

Appreciate your help as always!

If it's not too much trouble, could you input the formula and attach the sheet?

The reason I'm asking is that I'm in Norway and formulas are written in Norwegian on my Excel. So, copy and paste doesn't work. The formula seems complicated enough that I'm afraid I won't be able to reproduce it correctly on my own.

Thanks in advance.

Best regards,

Elijah

Hi, JeteMc! Thanks a lot!

The first time trying it did save, but it did take like a minute or so to compute/save. For some reason, it's faster now. So, problem solved, I think!

Excellent.

I was hoping to get some additional help from JeteMc on a similar sheet first, Ali.

JeteMc,

I have a sheet which does a similar count on some data using text values. I was hoping to update it to include the "Show dates with n days in a row" feature that you helped me with on the sheet in this thread.

I did try to update it accordingly and make adjustments, but it does not seem to be quite correct.

Any ideas where I made a mistake?

EDIT: Formatting is a little messy, but focused on formulas first.

Seems to work great, JeteMc! Thanks again!

I haven't had time to sit down with it and complete it, but it does appear to work as it should now. I'm going to put this thread back to solved now...

Have a great day and a great holiday!