Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    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. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    Last edited by RetiredGeek; 2015-04-26 at 07:53.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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:
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Glad we watch each others backs!

  6. #6
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    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
                                'bad luck -do nothing
                        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. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    =SUMPRODUCT(--(E2:J2>D2:I2))
    would seem to do what you want.
    Regards,
    Rory

    Microsoft MVP - Excel

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

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

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi rory

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

    zeddy

  10. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Well, it depends on the Null part. It wasn't clear to me how that should be handled?
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    OK, let's not get too picky, or I would use =SUMPRODUCT(--(E2:J2>=D2:I2))

    zeddy
    Last edited by zeddy; 2015-04-27 at 05:37.

  12. #11
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    @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. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It wouldn't be a particularly complicated fix. Within the formula you could just substitute the "Null" values with something like -1000000.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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