Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts

    find first and last values with conditions

    Column A is dates in order spanning several years, column B are numbers.
    I need the sum of the last number minus the first number for each month and year.

    I can set the month/year condition with this:
    =SUM(IF(MONTH($A:$A)=9,IF(YEAR($A:$A)=2014,
    But I do not know how to find the last and first numbers.
    When I use LOOKUP(9E+307,B:B) with above [in an array] it does not return the correct last number for that month.

    What I need is:
    For month and year, last number minus first number
    or
    =SUM(IF(MONTH($A:$A)=9,IF(YEAR($A:$A)=2014, last number in column [or last date entered] for that month/year minus first number in column [or first date entered] for that month/year.

  2. #2
    Lounger
    Join Date
    Feb 2011
    Posts
    28
    Thanks
    0
    Thanked 10 Times in 8 Posts
    I have broken the problem down into bits in the attached file. Columns A & B are a set of ascending dates and some random numbers. Column C checks if the date is the first or last of the month and displays the figure and column D is an array formula that finds the next non-blank cell's value, so you can hide those two columns. Column F does the subtraction; column G displays the month that the result applies to. It struggles with the first and last values though.
    Attached Files Attached Files

  3. #3
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    correct this for word wrap where month/year in a column H and formula in col I
    =SUMPRODUCT((YEAR($A$2:$A$2000)=YEAR(H2))*
    MONTH($A$2:$A$2000)=MONTH(H2))*$B$2:$B$2000)

    01/2010 630
    02/2010 1231
    03/2010 2612
    04/2010 3112
    05/2010 3772
    06/2010 5676
    07/2010 5760

  4. #4
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    unclehewie,
    Thanks. Is there a solution that does not involve the extra columns? Can this be done with a single formula?

    dguillett,
    Confused with your reply. Please explain. Thanks.

    Is there an Excel function or approach that can find the first entry within a condition, such as within a date to date period?

  5. #5
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    This will find the first item in col B for the earliest date in Col A.
    =MIN(IF(MONTH($A$2:$A$80)=MONTH(A3),$B$2:$B$80))
    This is an array formula that must be entered using Ctrl+shift+enter instead of just enter.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I found this interesting to do in one column. I don't know if this is overkill or not, but it worked using the file from unclehewie.

    It's an array formula that I filled down starting in row TWO.

    =IF(MONTH(A2)=MONTH(A3),"",INDEX($A$1:$B$500,MAX(( (MONTH($A$2:$A$200)+100*YEAR($A$2:$A$200))=MONTH(A 3)+100*YEAR(A3))*ROW($A$2:$A$200)),2)-INDEX($B$1:$B$500,MIN(ROW(),MAX(((MONTH($A$2:$A$20 0)+100*YEAR($A$2:$A$200))=MONTH(A3)+100*YEAR(A3))* ROW($A$2:$A$200)))))

    In row 1 I left out the month comparison:

    =INDEX($A$1:$B$500,MAX(((MONTH($A$2:$A$200)+100*YE AR($A$2:$A$200))=MONTH(A2)+100*YEAR(A2))*ROW($A$2: $A$200)),2)-INDEX($B$1:$B$500,MIN(ROW(),MAX(((MONTH($A$2:$A$20 0)+100*YEAR($A$2:$A$200))=MONTH(A2)+100*YEAR(A2))* ROW($A$2:$A$200))))
    Last edited by kweaver; 2014-09-27 at 11:15.

  7. #7
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    This array formula entered using CSE will find the max and the min and sub tract min from max It is ONE formula in ONE cell

    =MAX(IF(((MONTH($A$2:$A$1400)=MONTH(A2))(YEAR($A$2 :$A$1400)=YEAR(A2))),$B$2:$B$1400))-MIN(IF(((MONTH($A$2:$A$1400)=MONTH(A2))(YEAR($A$2: $A$1400)=YEAR(A2))),$B$2:$B$1400))

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Doesn't calculate the right answer(s) for me on the sample spreadsheet.

  9. #9
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    What is the correct answer and what is the logic. If you get the max for Jan @ 197 and the min for Jan at 55 and subtract, you get 142. Feb=115, etc

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Oops. I was using different test numbers. Sorry. Problem is, I think the OP wanted the single results not a difference showing up in each row. So, a comparison of the months like I did would have to apply.

    Nice formula.

  11. #11
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Confusing, OP said

    "What I need is:
    For month and year, last number minus first number
    or
    =SUM(IF(MONTH($A:$A)=9,IF(YEAR($A:$A)=2014, last number in column [or last date entered] for that month/year minus first number in column [or first date entered] for that month/year. "

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Skipro,

    Here is a VBA approach that calculates the difference of the adjacent cells in col B between the first and last listed date of each month/year. The code optionally places the monthly differences in col C and a msgbox at the end with the final total. Place in a standard module and run with your data sheet the active sheet.

    HTH,
    Maud

    MonthlyTotals1.png

    Code:
    Public Sub MonthlyMaxMin()
    '----------------------------------------
    'DECLARE AND SET VARIABLES
    Dim LastRow As Integer, StartRow As Integer
    Dim Total As Double, SubTotal As Double, I As Integer
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    StartRow = 2
    '----------------------------------------
    'FIND MONTHLY MIN AND MAX DATES
    For I = 2 To LastRow
        If Month(Cells(I, 1)) <> Month(Cells(I + 1, 1)) Then
            Min = WorksheetFunction.Min(Range(Cells(StartRow, 1), Cells(I, 1)))
            Max = WorksheetFunction.Max(Range(Cells(StartRow, 1), Cells(I, 1)))
            StartRow = I + 1
    '----------------------------------------
    'VLOOKUP IN COLUMN 2 WITH SUBTOTAL OF DIFFERENCE
            SubTotal = WorksheetFunction.VLookup(Max, Range("A2:B" & LastRow), 2, False) - _
                WorksheetFunction.VLookup(Min, Range("A2:B" & LastRow), 2, False)
    '----------------------------------------
    'OUTPUT SBUTOTAL TO COL C AND UPDATE RUNNING TOTAL
            Cells(I, 3) = SubTotal  'COMMENT OUT IF SUBTOTALS NOT REQUIRED
            Total = Total + SubTotal
        End If
    Next I
    MsgBox Total  'COMMENT OUT IF TOTAL NOT REQUIRED
    End Sub

  13. #13
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Folks,
    You are missing my objective. I do not want the Min/Max, I want the first and last for each month. Column A is dates and is in order, column B is data/numbers and is NOT in ascending or any other order so Min/Max does not return the same as first/last.
    See example.
    D8 is my entry, not from any formula or script.

    Maude,
    I have your script included in this example but it is not functioning. I get a syntax error when running macro.
    Line error:
    Dim Total As Double, SubTotal As Double, I As Integer LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row StartRow = 2
    Attached Files Attached Files

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Skipro,

    The file I provided does total the difference of first and last day of each month and places the value adjacent to the last day in column C. See the image in my above post. They are not max and min differences.

    The code you placed in the file was not pasted correctly. Somehow 3 lines of code were pasted on the same line:

    Dim Total As Double, SubTotal As Double, I As Integer LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row StartRow = 2

    There was also a missing End Sub. See my posted code above. Here is a screen shot of your spreadsheet with the code pasted correctly.

    HTH,
    Maud
    numbers.png

  15. #15
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Here is a macro that will do it fast with a for i loop

    Sub GetDifferenceLastDayLessFirstDay()
    Dim lr As Long
    Dim i As Long
    Application.ScreenUpdating = 0
    Columns(3).Clear
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    On Error Resume Next
    For i = lr To 1 Step -1
    If Month(Cells(i, 1)) <> Month(Cells(i + 1, 1)) Or _
    Cells(i, 1) = "Date" Then
    Cells(lr, "c") = Cells(lr, 2) - Cells(i + 1, 2)
    lr = i
    End If
    Next i
    Application.ScreenUpdating = 1
    End Sub
    Last edited by Supershoe; 2014-09-30 at 17:03.

Page 1 of 2 12 LastLast

Posting Permissions

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