Results 1 to 15 of 26

20140924, 20:06 #1
 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.

20140925, 05:46 #2
 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 nonblank 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.

20140925, 09:40 #3
 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

20140926, 17:13 #4
 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?

20140926, 18:33 #5
 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.

20140927, 11:11 #6
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,492
 Thanks
 33
 Thanked 63 Times in 59 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; 20140927 at 11:15.

20140927, 12:27 #7
 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))

20140927, 13:37 #8
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,492
 Thanks
 33
 Thanked 63 Times in 59 Posts
Doesn't calculate the right answer(s) for me on the sample spreadsheet.

20140927, 13:57 #9
 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

20140927, 14:54 #10
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,492
 Thanks
 33
 Thanked 63 Times in 59 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.

20140927, 16:52 #11
 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. "

20140929, 22:20 #12
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,727
 Thanks
 125
 Thanked 680 Times in 617 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

20140930, 14:18 #13
 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

20140930, 16:54 #14
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,727
 Thanks
 125
 Thanked 680 Times in 617 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

20140930, 17:01 #15
 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 SubLast edited by Supershoe; 20140930 at 17:03.