Finding biggest observation conditioned on several factors

[homie]

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]

 

[Marcelo Branco]

Maybe something like this
(i excluded the columns that doesn't matter for the calculation)


[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]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Timestamp​

[/TD]
[TD]

Currency​

[/TD]
[TD]

Amount European number​

[/TD]
[TD]

Action​

[/TD]
[TD][/TD]
[TD]

Ref Date​

[/TD]
[TD]

Currency​

[/TD]
[TD]

Action​

[/TD]
[TD]

Largest Num per Day​

[/TD]
[TD]

Largest Value​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

16/09/2015 00:09​

[/TD]
[TD]

NOK​

[/TD]
[TD]

100​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD]

17/09/2015​

[/TD]
[TD]

NOK​

[/TD]
[TD]

Deposit​

[/TD]
[TD]

5​

[/TD]
[TD]

4800​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

16/09/2015 00:15​

[/TD]
[TD]

USD​

[/TD]
[TD]

100​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

16/09/2015 05:54​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,30015​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

16/09/2015 14:34​

[/TD]
[TD]

NOK​

[/TD]
[TD]

100​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

16/09/2015 17:51​

[/TD]
[TD]

NOK​

[/TD]
[TD]

4800​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

16/09/2015 18:19​

[/TD]
[TD]

NOK​

[/TD]
[TD]

1000​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

16/09/2015 20:19​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,2699​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

16/09/2015 21:04​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,756​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

17/09/2015 05:30​

[/TD]
[TD]

NOK​

[/TD]
[TD]

2015​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

17/09/2015 06:50​

[/TD]
[TD]

NOK​

[/TD]
[TD]

200​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

17/09/2015 07:40​

[/TD]
[TD]

NOK​

[/TD]
[TD]

900​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

17/09/2015 08:49​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,52​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

17/09/2015 08:53​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,37501792​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

17/09/2015 11:07​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,02707997​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

17/09/2015 11:26​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,80150239​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

17/09/2015 11:28​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,0724​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

17/09/2015 11:28​

[/TD]
[TD]

LTC​

[/TD]
[TD]

0,5​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

17/09/2015 12:08​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,1051​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

17/09/2015 12:27​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,4​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD]

17/09/2015 16:30​

[/TD]
[TD]

NOK​

[/TD]
[TD]

2500​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD]

17/09/2015 16:33​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,025​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

23​

[/TD]
[TD]

17/09/2015 17:40​

[/TD]
[TD]

XBT​

[/TD]
[TD]

1,5​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

24​

[/TD]
[TD]

17/09/2015 17:55​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,025​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

25​

[/TD]
[TD]

17/09/2015 17:57​

[/TD]
[TD]

NOK​

[/TD]
[TD]

500​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Criteria in F2:H2

Array formula in I2
=MAX(FREQUENCY(IF(INT(A$2:A$25)>=F2-30,IF(F2>=INT(A$2:A$25),IF(B$2:B$25=G2,IF(D$2:D$25=H2,INT(A$2:A$25))))),INT(A$2:A$25)))
Ctrl+Shift+enter

Array formula in J2
=MAX(IF(INT(A$2:A$25)>=F2-30,IF(F2>=INT(A$2:A$25),IF(B$2:B$25=G$2,IF(D$2:D$25=H2,C$2:C$25)))))
Ctrl+Shift+Enter

Hope this helps

M.

 

[homie]

Thank you Marcelo Branco, I will try this and let you know how it pans out.

BTW, how did you manage to get everything in neat cells like that in the post on the forum? I know you can add a grid when you make the post, but then you have to manually type in all the data. I want to just be able to copy my data from Excel into a nice sheet like you have in your post. Is that possible?

 

[homie]

Maybe something like this
(i excluded the columns that doesn't matter for the calculation)


[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]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Timestamp​

[/TD]
[TD]

Currency​

[/TD]
[TD]

Amount European number​

[/TD]
[TD]

Action​

[/TD]
[TD][/TD]
[TD]

Ref Date​

[/TD]
[TD]

Currency​

[/TD]
[TD]

Action​

[/TD]
[TD]

Largest Num per Day​

[/TD]
[TD]

Largest Value​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

16/09/2015 00:09​

[/TD]
[TD]

NOK​

[/TD]
[TD]

100​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD]

17/09/2015​

[/TD]
[TD]

NOK​

[/TD]
[TD]

Deposit​

[/TD]
[TD]

5​

[/TD]
[TD]

4800​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

16/09/2015 00:15​

[/TD]
[TD]

USD​

[/TD]
[TD]

100​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

16/09/2015 05:54​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,30015​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

16/09/2015 14:34​

[/TD]
[TD]

NOK​

[/TD]
[TD]

100​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

16/09/2015 17:51​

[/TD]
[TD]

NOK​

[/TD]
[TD]

4800​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

16/09/2015 18:19​

[/TD]
[TD]

NOK​

[/TD]
[TD]

1000​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

16/09/2015 20:19​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,2699​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

16/09/2015 21:04​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,756​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

17/09/2015 05:30​

[/TD]
[TD]

NOK​

[/TD]
[TD]

2015​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

17/09/2015 06:50​

[/TD]
[TD]

NOK​

[/TD]
[TD]

200​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

17/09/2015 07:40​

[/TD]
[TD]

NOK​

[/TD]
[TD]

900​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

17/09/2015 08:49​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,52​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

17/09/2015 08:53​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,37501792​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

17/09/2015 11:07​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,02707997​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

17/09/2015 11:26​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,80150239​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

17/09/2015 11:28​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,0724​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

17/09/2015 11:28​

[/TD]
[TD]

LTC​

[/TD]
[TD]

0,5​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

17/09/2015 12:08​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,1051​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

17/09/2015 12:27​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,4​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD]

17/09/2015 16:30​

[/TD]
[TD]

NOK​

[/TD]
[TD]

2500​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD]

17/09/2015 16:33​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,025​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

23​

[/TD]
[TD]

17/09/2015 17:40​

[/TD]
[TD]

XBT​

[/TD]
[TD]

1,5​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

24​

[/TD]
[TD]

17/09/2015 17:55​

[/TD]
[TD]

XBT​

[/TD]
[TD]

0,025​

[/TD]
[TD]

Withdraw​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

25​

[/TD]
[TD]

17/09/2015 17:57​

[/TD]
[TD]

NOK​

[/TD]
[TD]

500​

[/TD]
[TD]

Deposit​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Criteria in F2:H2

Array formula in I2
=MAX(FREQUENCY(IF(INT(A$2:A$25)>=F2-30,IF(F2>=INT(A$2:A$25),IF(B$2:B$25=G2,IF(D$2:D$25=H2,INT(A$2:A$25))))),INT(A$2:A$25)))
Ctrl+Shift+enter

Array formula in J2
=MAX(IF(INT(A$2:A$25)>=F2-30,IF(F2>=INT(A$2:A$25),IF(B$2:B$25=G$2,IF(D$2:D$25=H2,C$2:C$25)))))
Ctrl+Shift+Enter

Hope this helps

M.

You genius, it works! I figured it would have to be some kind of Array formulas, but since I've never had to use one before I am not good with them and can't think of a solution with Array formulas.

If I want to find the equivalent minimum amount I figure I can just replace MAX with MIN in the formula?

For number of deposits though, I want to find the second smallest number of deposits for any given day in the previous 30 days. How do I do this?

 

[Marcelo Branco]

If I want to find the equivalent minimum amount I figure I can just replace MAX with MIN in the formula?

Yes, replace MAX by MIN

For number of deposits though, I want to find the second smallest number of deposits for any given day in the previous 30 days. How do I do this?

We need a different formula. Maybe this array formula
=AGGREGATE(15,6,1/(1/FREQUENCY(IF((INT(A$2:A$25)>=F2-30)*(F2>=INT(A$2:A$25))*(B$2:B$25=G2)*(D$2:D$25=H2),INT(A$2:A$25)),INT(A$2:A$25))),2)
Ctrl+Shift+Enter

M.

 

[homie]

Yes, replace MAX by MIN



We need a different formula. Maybe this array formula
=AGGREGATE(15,6,1/(1/FREQUENCY(IF((INT(A$2:A$25)>=F2-30)*(F2>=INT(A$2:A$25))*(B$2:B$25=G2)*(D$2:D$25=H2),INT(A$2:A$25)),INT(A$2:A$25))),2)
Ctrl+Shift+Enter

M.

Sorry, also need second smallest amount deposited. Turns out the smallest deposited amount is also always 0. Any help would be much appreciated

I'll try the one for the second smallest number of deposits now.

 

[Marcelo Branco]

Sorry, also need second smallest amount deposited. Turns out the smallest deposited amount is also always 0.

0? Using the data sample above the formula returned 100 for me.
Have you confirmed the formula with Ctrl+Shift+Enter?

Question:
Suppose there are 5 deposits like
10
10
80
80
100

What should be considered the 2nd smallest deposit?

M.

 

[homie]

0? Using the data sample above the formula returned 100 for me.
Have you confirmed the formula with Ctrl+Shift+Enter?

Question:
Suppose there are 5 deposits like
10
10
80
80
100

What should be considered the 2nd smallest deposit?

M.

The data sample above is just a tiny fraction of the whole dataset. I couldn't really post 10 000 rows of data, hehe.

To your question of what should be considered the second smallest deposit: 80. Sorry, I should have specified that earlier.

 

[Marcelo Branco]

The data sample above is just a tiny fraction of the whole dataset. I couldn't really post 10 000 rows of data, hehe.

I understand you cannot post the whole data, but seems a bit weird to me deposits whose values are equal to 0 (zero).
This really happens?

M.

 

[homie]

I understand you cannot post the whole data, but seems a bit weird to me deposits whose values are equal to 0 (zero).
This really happens?

M.

Well, I guess technically not, but pretty darn close. There are deposits of 0,0001 NOK. 1 USD = 8 NOK. So that's less than one thousand of one cent. Either way, it would actually be a lot more helpful for the purposes of this Excel Model, to rule out small deposits. Any deposits under NOK 100 are outliers, and not helpful to what I trying to do. Some IF function to rule out those maybe? Can you think of a way? The same for large deposits, like over 200 000 NOK.

The reason I am finding this difficult is that I have to do this all within one cell, and usually I just break things up. So I need to use Array and many functions within a cell, makes it hard to keep it straight.