dj_chris_p
New Member
- Joined
- Oct 26, 2015
- Messages
- 6
I have dynamic streaming data coming into an Excel sheet.
Let's say the cell where this data is coming into is A1
What I want to do, is count how many times the value of A1 is greater than 5.
I don't mind if this is done either as a counter which increases by 1 every time the condition is met; or if a cell prints a "1" every time the condition is met, and then continues in the cells beneath, so just ending up with a long column of "1"'s which I can then Sum.
I have no idea how to do a counter.
For the second idea, I have created this formula which returns a value of "1" in A2 if the condition is met: =IF(A2="1","1",IF($A$1>=5,"1",""))
This checks if the condition A1>=5 is correct, then returns a value of 1 if it is. The circular reference means that the "1" then remains in A2.
I just don't know how to repeat this in the cells A3 and below to continue the process. I've tried something like this: =IF(A3="1","1",IF(AND($A$1>=5,"1",""),A2="1")) , but it obviously isn't quite correct. The logic should go something like this: IF A1>=5, AND A2="1", then return "1".
This all has to be done using Excel formula rather than VBA, as the spreadsheet is part of another system which uses Excel functions in its spreadsheets.
Also, a thought... If A1>=5, I don't want all the cells below to suddenly return values of "1", which could happen in my mind if they all check at the same time, so is there a way to put a time delay on the next cell down to prevent this?
Let's say the cell where this data is coming into is A1
What I want to do, is count how many times the value of A1 is greater than 5.
I don't mind if this is done either as a counter which increases by 1 every time the condition is met; or if a cell prints a "1" every time the condition is met, and then continues in the cells beneath, so just ending up with a long column of "1"'s which I can then Sum.
I have no idea how to do a counter.
For the second idea, I have created this formula which returns a value of "1" in A2 if the condition is met: =IF(A2="1","1",IF($A$1>=5,"1",""))
This checks if the condition A1>=5 is correct, then returns a value of 1 if it is. The circular reference means that the "1" then remains in A2.
I just don't know how to repeat this in the cells A3 and below to continue the process. I've tried something like this: =IF(A3="1","1",IF(AND($A$1>=5,"1",""),A2="1")) , but it obviously isn't quite correct. The logic should go something like this: IF A1>=5, AND A2="1", then return "1".
This all has to be done using Excel formula rather than VBA, as the spreadsheet is part of another system which uses Excel functions in its spreadsheets.
Also, a thought... If A1>=5, I don't want all the cells below to suddenly return values of "1", which could happen in my mind if they all check at the same time, so is there a way to put a time delay on the next cell down to prevent this?