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:

Timestamp recognized by ExcelTimestamp recognized by Excel, roundedRemove USD,EUR,NOK or XBT, ETH, LTC from stringAmount American number as textCurrencyAmount European numberAction
16.09.2015 00:0916.09.2015 00:099/16/2015 0:09,100100NOK100Deposit
16.09.2015 00:1516.09.2015 00:159/16/2015 0:15,100100USD100Deposit
16.09.2015 05:5416.09.2015 05:549/16/2015 5:54,0.300150.30015XBT0,30015Withdraw
16.09.2015 14:3416.09.2015 14:349/16/2015 14:34,100100NOK100Deposit
16.09.2015 17:5116.09.2015 17:519/16/2015 17:51,48004800NOK4800Deposit
16.09.2015 18:1916.09.2015 18:199/16/2015 18:19,10001000NOK1000Deposit
16.09.2015 20:1916.09.2015 20:199/16/2015 20:19,0.26990.2699XBT0,2699Withdraw
16.09.2015 21:0416.09.2015 21:049/16/2015 21:04,0.7560.756XBT0,756Withdraw
17.09.2015 05:3017.09.2015 05:309/17/2015 5:30,20152015NOK2015Deposit
17.09.2015 06:5017.09.2015 06:509/17/2015 6:50,200200NOK200Deposit
17.09.2015 07:4017.09.2015 07:409/17/2015 7:40,900900NOK900Deposit
17.09.2015 08:4917.09.2015 08:499/17/2015 8:49,0.520.52XBT0,52Withdraw
17.09.2015 08:5317.09.2015 08:539/17/2015 8:53,0.375017920.37501792XBT0,37501792Withdraw
17.09.2015 11:0717.09.2015 11:079/17/2015 11:07,0.027079970.02707997XBT0,02707997Withdraw
17.09.2015 11:2617.09.2015 11:269/17/2015 11:26,0.801502390.80150239XBT0,80150239Withdraw
17.09.2015 11:2817.09.2015 11:289/17/2015 11:28,0.07240.0724XBT0,0724Withdraw
17.09.2015 11:2817.09.2015 11:289/17/2015 11:28,0.50.5LTC0,5Withdraw
17.09.2015 12:0817.09.2015 12:089/17/2015 12:08,0.10510.1051XBT0,1051Withdraw
17.09.2015 12:2717.09.2015 12:279/17/2015 12:27,0.40.4XBT0,4Withdraw
17.09.2015 16:3017.09.2015 16:309/17/2015 16:30,25002500NOK2500Deposit
17.09.2015 16:3317.09.2015 16:339/17/2015 16:33,0.0250.025XBT0,025Withdraw
17.09.2015 17:4017.09.2015 17:409/17/2015 17:40,1.51.5XBT1,5Withdraw
17.09.2015 17:5517.09.2015 17:559/17/2015 17:55,0.0250.025XBT0,025Withdraw
17.09.2015 17:5717.09.2015 17:579/17/2015 17:57,500500NOK500Deposit

<tbody>
</tbody>
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Maybe something like this
(i excluded the columns that doesn't matter for the calculation)


A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
Timestamp​
Currency​
Amount European number​
Action​
Ref Date​
Currency​
Action​
Largest Num per Day​
Largest Value​
2​
16/09/2015 00:09​
NOK​
100​
Deposit​
17/09/2015​
NOK​
Deposit​
5​
4800​
3​
16/09/2015 00:15​
USD​
100​
Deposit​
4​
16/09/2015 05:54​
XBT​
0,30015​
Withdraw​
5​
16/09/2015 14:34​
NOK​
100​
Deposit​
6​
16/09/2015 17:51​
NOK​
4800​
Deposit​
7​
16/09/2015 18:19​
NOK​
1000​
Deposit​
8​
16/09/2015 20:19​
XBT​
0,2699​
Withdraw​
9​
16/09/2015 21:04​
XBT​
0,756​
Withdraw​
10​
17/09/2015 05:30​
NOK​
2015​
Deposit​
11​
17/09/2015 06:50​
NOK​
200​
Deposit​
12​
17/09/2015 07:40​
NOK​
900​
Deposit​
13​
17/09/2015 08:49​
XBT​
0,52​
Withdraw​
14​
17/09/2015 08:53​
XBT​
0,37501792​
Withdraw​
15​
17/09/2015 11:07​
XBT​
0,02707997​
Withdraw​
16​
17/09/2015 11:26​
XBT​
0,80150239​
Withdraw​
17​
17/09/2015 11:28​
XBT​
0,0724​
Withdraw​
18​
17/09/2015 11:28​
LTC​
0,5​
Withdraw​
19​
17/09/2015 12:08​
XBT​
0,1051​
Withdraw​
20​
17/09/2015 12:27​
XBT​
0,4​
Withdraw​
21​
17/09/2015 16:30​
NOK​
2500​
Deposit​
22​
17/09/2015 16:33​
XBT​
0,025​
Withdraw​
23​
17/09/2015 17:40​
XBT​
1,5​
Withdraw​
24​
17/09/2015 17:55​
XBT​
0,025​
Withdraw​
25​
17/09/2015 17:57​
NOK​
500​
Deposit​

<tbody>
</tbody>


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.
 
Upvote 0
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?
 
Upvote 0
Marcelo Branco said:
Maybe something like this
(i excluded the columns that doesn't matter for the calculation)


A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
Timestamp​
Currency​
Amount European number​
Action​
Ref Date​
Currency​
Action​
Largest Num per Day​
Largest Value​
2​
16/09/2015 00:09​
NOK​
100​
Deposit​
17/09/2015​
NOK​
Deposit​
5​
4800​
3​
16/09/2015 00:15​
USD​
100​
Deposit​
4​
16/09/2015 05:54​
XBT​
0,30015​
Withdraw​
5​
16/09/2015 14:34​
NOK​
100​
Deposit​
6​
16/09/2015 17:51​
NOK​
4800​
Deposit​
7​
16/09/2015 18:19​
NOK​
1000​
Deposit​
8​
16/09/2015 20:19​
XBT​
0,2699​
Withdraw​
9​
16/09/2015 21:04​
XBT​
0,756​
Withdraw​
10​
17/09/2015 05:30​
NOK​
2015​
Deposit​
11​
17/09/2015 06:50​
NOK​
200​
Deposit​
12​
17/09/2015 07:40​
NOK​
900​
Deposit​
13​
17/09/2015 08:49​
XBT​
0,52​
Withdraw​
14​
17/09/2015 08:53​
XBT​
0,37501792​
Withdraw​
15​
17/09/2015 11:07​
XBT​
0,02707997​
Withdraw​
16​
17/09/2015 11:26​
XBT​
0,80150239​
Withdraw​
17​
17/09/2015 11:28​
XBT​
0,0724​
Withdraw​
18​
17/09/2015 11:28​
LTC​
0,5​
Withdraw​
19​
17/09/2015 12:08​
XBT​
0,1051​
Withdraw​
20​
17/09/2015 12:27​
XBT​
0,4​
Withdraw​
21​
17/09/2015 16:30​
NOK​
2500​
Deposit​
22​
17/09/2015 16:33​
XBT​
0,025​
Withdraw​
23​
17/09/2015 17:40​
XBT​
1,5​
Withdraw​
24​
17/09/2015 17:55​
XBT​
0,025​
Withdraw​
25​
17/09/2015 17:57​
NOK​
500​
Deposit​

<tbody>
</tbody>


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.
Click to expand...

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?
 
Upvote 0
homie said:
If I want to find the equivalent minimum amount I figure I can just replace MAX with MIN in the formula?
Click to expand...

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?
Click to expand...

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.
 
Upvote 0
Marcelo Branco said:
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.
Click to expand...

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.
 
Upvote 0
homie said:
Sorry, also need second smallest amount deposited. Turns out the smallest deposited amount is also always 0.
Click to expand...

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.
 
Upvote 0
Marcelo Branco said:
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.
Click to expand...

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.
 
Upvote 0
homie said:
The data sample above is just a tiny fraction of the whole dataset. I couldn't really post 10 000 rows of data, hehe.
Click to expand...

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.
 
Upvote 0
Marcelo Branco said:
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.
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Need to convert days, hours, minutes and second text into hh:mm:ss
Replies
7
Views
89
Aadya
A
K
  • Question
conditional formatting question
Replies
2
Views
157
kerm007
K
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
311
awoohaw
awoohaw
D
  • Solved
Filter large amount of data by multiple constraints
Replies
7
Views
196
Peter_SSs
Peter_SSs
J
  • Solved
Add 1 day to a date within a range as long as 2 conditions are met [without macros]
Replies
4
Views
566
juanam
J

Forum statistics

Threads
1,215,022
Messages
6,122,726
Members
449,093
Latest member
Mnur

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