Need a function that input a value and a range, output where the first cell value in that range is bigger than the value

[dikken20]

Details:

A1 contains a number, range B1:K1 contain also numbers.
The function will get both the value of A1 and the vales inside the B1:K1 range.
It will check where is the first time a value inside that range (beginning value in B1) is higher than A1, and output a number that represents the number of cells till finding the highest number for the first time.

Example is always better to understand:
A1 = 250
B1=55 C1=60 D1=20 E1=170 F1=90 G1=300 H1=200 I1=400 J1=550 K1=10

Function finds that G1 is higher than A1 so result will be 5 (B1:F1).

In case all range B1:K1 is smaller than A1 then result will be 10.


Is there a built-in function for that, otherwise, can you help me write a function that does that?
Thank you!

 

[Rick Rothstein]

Will your range always be a single horizontal row or possibly a single vertical column? If not, how should the count take place for a rectangular range consisting of multiple rows and multiple columns?

Also, the comparison is always "larger than", not "equal to or larger than", correct?

 

[dikken20]

Will your range always be a single horizontal row or possibly a single vertical column? If not, how should the count take place for a rectangular range consisting of multiple rows and multiple columns?

Also, the comparison is always "larger than", not "equal to or larger than", correct?

Thanks for the reply!

Always single horizontal row and always "larger than".
I do actually see a good option to change the condition to be "Larger than" a percent of that value. meaning, if cell A1 = 100 and say percent is 70% then it will look for the first cell that is higher than 70 and will return the count of cells before

 

[Rick Rothstein]

Here is a UDF (user defined function) that should work for you. The first argument is the value to be larger than, the second argument is the horizontal (or vertical) range to search in and I have included an optional argument allowing you to specify a percentage to modify the first argument's value (the default is 100 for 100%).

[table="width: 500"]
[tr]
	[td]Function LessThanCount(Value As Double, Rng As Range, Optional Percent As Double = 100) As Long
  Dim Cnt As Long, Cell As Range
  For Each Cell In Rng
    If Cell.Value > Percent * Value / 100 Then Exit For
    LessThanCount = LessThanCount + 1
  Next
End Function[/td]
[/tr]
[/table]

 

[dikken20]

Here is a UDF (user defined function) that should work for you. The first argument is the value to be larger than, the second argument is the horizontal (or vertical) range to search in and I have included an optional argument allowing you to specify a percentage to modify the first argument's value (the default is 100 for 100%).

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function LessThanCount(Value As Double, Rng As Range, Optional Percent As Double = 100) As Long
  Dim Cnt As Long, Cell As Range
  For Each Cell In Rng
    If Cell.Value > Percent * Value / 100 Then Exit For
    LessThanCount = LessThanCount + 1
  Next
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Toda Rick, this is exactly what I was looking for!

 

[dikken20]

On a second thought, I would like to ask for another UDF with a very close functionality please.

I would like to get the number of cells that numbered on a decreasing order.

Input:
1. Value(A1)
2. Range(B1:K1)
3. Optional percent to test whether B1 is lower than a percent of A1.
4. Optional percent tests whether a cell within the range is lower than a percent of the PREVIOUS cell.
5. Optional value checks the difference between a cell and its the previous cell within the range, the difference must be greater than this value.
While #4 and #5 will be a combined test that will check if #4 is true as long as the difference between the tested cells is greater than #5 , in other words the a condition that both #4 and #5 are true.

Example1:
A1=500
B1=490 C1=430 D1=300 E1=250 F1=200 G1=190 H1=150 I1=110 J1=80 K1=75
Percent1 = 100%
Percent2 = 90%
Value=10
Results: 5 (G1<90% of F1)

Example2 (difference from example1 is G1=175):
A1=500
B1=490 C1=430 D1=300 E1=250 F1=200 G1=175 H1=150 I1=110 J1=80 K1=75
Percent1 = 100%
Percent2 = 90%
Value=10
Results: 9 (K1-J1 less than the value of 10)

Thank you!

 

[dikken20]

On a second thought, I would like to ask for another UDF with a very close functionality please.

I would like to get the number of cells that numbered on a decreasing order.

Input:
1. Value(A1)
2. Range(B1:K1)
3. Optional percent to test whether B1 is lower than a percent of A1.
4. Optional percent tests whether a cell within the range is lower than a percent of the PREVIOUS cell.
5. Optional value checks the difference between a cell and its the previous cell within the range, the difference must be greater than this value.
While #4 and #5 will be a combined test that will check if #4 is true as long as the difference between the tested cells is greater than #5 , in other words the a condition that both #4 and #5 are true.

Example1:
A1=500
B1=490 C1=430 D1=300 E1=250 F1=200 G1=190 H1=150 I1=110 J1=80 K1=75
Percent1 = 100%
Percent2 = 90%
Value=10
Results: 5 (G1<90% of F1)

Example2 (difference from example1 is G1=175):
A1=500
B1=490 C1=430 D1=300 E1=250 F1=200 G1=175 H1=150 I1=110 J1=80 K1=75
Percent1 = 100%
Percent2 = 90%
Value=10
Results: 9 (K1-J1 less than the value of 10)

Thank you!

Help please?