Need help creating a Counter or descending IF list

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?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
but the countif will re-calculate when the cells are populated.

True, but I'm stuck at the stage before that :) So I need to populate those cells with the values. I can manage to get A2 to work, but it's A3 onwards that I'm stuck on.

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".
 
Upvote 0
True, but I'm stuck at the stage before that :) So I need to populate those cells with the values. I can manage to get A2 to work, but it's A3 onwards that I'm stuck on.

[/COLOR]

ok that is part of your "second part". Lets work on that.

So you have a value in A1.

you want A2 to return a 1 if A1 is greater than 5

this goes in A2
Code:
=IF($A$1>=5,1,"")

In A3 you want it also to show a 1 if A1 is >= 5. If that is all just copy the formula down. IF not then I need to understand what else A3 is checking against. Do you only want it to check A1 >= 5 AND A2=1?

Code:
=if(AND($A$1>=5,A2=1),1,"")

If that is not it then I need more info
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top