Finding biggest observation conditioned on several factors

H

homie

Board Regular
Joined
Nov 30, 2014
Messages
66
Hi,

I need to make a formula to return the largest number of Deposits of NOK for any given day in the previous 30 days. The deposits are timestamped by the minute. So I need to add the number of deposits per day for each day of the previous 30 days and then find the largest number of deposits for any of those days.

This formula/solution needs to be in one cell. I will copy it downward and the "present day" will change, so the previous 30 days will change as I copy it downward. The table below is where I get the information, but I will present the relevant data in another sheet entirely.

How do I go about solving this?

2nd related problem: I also need to find the largest Amount Deposited of NOK for any given day in the previous 30 days.

The dataset where I will be looking, looks like this:

[TABLE="width: 972"]
<tbody>[TR]
[TD]Timestamp recognized by Excel[/TD]
[TD]Timestamp recognized by Excel, rounded[/TD]
[TD]Remove USD,EUR,NOK or XBT, ETH, LTC from string[/TD]
[TD]Amount American number as text[/TD]
[TD]Currency[/TD]
[TD]Amount European number[/TD]
[TD]Action[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 00:09[/TD]
[TD="align: right"]16.09.2015 00:09[/TD]
[TD]9/16/2015 0:09,100[/TD]
[TD]100[/TD]
[TD]NOK[/TD]
[TD="align: right"]100[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 00:15[/TD]
[TD="align: right"]16.09.2015 00:15[/TD]
[TD]9/16/2015 0:15,100[/TD]
[TD]100[/TD]
[TD]USD[/TD]
[TD="align: right"]100[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 05:54[/TD]
[TD="align: right"]16.09.2015 05:54[/TD]
[TD]9/16/2015 5:54,0.30015[/TD]
[TD]0.30015[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,30015[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 14:34[/TD]
[TD="align: right"]16.09.2015 14:34[/TD]
[TD]9/16/2015 14:34,100[/TD]
[TD]100[/TD]
[TD]NOK[/TD]
[TD="align: right"]100[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 17:51[/TD]
[TD="align: right"]16.09.2015 17:51[/TD]
[TD]9/16/2015 17:51,4800[/TD]
[TD]4800[/TD]
[TD]NOK[/TD]
[TD="align: right"]4800[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 18:19[/TD]
[TD="align: right"]16.09.2015 18:19[/TD]
[TD]9/16/2015 18:19,1000[/TD]
[TD]1000[/TD]
[TD]NOK[/TD]
[TD="align: right"]1000[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 20:19[/TD]
[TD="align: right"]16.09.2015 20:19[/TD]
[TD]9/16/2015 20:19,0.2699[/TD]
[TD]0.2699[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,2699[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 21:04[/TD]
[TD="align: right"]16.09.2015 21:04[/TD]
[TD]9/16/2015 21:04,0.756[/TD]
[TD]0.756[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,756[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 05:30[/TD]
[TD="align: right"]17.09.2015 05:30[/TD]
[TD]9/17/2015 5:30,2015[/TD]
[TD]2015[/TD]
[TD]NOK[/TD]
[TD="align: right"]2015[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 06:50[/TD]
[TD="align: right"]17.09.2015 06:50[/TD]
[TD]9/17/2015 6:50,200[/TD]
[TD]200[/TD]
[TD]NOK[/TD]
[TD="align: right"]200[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 07:40[/TD]
[TD="align: right"]17.09.2015 07:40[/TD]
[TD]9/17/2015 7:40,900[/TD]
[TD]900[/TD]
[TD]NOK[/TD]
[TD="align: right"]900[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 08:49[/TD]
[TD="align: right"]17.09.2015 08:49[/TD]
[TD]9/17/2015 8:49,0.52[/TD]
[TD]0.52[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,52[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 08:53[/TD]
[TD="align: right"]17.09.2015 08:53[/TD]
[TD]9/17/2015 8:53,0.37501792[/TD]
[TD]0.37501792[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,37501792[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 11:07[/TD]
[TD="align: right"]17.09.2015 11:07[/TD]
[TD]9/17/2015 11:07,0.02707997[/TD]
[TD]0.02707997[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,02707997[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 11:26[/TD]
[TD="align: right"]17.09.2015 11:26[/TD]
[TD]9/17/2015 11:26,0.80150239[/TD]
[TD]0.80150239[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,80150239[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 11:28[/TD]
[TD="align: right"]17.09.2015 11:28[/TD]
[TD]9/17/2015 11:28,0.0724[/TD]
[TD]0.0724[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,0724[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 11:28[/TD]
[TD="align: right"]17.09.2015 11:28[/TD]
[TD]9/17/2015 11:28,0.5[/TD]
[TD]0.5[/TD]
[TD]LTC[/TD]
[TD="align: right"]0,5[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 12:08[/TD]
[TD="align: right"]17.09.2015 12:08[/TD]
[TD]9/17/2015 12:08,0.1051[/TD]
[TD]0.1051[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,1051[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 12:27[/TD]
[TD="align: right"]17.09.2015 12:27[/TD]
[TD]9/17/2015 12:27,0.4[/TD]
[TD]0.4[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,4[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 16:30[/TD]
[TD="align: right"]17.09.2015 16:30[/TD]
[TD]9/17/2015 16:30,2500[/TD]
[TD]2500[/TD]
[TD]NOK[/TD]
[TD="align: right"]2500[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 16:33[/TD]
[TD="align: right"]17.09.2015 16:33[/TD]
[TD]9/17/2015 16:33,0.025[/TD]
[TD]0.025[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,025[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 17:40[/TD]
[TD="align: right"]17.09.2015 17:40[/TD]
[TD]9/17/2015 17:40,1.5[/TD]
[TD]1.5[/TD]
[TD]XBT[/TD]
[TD="align: right"]1,5[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 17:55[/TD]
[TD="align: right"]17.09.2015 17:55[/TD]
[TD]9/17/2015 17:55,0.025[/TD]
[TD]0.025[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,025[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 17:57[/TD]
[TD="align: right"]17.09.2015 17:57[/TD]
[TD]9/17/2015 17:57,500[/TD]
[TD]500[/TD]
[TD]NOK[/TD]
[TD="align: right"]500[/TD]
[TD]Deposit[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Sort by date Sort by votes
Marcelo Branco said:
Your new data sample has only withdrawals for XBT (BTC), no deposits. Is this correct?

M.
Click to expand...
Sorry for the late reply, very busy weekend.

Yeah, that is correct. For some reason, our data guy could only extract fiat currency (NOK) deposits (not withdrawels) and only XBT withdrawels (no deposits).
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Ok.
Assuming data sample in Sheet1 columns A:F, headers in row 1, data beginning in row 2
Dates (column A) in ascending order

Sheet2
Criteria --> columns A:D (light blue)
Just for testing purposes i included a row with Withdraw as criteria for XBT

Results --> columns E:L (gray area)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[TD="bgcolor: #DCE6F1"]
J​
[/TD]
[TD="bgcolor: #DCE6F1"]
K​
[/TD]
[TD="bgcolor: #DCE6F1"]
L​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Ref Date​
[/TD]
[TD]
Days Before​
[/TD]
[TD]
Currency​
[/TD]
[TD]
Action​
[/TD]
[TD]
LDA​
[/TD]
[TD]
LDA Day​
[/TD]
[TD]
LAA​
[/TD]
[TD]
LAA Day​
[/TD]
[TD]
SDA​
[/TD]
[TD]
SDA Day​
[/TD]
[TD]
SAA​
[/TD]
[TD]
SAA Day​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD="bgcolor: #DCE6F1"]
19/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
NOK​
[/TD]
[TD="bgcolor: #DCE6F1"]
Deposit​
[/TD]
[TD="bgcolor: #D9D9D9"]
6​
[/TD]
[TD="bgcolor: #D9D9D9"]
17/09/2015​
[/TD]
[TD="bgcolor: #D9D9D9"]
28600​
[/TD]
[TD="bgcolor: #D9D9D9"]
18/09/2015​
[/TD]
[TD="bgcolor: #D9D9D9"]
1​
[/TD]
[TD="bgcolor: #D9D9D9"]
16/09/2015​
[/TD]
[TD="bgcolor: #D9D9D9"]
100​
[/TD]
[TD="bgcolor: #D9D9D9"]
16/09/2015​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD="bgcolor: #DCE6F1"]
19/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
XBT​
[/TD]
[TD="bgcolor: #DCE6F1"]
Deposit​
[/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD="bgcolor: #DCE6F1"]
19/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
XBT​
[/TD]
[TD="bgcolor: #DCE6F1"]
Withdraw​
[/TD]
[TD="bgcolor: #D9D9D9"]
10​
[/TD]
[TD="bgcolor: #D9D9D9"]
18/09/2015​
[/TD]
[TD="bgcolor: #D9D9D9"]
3,65279​
[/TD]
[TD="bgcolor: #D9D9D9"]
18/09/2015​
[/TD]
[TD="bgcolor: #D9D9D9"]
1​
[/TD]
[TD="bgcolor: #D9D9D9"]
16/09/2015​
[/TD]
[TD="bgcolor: #D9D9D9"]
0,30015​
[/TD]
[TD="bgcolor: #D9D9D9"]
16/09/2015​
[/TD]
[/TR]
</tbody>[/TABLE]


To get these results run the macro Main

Code: 
Sub Main()
    Dim lastRow1 As Long, lastRow2 As Long, lDataFirst As Long, lDataLast As Long
    Dim shData As String, rData As Range, i As Long
    
    shData = "Sheet1" '<--adjust the sheet name
    With Sheets(shData)
        lastRow1 = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set rData = .Range("A2:F" & lastRow1)
    End With
        
    With Sheets("Sheet2")
        lastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To lastRow2
            lDataFirst = GetFirst(rData.Columns(1), .Range("A" & i) - .Range("B" & i))
            lDataLast = GetLast(rData.Columns(1), .Range("A" & i))
            If lDataFirst > lDataLast Then
                .Range("E" & i).Resize(, 8) = Array(0, "", 0, "", 0, "", 0, "")
            Else
                .Range("E" & i).Resize(, 8) = GetResults(rData, lDataFirst, lDataLast, _
                                .Range("C" & i).Value, .Range("D" & i).Value)
            End If
        Next i
    End With
End Sub

Function GetFirst(r As Range, lDay As Date)
    Dim rFound As Range, lFirst As Variant
    
    Set rFound = r.Find(lDay, After:=r.Cells(r.Rows.Count), _
        Lookat:=xlWhole, LookIn:=xlValues, SearchDirection:=xlNext)
    
    If rFound Is Nothing Then
        lFirst = Application.Match(CLng(lDay), r)
        If IsError(lFirst) Then
            GetFirst = 1
        Else
            GetFirst = lFirst + 1
        End If
    Else
        GetFirst = rFound.Row - r.Row + 1
    End If
End Function

Function GetLast(r As Range, lDay As Date)
    Dim rFound As Range, lLast As Variant
    
    Set rFound = r.Find(lDay, After:=r.Cells(1), _
        Lookat:=xlWhole, LookIn:=xlValues, SearchDirection:=xlPrevious)
    
    If rFound Is Nothing Then
        lLast = Application.Match(CLng(lDay), r)
        If IsError(lLast) Then
            GetLast = 0
        Else
            GetLast = lLast
        End If
    Else
        GetLast = rFound.Row - r.Row + 1
    End If
End Function

Function GetResults(r As Range, lFirst As Long, lLast As Long, Crit1 As String, Crit2 As String)
    Dim vData As Variant, dic As Object, vKey As Variant, i As Long
    'LDA=LargestNumDepositsAny, LDA=LargestAmountAny
    Dim lLDA As Long, dbLAA As Double
    Dim DayLAA As Variant, DayLDA As Variant
    'SDA=SmallestNumDepositsAny, SAA=SmallestAmountAny
    Dim lSDA As Long, dbSAA As Double
    Dim DaySDA As Variant, DaySAA As Variant
    
    Set dic = CreateObject("Scripting.Dictionary")
    
    vData = Range(r.Cells(lFirst, 1), r.Cells(lLast, 6))
    For i = LBound(vData, 1) To UBound(vData, 1)
        If UCase(vData(i, 4)) = UCase(Crit1) And UCase(vData(i, 6)) = UCase(Crit2) Then
            If dic.exists(vData(i, 1)) Then
                dic(vData(i, 1)) = Array(dic(vData(i, 1))(0) + 1, dic(vData(i, 1))(1) + vData(i, 5))
            Else
                dic(vData(i, 1)) = Array(1, vData(i, 5))
            End If
        End If
    Next i
    
    If dic.Count Then
        'Initialize lSDA and dbSAA with Max possible values
        lSDA = UBound(vData, 1)
        dbSAA = Application.Sum(r.Columns(5))
        
        For Each vKey In dic.keys
            If dic(vKey)(0) > lLDA Then
                lLDA = dic(vKey)(0)
                DayLDA = vKey
            End If
            If dic(vKey)(1) > dbLAA Then
                dbLAA = dic(vKey)(1)
                DayLAA = vKey
            End If
            If dic(vKey)(0) < lSDA Then
                lSDA = dic(vKey)(0)
                DaySDA = vKey
            End If
            If dic(vKey)(1) < dbSAA Then
                dbSAA = dic(vKey)(1)
                DaySAA = vKey
            End If
        Next vKey
    End If
    
    GetResults = Array(lLDA, DayLDA, dbLAA, DayLAA, lSDA, DaySDA, dbSAA, DaySAA)
End Function

M.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Age Group Ranging by gender
Replies
2
Views
259
Raj1979
R
O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
186
Omran Y
O
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
A
  • Question Question
Need to convert days, hours, minutes and second text into hh:mm:ss
Replies
7
Views
895
Aadya
A
M
  • Question Question
SLA Time Calculation
Replies
5
Views
344
felixstraube
F

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top