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
If i'm understanding correctly you need the smallest deposit (or the second smallest) provided the value is greater than a very small deposit. If i'm right you have to define what is the very small value that should not be taken in account in the formula.
So the formula, to get the value you want, should use a condition like:
> 10.10
that is: greater than 10 cents for example.

Are you saying that any deposit under 100 should be disregard?

M.
 
Last edited:
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Crap, I just realized one thing: I was not specific enough in my Orignial Post regarding largest amound deposited for any given day in the previous 30 days. I did not mean the largest singledeposit of any given day, I meant the most that was deposited on any given day.
Example:
Deposits on 02.06.2015:
10
250
400
500

Deposits on 03.06.2015:
700
11
50
90


If the formula only looked at these two dates it should NOT return "700" which is the largest single deposit over all the days compared. It should return 1160, which is the largest amount deposited in any single day of the days compared.

Sorry for not being clear earlier, I didn't consider the opportunity that it could be interpreted several ways.
 
Upvote 0
Marcelo Branco said:
If i'm understanding correctly you need the smallest deposit (or the second smallest) provided the value is greater than a very small deposit. If i'm right you have to define what is the very small value that should not be taken in account in the formula.
So the formula, to get the value you want, should use a condition like:
> 10.10
that is: greater than 10 cents for example.

Are you saying that any deposit under 100 should be disregard?

M.
Click to expand...

My apologies, I have asked for the wrong information. I didn't think straight and confused the matter, sorry.

Of course the pertinant data isn't the largest and smallest single deposit in the previous 30 days. No, it would be the day with the lowest number of deposits, and the day with the lowest amount deposited. So total number of deposits for each day (we have that already), but then the total amount deposited for each day. Comparing the total amounts deposited for each of the 30 previous days, what is lowest amount deposited for any given day? THAT is the real question
EDIT: Also, what is the highest amount deposited for any given day (adding all deposits up per day)?
 
Last edited:
Upvote 0
This is getting pretty confusing. :confused:
I hope that at least the first part, Largest number of deposits per day, is correct.

Now, until you clarify everything, i will try the highest amount deposited any day, provided that such day is in the specified range (RefDate-30 days and Ref Date)

To make things easier i created a new column B (Day)
Formula in B2 copied down
=INT(A2)


[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]
Day​
[/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 Deposit per day​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
16/09/2015 00:09​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
100​
[/TD]
[TD]
Deposit​
[/TD]
[TD][/TD]
[TD]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
Deposit​
[/TD]
[TD]
6115​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
16/09/2015 00:15​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
USD​
[/TD]
[TD]
100​
[/TD]
[TD]
Deposit​
[/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]
16/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,30015​
[/TD]
[TD]
Withdraw​
[/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]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
100​
[/TD]
[TD]
Deposit​
[/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]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
4800​
[/TD]
[TD]
Deposit​
[/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]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
1000​
[/TD]
[TD]
Deposit​
[/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]
16/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,2699​
[/TD]
[TD]
Withdraw​
[/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]
16/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,756​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
2015​
[/TD]
[TD]
Deposit​
[/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]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
200​
[/TD]
[TD]
Deposit​
[/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]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
900​
[/TD]
[TD]
Deposit​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,52​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,37501792​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,02707997​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,80150239​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,0724​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
LTC​
[/TD]
[TD]
0,5​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,1051​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,4​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
2500​
[/TD]
[TD]
Deposit​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,025​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
1,5​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,025​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
500​
[/TD]
[TD]
Deposit​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in J2
=MAX(IF(B$2:B$25>=G2-30,IF(G2>=B$2:B$25,IF(C$2:C25=H2,IF(E$2:E$25=I2,SUMIFS(D$2:D$25,B$2:B$25,B$2:B$25,C$2:C$25,H2,E$2:E$25,I2))))))
Ctrl+Shift+Enter

Please, confirm if this does what you really need

M,
 
Upvote 0
Marcelo Branco said:
This is getting pretty confusing. :confused:
I hope that at least the first part, Largest number of deposits per day, is correct.

Now, until you clarify everything, i will try the highest amount deposited any day, provided that such day is in the specified range (RefDate-30 days and Ref Date)

To make things easier i created a new column B (Day)
Formula in B2 copied down
=INT(A2)


[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]
Day​
[/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 Deposit per day​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
16/09/2015 00:09​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
100​
[/TD]
[TD]
Deposit​
[/TD]
[TD][/TD]
[TD]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
Deposit​
[/TD]
[TD]
6115​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
16/09/2015 00:15​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
USD​
[/TD]
[TD]
100​
[/TD]
[TD]
Deposit​
[/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]
16/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,30015​
[/TD]
[TD]
Withdraw​
[/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]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
100​
[/TD]
[TD]
Deposit​
[/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]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
4800​
[/TD]
[TD]
Deposit​
[/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]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
1000​
[/TD]
[TD]
Deposit​
[/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]
16/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,2699​
[/TD]
[TD]
Withdraw​
[/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]
16/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,756​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
2015​
[/TD]
[TD]
Deposit​
[/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]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
200​
[/TD]
[TD]
Deposit​
[/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]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
900​
[/TD]
[TD]
Deposit​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,52​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,37501792​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,02707997​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,80150239​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,0724​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
LTC​
[/TD]
[TD]
0,5​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,1051​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,4​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
2500​
[/TD]
[TD]
Deposit​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,025​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
1,5​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,025​
[/TD]
[TD]
Withdraw​
[/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]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
500​
[/TD]
[TD]
Deposit​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in J2
=MAX(IF(B$2:B$25>=G2-30,IF(G2>=B$2:B$25,IF(C$2:C25=H2,IF(E$2:E$25=I2,SUMIFS(D$2:D$25,B$2:B$25,B$2:B$25,C$2:C$25,H2,E$2:E$25,I2))))))
Ctrl+Shift+Enter

Please, confirm if this does what you really need

M,
Click to expand...

I can confirm that indeed it does! You sure know your Arrays, thanks a lot!
 
Upvote 0
Marcelo Branco said:
Great! Now we have two problems solved.
What else? :)

M.
Click to expand...

Haha, we'll have to see as this model develops :D
Right now I am building off these two solutions, putting them to use and expanding upon them. I got a feeling there will be new hurdles down the line though :P
I see you're from Rio, cool! I will definitively visit there one day, probably next summer. This year I was deciding between Rio and Bali, but finally decided on Bali. I leave in a week :D
 
Upvote 0
homie said:
Haha, we'll have to see as this model develops :D
Right now I am building off these two solutions, putting them to use and expanding upon them. I got a feeling there will be new hurdles down the line though :P
I see you're from Rio, cool! I will definitively visit there one day, probably next summer. This year I was deciding between Rio and Bali, but finally decided on Bali. I leave in a week :D
Click to expand...

With very large data the performance of array formulas can be poor :(. If so, you will need a macro.

Good luck in Bali!

M.
 
Upvote 0
Marcelo Branco said:
With very large data the performance of array formulas can be poor :(. If so, you will need a macro.

Good luck in Bali!

M.
Click to expand...
Thanks!

But yeah, I noticed this! I finished all the formulas, which were derived from the ones you posted here. I have 16 cells in a row that I will copy downward. When I finished I will have 32 cells in a row that I will copy downward about 120 rows. Just to test whether I had written the 16 formulas correct so far, I tried copying them downward 14 rows and it took 10 minutes for Excel to do it!!! When I get 32 cells with formulas in a row and copying down 120 rows I am afraid it will crash :O
We'll have to see.

How is Macros a solution? I thought Macros were "heavier" and slowed Excel down more, no?
 
Upvote 0
Marcelo Branco said:
With very large data the performance of array formulas can be poor :(. If so, you will need a macro.

Good luck in Bali!

M.
Click to expand...

P.S. In my Array formulas I have references for 99 000 rows, e.g. the equivalent of "B$2:B$25" in your formula above I have "B$8:B$99000". Does that slow down Excel? I have 10 000 rows in my dataset so far, but it might be expanded to 20 000 when I get more data, I just wrote 99 000 to be safe. But does this slow down Excel even if there is no data in most of these rows?
 
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