Theshold Count

[louismk]

HUI,


I'm not sure if you remember me but a few months ago you helped me with a very useful count macro which recapped the alternance between values and zeros for a given set of numbers in one cell(thanks by the way!). I have started on this project again (oh goodie) and I am stuck on something that we need and I thought maybe you could help. What we need to do is to look at a set of values (13 values to be exact) and count which of the values cross over one set value or percentage of something. So like for example say if my set value to look at was 5 and my array of values was 1-3-3-6-6-5-7-8-9-8-4 I would want a formula or a macro that could look at these values and know to count the number of times that any of the values goes over 5. So ultamatly it would return 6 for the array in the example since there are 6 vlaues that are greater than five.

Now, Here is the part where I am really getting stuck. I would want another cell to take the ammount that each value goes over the set value (in the case of our exanmple,5) and add up the total ammounts where the values actually go over 5. So for our example it would return 14 for the example since adding up the varance of each value over five totals 14.

I tried to fiddle with the macro that you sent me for the zero count but I had no luck. Can you help??


Thanks in advance!

 

[xld]

Try


=COUNTIF(rng,">"&threshold)


and


=SUMIF(rng,">"&threshold)-COUNTIF(rng,">"&threshold)*threshold

 

[kchiba]

Louismk,


is the array in a single cell or is it a renge of cells?

 

[louismk]

Array is a row with 13 different values.

 

[Hui]

Louismk


Try the following Function


To use =checkstr(cell, set value, Result_Type)


Result_Type = 1, The number of values over Set Value

Result_Type = 2, The sum of values over Set value


eg: assuming C10 has 1-3-3-6-6-5-7-8-9-8-4


=checkstr(C10, 5, 1) = 6

=checkstr(C10, 5, 2) = 44


Result_Type = 1 will be 6 the number of values over 5

Result_Type = 2 will be 44 the sum of values over 5

[pre]

Function checkstr(ByVal target As String, setv As Double, setc As Integer) As Double

Dim counter As Integer
Dim cumm As Double
Dim i As Integer

Let i = 1
Let counter = 0

For i = 1 To Len(target)
If Val(Mid(target, i, 1)) > setv And Mid(targe, i, 1) <> "-" Then
cumm = cumm + Val(Mid(target, i, 1))
counter = counter + 1
End If
Next

Select Case setc
Case 1
checkstr = counter
Case 2
checkstr = cumm
Case Else
checkstr = 0
End Select

End Function

[/pre]

Hope thats what your after

 

[louismk]

Hui,


This macro works but its not quite what I'm looking for. In the first case I'm looking for one that can count the cases in multuple cells not the summary of multiple cells in one cell. So like the formula would have to work with like =checkstr(A1:A13,9,1) and this would return the ammount of values greater than 9 in cells a1 through a13. In the second case what would need to happen is to then take each value greater than 9 and total up the sum of each difference between a given value greater than 9 and 9. If from our first case we got "4" returned in the cell the second case would be able to look at the 4 values that are in fact greater than 9 and add up all of the differences. So if in reality the 4 values that were giving us the result in the first case were 12,13,10, and 15 the second case would return "14" because that the sum of all of the differences of each of these values and 9 (3+4+1+6). But these macros do in fact work and I'm probably going to be able to use the first case in another area of my project because it works well with concatenates so thanks!- but do you think you can help me out now that I have better explaned the paramiters?? Thanks alot for all your help!

 

[Hui]

To count the number of items > a value

=COUNTIFS(A1:A13,">5")

or

=COUNTIFS(A1:A13,">"&D1)

where the value is in D1


To Sum the amount greater than a value

=SUMIFS(A1:A13,A1:A13,">5")-COUNTIFS(A1:A13,">5")*5

or

=SUMIFS(A1:A13,A1:A13,">"&D1)-COUNTIFS(A1:A13,">"&D1)*D1

or

=SUMPRODUCT((A1:A13>D1)*(A1:A13-D1))

 

[louismk]

 

[louismk]

Got it! Thanks to all who responded


Cheers