find first and last values with conditions

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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))

Reply | Quote

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.

Reply | Quote

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(A3)+100*YEAR(A3))*ROW($A$2:$A$200)),2)-INDEX($B$1:$B$500,MIN(ROW(),MAX(((MONTH($A$2:$A$200)+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*YEAR($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$200)+100*YEAR($A$2:$A$200))=MONTH(A2)+100*YEAR(A2))*ROW($A$2:$A$200))))

Reply | Quote

Doesn’t calculate the right answer(s) for me on the sample spreadsheet.

Reply | Quote

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

Reply | Quote

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. “

Reply | Quote

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

38013-MonthlyTotals1

Code:

Public Sub MonthlyMaxMin()
[COLOR=”#008000″]’—————————————-
‘DECLARE AND SET VARIABLES[/COLOR]
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
[COLOR=”#008000″]’—————————————-
‘FIND MONTHLY MIN AND MAX DATES[/COLOR]
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
[COLOR=”#008000″]’—————————————-
‘VLOOKUP IN COLUMN 2 WITH SUBTOTAL OF DIFFERENCE[/COLOR]
        SubTotal = WorksheetFunction.VLookup(Max, Range(“A2:B” & LastRow), 2, False) – _
            WorksheetFunction.VLookup(Min, Range(“A2:B” & LastRow), 2, False)
[COLOR=”#008000″]’—————————————-
‘OUTPUT SBUTOTAL TO COL C AND UPDATE RUNNING TOTAL[/COLOR]
        Cells(I, 3) = SubTotal  [COLOR=”#008000″]’COMMENT OUT IF SUBTOTALS NOT REQUIRED[/COLOR]
        Total = Total + SubTotal
    End If
Next I
MsgBox Total  [COLOR=”#008000″]’COMMENT OUT IF TOTAL NOT REQUIRED[/COLOR]
End Sub

Reply | Quote

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

Reply | Quote

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
38019-numbers

Reply | Quote

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

Reply | Quote

Skipro,

The max and min formulas in my code are finding the first and last date of the month in col A; not values in col B

Reply | Quote

Skipro,

The max and min formulas in my code are finding the first and last date of the month in col A; not values in col B

Thanks for help and this info.
To help me understand this better, what triggered this clarification? What does this tell me besides the literal statement? Are you saying dguillett’s script does it differently?

Reply | Quote

maudibe and dguillett
Thanks
Maudibe’s script copies font format from corresponding column B; color, bold, position in cell.
How is this happening? Where in the script does this occur? Can this be included in dguillett’s script?

Reply | Quote

Skipro,

I just formatted the column of cells in col 3 with center, bold, and red font in the first image. The second image just placed the vales in the cells that you had formatted in your posted book. No code; just formatting

Reply | Quote

Skipro,

I just formatted the column of cells in col 3 with center, bold, and red font in the first image. The second image just placed the vales in the cells that you had formatted in your posted book. No code; just formatting

When I run your script, it copies the font format in “B”. tried it several times, same result. dguillett’s script does not do this.
see attached 1st 3 returns up to line 43

Reply | Quote

I added a couple of simple lines to the code

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)
Cells(lr, “b”).Copy
Cells(lr, “c”).PasteSpecial , Paste:=xlPasteFormats
lr = i
End If
Next i
Application.ScreenUpdating = 1
End Sub

Reply | Quote

Skipro,

Its because you has formatted the cells in col B and C when you posted the spreadsheet in Post #13. I removed the cell formatting and it works perfectly.

Maud

38025-Example_Revised

Reply | Quote

Maudibe and dguillett,
If data is on sheet “daily” and I need the return on sheet “mos sum”, how do I change the scripts?

Reply | Quote

You should have told us that in the first place and provided an example file showing EXACTLY what you wanted. Do so.

Reply | Quote

I think we need to be cognizant of the fact that workbooks projects are constantly evolving, especially when we assist with code. One tweak often leads to another.

skipro,

Here is the amended workbook that will write to the “mos sum” sheet instead.

HTH,
Maud

38081-example_Revised_2

Reply | Quote

Maudibe,
Thanks. As you say, these things continually evolve, especially if they are successful. My main sheet got too busy. These changes also helps me understand the code and approach since my scripting skills are below basic. This helps me be more independent of this forum/group.
The date range in A was a nice touch. Now I have to try and decode this to understand the coding.

Reply | Quote