How do you count how often a specific value occurs in single changing cell?

[blackjack]

It seems simple but I can't figure it out and can't find it on google. How do you count how often a specific value occurs in single changing cell? I thought =countif would work but everytime the cell changes it takes the count back to 0. I'm using random numbers and would like to count each time the number is below .1.

Example
A1 +RAND()
A2 =Countif(A1<.1),1

[gebobs]

The equation in A2 should be:

=countif(a1,"<.1")

I've tested it and it seems to work fine for me. Is your recalculation set to automatic?

[blackjack]

Originally Posted by gebobs

The equation in A2 should be:

=countif(a1,"<.1")

I've tested it and it seems to work fine for me. Is your recalculation set to automatic?

Thank you for trying but it doesn't work. I need it to count each time the condition is met, the formula resets back to zero after the field changes.

Either this is impossible or it's something simple. No idea.

[gebobs]

Perhaps I don't understand. By "after the field changes" do you mean "after the result in A1 changes"?

If so, it works fine for me. When I double-click in another cell (other than A1 and A2), the rand() formula recalculates. If the result is less than 0.1, then the result in A2 becomes 1. Otherwise, the result in A2 is 0. A2 will equal 1 about 10% of the time.

Here is a my file. I made the condition "<0.5" so the condition will be met more often. If I am misunderstanding, please clue me in.

[blackjack]

It's my fault I'm not explaining this very well so I'll try a couple of ways.

I need a running total every time the random number in A1<.1. I need it to add 1 each time the random number is less than .1. The reset to 0 is what I'm trying to avoid. I'm looking for a way to have A2 add to itself each time the condition is met.

example
A1 .06, A2 1
recalculate A1 .4, A2 1
recalculate A1 .09, A2 2

[gebobs]

If I'm not mistaken, that will require code and I'm a rusty on my Excel VBA, order of events, etc. I think something like this would work.

Add a button. When pressed, run the code:

Check value of A2 and store in variable, A2Value.
Recalculate
A2=A2+A2Value
End sub

Perhaps repost this in the Excel Programming forum. I'm not sure if it will get any more looks here now that I've responded and bungled it all.

[gebobs]

I'm guessing simply having a list of rand() functions and counting them all up would not suit your needs.

[blackjack]

Sadly I know nothing about VBA nor how to write it so I'm struggling to get what you posted into usable form. I'm getting errors. Below is my attempt... Can you help straighten this out?

Sub FatigueTracker()
Recalculate
Q5 = Q5 + Q5Value
End Sub

Once working I assume I can add this to a button then including it in another macro push?

[gebobs]

As I said, I'm rusty. The Excel Programming forum will help.

[blackjack]

Thanks for trying! Much appreciated.

[gebobs]

I kind of kludged it using a few "mirror" cells. Again it is using the condition level of 0.5 to make testing easier.

Cells A1 and A2 are as you have them.

Cell C1 stores the value of A1 as it is calculated when the button is pressed. This needs to be done because when the code writes the counter, it will recalculate the Rand() again and you won't see what the value was.

Cell C2 is the counter. If you use A2 as the counter, the code overwrites the equation.

Thus the code is:

Sub Button1_Click()

Dim x As Double

Calculate
x = Cells(2, 1)
Cells(1, 3) = Cells(1, 1)
Cells(2, 3) = Cells(2, 3) + x

End Sub

There are probably better ways. See attached.

[gebobs]

I've tried to respond to your PM regarding the .PHP file that you are getting when you try to download the file in my previous post above. I've sent two messages, but they aren't appearing in my sent file folder so I'm not sure if they are getting through.

The .PHP is the HTML link for the file. When you click it, the file should automatically download. It is a .XLSM file.

I see you are using Excel 2007. I think the file should be compatible with that version, but i am not sure.

What browser are you using?

Worse comes to worse, PM your email address to me and I'll send the file to you.

[blackjack]

Thanks that solved my 4 day dilemma!