1. ## Counting inequalities

Following table was pulled from an SQL Server data base and shows dividends paid over the past years.

Count Div Increase.png

I want to count how many times the dividend was at least equal or greater than that of the preceding year and came
up with the following (ugly) formulla:
=(\$E2>=\$D2)+(\$F2>=\$E2)+(\$G2>=\$F2)+(\$H2>=\$G2)+(\$I2> =\$H2)+(\$J2>=\$I2)
Is there a more compact or general way to express this?

2. Woody,

Here's a User Defined Function that should simplify things.
Code:
```Option Explicit

Function iIncDivCnt(rngBase As Range) As Integer

Dim lColCntr As Long

Application.Volatile

lColCntr = 0
iIncDivCnt = 0

Do

If rngBase.Offset(0, lColCntr + 1).Value >= _
rngBase.Offset(0, lColCntr) Then _
iIncDivCnt = iIncDivCnt + 1

lColCntr = lColCntr + 1

Loop Until rngBase.Offset(0, lColCntr) = ""

End Function```
WoodyCntDiv.JPG

Formula in Col L: =iIncDivCnt(\$D2)
Note: The \$ this keeps it anchored to col D as the starting point but allows you to fill the formula down the column by dragging.

Test File: WoodyDivCntr.xlsm

I designed the code so that if you maintain the blank column between the last dividend and the count column you will NOT have to change anything when you add a new year of dividends, just insert a column and start typing.

HTH

3. RG,

Nice job on your UDF. A tweak to prevent a blank space in the row from stopping the code short. You can enter the entire range into the formula:

in cell L2 =iIncDivCnt(D2:J2)

cellcount.png

Maud

Code:
```Option Explicit

Function iIncDivCnt(rngBase As Range) As Long
'---------------------------------
'DECLARE AND SET VARIABLES
Application.Volatile
Dim CELL As Range
Dim lColCntr As Long, rows As Long, cols As Long
rows = rngBase.rows.Count
cols = rngBase.Columns.Count
rngBase.Resize(cols - 1).Select
'---------------------------------
'COUNT CONDITIONS
For Each CELL In rngBase
If CELL < CELL.Offset(0, 1) And CELL <> "" Then
iIncDivCnt = iIncDivCnt + 1
End If
Next
End Function```

4. Maud,

Nice catch. One note the user must remember to include the blank column, between the last dividend and the counter, otherwise inserting 2 columns the counts go off. Don't ask me why it didn't happen after inserting one column but it didn't!

BTW: you for got the = sign in the test as the OP said GT or Equal To. :HTH:

5. Glad we watch each others backs!

6. Thanks for your replies. I was already afraid that no compact solution could be found within the set of Excel functions. What further complicated the issue was the presence of Nulls in the data set (not mentioned in the Original question). The spreadsheet cells do actually contain the text "NULL" when the dividend is absent (meaning no dividend or we do not know yet for the last year) in the original SQL data base. (Contrarily to my belief it would be a blank cell).

Following code takes this into account.

Code:
```Public Function CountDividendIncrease(rngBase As Range) As Long

Const c_strNullValue As String = "NULL"

Dim lngResult As Long
lngResult = 0

Dim a_Dividends As Variant
a_Dividends = rngBase.Value

Dim i As Long
For i = 2 To UBound(a_Dividends, 2)
Select Case a_Dividends(1, i - 1) = c_strNullValue
Case True
Select Case a_Dividends(1, i) = c_strNullValue
Case True
'both values are null, do nothing
Case False
'there is a dividend for the next year
lngResult = lngResult + 1
End Select
Case False
Select Case a_Dividends(1, i) = c_strNullValue
Case True
'do nothing, there is no dividend next
Case False
'both years had dividends - check if
'next year's is at least equal
Select Case a_Dividends(1, i) >= a_Dividends(1, i - 1)
Case True
lngResult = lngResult + 1
Case False
End Select
End Select
End Select
Next i

PROC_EXIT:
CountDividendIncrease = lngResult
End Function```
PS: Year ago a learned that using Offsets was a bad practice as it would slow down calculation. I guess that with modern computers and calculation limited within one row that is no longer the case. But coding habits are hard to forget.

7. =SUMPRODUCT(--(E2:J2>D2:I2))
would seem to do what you want.

8. ## The Following 2 Users Say Thank You to rory For This Useful Post:

RetiredGeek (2015-04-27),zeddy (2015-04-27)

9. Hi rory

No seem needed - it'll definitely do the job!
Concise, effective, efficient, and elegant to boot.

zeddy

10. Well, it depends on the Null part. It wasn't clear to me how that should be handled?

11. OK, let's not get too picky, or I would use =SUMPRODUCT(--(E2:J2>=D2:I2))

zeddy

12. @Rory: this is indeed an elegant way to achieve the desired result. Congratulations.

However, I have to stick with a (modified) UDF. As you observed the NULLs spoil everything. To further complicate the matter, the data base does also contain some real zeroes for the dividend. Technically you can say that if for two consecutive years the dividend stays zero, then the dividend has not decreased! But that was not the spirit of the inquiry. Damn database data

13. It wouldn't be a particularly complicated fix. Within the formula you could just substitute the "Null" values with something like -1000000.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•