• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count consecutively events greater than zero or equal to zeros

herclau

Member
Hello,
I need your help.
In the attached file I show on the Sheet(Expected Result) a summary example of the input data to the problem and what would be the expected solution.
on Sheet(Data) there are three columns with the data where the solution would apply. The columns have, in this case, 65534 data each.
62417
 

Attachments

herclau

Member
Care to explain how you arrive at the "expected result"?
What exactly are you summing or counting?
I did it manually. 1 for the first event in the first row. It does not depend if it is zero or greater than zero. then if the event is greater than zero, the consecutive one will be maintained. If zero increases the consecutive
In the attached I show my attempts. which is valid for when the first event is zero; But it doesn't work when the first event is greater than zero.
 

Attachments

GraH - Guido

Well-Known Member
Somehow, you have confused me even more. You have not finished your sentence "If zero increases the consecutive..."

In your first attachment you have 3 single columns of data: ActualFreq, SystemPower, Fanspd.
You have 2 "expected result" tables with values and counts.

1. Where do those values come from? Do they relate to the data columns at all?
2. How do you count them manually -> explain please, don't give formulae: e.g. why is the first 45 counted as 2 and the second 45 as 3?

62429

And finally, why do you oppose to using IF/IFS? Those are as common and basic functions as they come...
 

herclau

Member
For each column I will independently apply the resulting function. The example on the first page is to test the function achieved.
In all honesty, after many trials, I don't know what function to choose.
In the example that I made manually, 45 was to observe that the consecutive value of the event was maintained for values greater than and different from zero.
Columns A and B:
In column A the first event is zero, in B it will be 1,
in the second event of column A it is zero, it is not different from the first, column B is kept 1 ..., in row 5 of column A the event is greater than zero, so in column B we add one, obtaining 2. This value is maintained, for all values greater than zero, until the next zero, Row 9; that we add 1 to the value of column B obtaining 3 .....
In columns C and D the same procedure is applied, only we do not start with zero. in this case the function (N (C1) + 1-ABS (SUM (($ A1 = 0), - ($ A2> 0)))) does not work correctly. Example 3
 

Attachments

herclau

Member
In this file I don't see the function applied in the columns (ActualFreq SystemPower FanSpd) on the Data sheet.
The calculation takes almost 1 minute !!! But it is the expected result!
 

herclau

Member
In this file I don't see the function applied in the columns (ActualFreq SystemPower FanSpd) on the Data sheet.
The calculation takes almost 1 minute !!! But it is the expected result!
And the applied function behaves like a volatile function, every time I open the data sheet, it initializes the calculations again, and you have to wait almost a minute.
 

vletm

Excel Ninja
herclau
There are none functions to see in 'Datos'-sheet.
For me, it takes about 21seconds ... yes, You're correct -that's less than one minute. Hurry?
As I wrote, this version gives results to 'Datos'-sheet values after You select 'Datos'- sheet - every time!
Do You have any wishes, how would You like to start calculation?
If none ... then test this
 

Attachments

Last edited:

herclau

Member
I have arrived at this solution. It is fast and I have not used volatile functions!
But at times I receive the "not responding" alert.
Any comments that can help me know why this!
62484
 

Attachments

Last edited:

vletm

Excel Ninja
herclau
If someone would like to use ~196605 time same ... formulas ( I couldn't check actual number of those ) then You should have other kind of PC.
 

AndiLovesExcel

New Member
Helo herclau,
my understanding is you want to increase the counter ("expected result") by 1 each time the value changes to zero or <> zero.
This you want to apply to three columns in "Datos", in a non-volatile manner.

My suggestion for your sheet "Expected results" which you'd need to apply to your actual columns in the other sheet:
C2 = 1
C3: =C2+1*AND(A2*A3=0;A2+A3>0)
If negative figures should also occur:
C3: =C2+1*AND(A2*A3=0;ABS(A2)+ABS(A3)>0)

Regards
 

herclau

Member
Helo herclau,
my understanding is you want to increase the counter ("expected result") by 1 each time the value changes to zero or <> zero.
This you want to apply to three columns in "Datos", in a non-volatile manner.

My suggestion for your sheet "Expected results" which you'd need to apply to your actual columns in the other sheet:
C2 = 1
C3: =C2+1*AND(A2*A3=0;A2+A3>0)
If negative figures should also occur:
C3: =C2+1*AND(A2*A3=0;ABS(A2)+ABS(A3)>0)

Regards
updating the file with your recommendation, it runs faster. I would like it, if possible replace the Column () function. I have my doubts whether or not it is a volatile function!
62533
 

Attachments

AndiLovesExcel

New Member
For each of the 66k cells with =Status you have 14 formula calls and 8 data references. That's almost 1 mil formulae and 0.5 mil data references. I currently can't tell whether an individual one is a bad guy or it's just the mere quantity of them.
Not knowing about the bigger picture of how the file is created, processed and referenced, may I ask whether you have considered converting the table into a range and use the basic formula as stated above?
You could still define a named range over the used data.
Regards
 

Peter Bartholomew

Well-Known Member
An unusual feature of your problem is that the relative referencing concepts of the prior row and the column to the left have some significance. When it comes to optimisation for speed, some simplification of the formulas would seem to be called for. If one switches to R1C1 notation (File-Options-Formulas) to better express relative references, then 'actualR', 'priorR' and 'ValueS' could be simplified to
=Datos!RC[-1]
=Datos!R[-1]C[-1]
=Datos!R[-1]C

respectively.

None of the other formulas appear to introduce particularly large computational overheads, though one possible idea I might evaluate is to omit the first row check in 'Status' and instead trap the condition as an error when it occurs
= IFERROR(ValueS + combinedCriteria?, 1)

Note: The calculation does not depend upon the use of R1C1 notation; it is simply a clearer way of expressing relative references.
 

Attachments

herclau

Member
An unusual feature of your problem is that the relative referencing concepts of the prior row and the column to the left have some significance. When it comes to optimisation for speed, some simplification of the formulas would seem to be called for. If one switches to R1C1 notation (File-Options-Formulas) to better express relative references, then 'actualR', 'priorR' and 'ValueS' could be simplified to
=Datos!RC[-1]
=Datos!R[-1]C[-1]
=Datos!R[-1]C

respectively.

None of the other formulas appear to introduce particularly large computational overheads, though one possible idea I might evaluate is to omit the first row check in 'Status' and instead trap the condition as an error when it occurs
= IFERROR(ValueS + combinedCriteria?, 1)

Note: The calculation does not depend upon the use of R1C1 notation; it is simply a clearer way of expressing relative references.
Hello,
Thank you for your guidance and lesson.
It really returned my breath !!! The excel sheets I work with; They can have up to 92 columns with 65536 rows. And following the directions of readings made we use tables!

Looking for possible solutions in the Forum, in some examples I saw that the notation R1C1 is used. But until now I have not been able to introduce it in the "Name Mananger". As I show in the following figure:

62566
 

vletm

Excel Ninja
herclau
92 * 65 536 = 6 029 312 cells to get results and
someone would ... use formulas ... and get results very quickly ... yes!
 

Peter Bartholomew

Well-Known Member
@herclau
Yes, that is it. All that it is doing is switching to an older notation for referencing cells; they are the same cells with the same formulas, though they will appear different. To work, the convention you use to reference a cell must be the one you have selected in options. If you change the option, Excel will change the manner in which it displays existing formulas.

R1C1 Notation.
R1C1 itself is an absolute reference to the cell in the 1st row and 1st column, that is $A$1.
With square brackets, the reference becomes relative, so R[-1]C[-1] is one column to the left and one row above the active cell. To describe this relationship using the A1 notation, one has to define both the active cell and the referenced cell. For example, a formula in cell C5 would show B4, in this case, and it continues down with C6 referencing B5 etc. The other slight catch is that R[0] and C[0] are abbreviated to R (the current row) and C (the current column) respectively. Using the A1 notation these would be 6:6, B:B etc.

Working backwards from your name listing
actualR...Datos!H15 implies the formula was written in cell I16
whereas
prior...…..Datos!H18 implies that formula is written in cell I19
Whoever thought the A1 notation was simple :)

Do bear in mind @vletm's point that you are pushing the limits of what might be reasonable to expect Excel to perform. It used to be the business data analysis community that pushed those limits but the 'slicing and dicing' of large datasets has now largely moved to Power Query and Power Pivot.
 

vletm

Excel Ninja
Peter Bartholomew
I'm not pushing ...
If someone should run 100m or marathon then there would be differences how to do that or how?
... and if expected time would to get based from 100m time (or something like that) then
... that would be the World Record!
( Even =A1+A2 could be useful to use, instead of ... something which would look more ... better? )
 
Top