Israelinas
New Member
- Joined
- Dec 25, 2016
- Messages
- 25
Hi all,
It is a quite complicate formula, but I hope this is the right place.
I have a long list which presents (A) account id, (B) date, and (C) amount.
I have sorted the data according to the levels below:
1) account id - from biggest to smallest (doesn't really matter).
2) date from newest to oldest.
Now I am trying t build a formula that will return the correct value for me:
1) return the frequent (C) amount for each (A) account id.
2) If there isn't frequent one, return the maximum between them.
for example:
CASE 1:
I have 3 rows for account id: 571638.
for each row I have date: 1st with 30/09/2018, 2nd with 31/08/2018, 3rd with 31/07/2018.
for each row I have amount: 1st with 3,880, 2nd with 3,880, 3rd with 5,144.
In this case I want to get the frequent amount (3,880)
CASE 2:
all is the same instead of the amount: 1st with 3,880, 2nd with 3,980, 3rd with 4,150.
In this case I want to get the maximum.
Hope you can help me.
Many thanks
Salinas
It is a quite complicate formula, but I hope this is the right place.
I have a long list which presents (A) account id, (B) date, and (C) amount.
I have sorted the data according to the levels below:
1) account id - from biggest to smallest (doesn't really matter).
2) date from newest to oldest.
Now I am trying t build a formula that will return the correct value for me:
1) return the frequent (C) amount for each (A) account id.
2) If there isn't frequent one, return the maximum between them.
for example:
CASE 1:
I have 3 rows for account id: 571638.
for each row I have date: 1st with 30/09/2018, 2nd with 31/08/2018, 3rd with 31/07/2018.
for each row I have amount: 1st with 3,880, 2nd with 3,880, 3rd with 5,144.
In this case I want to get the frequent amount (3,880)
CASE 2:
all is the same instead of the amount: 1st with 3,880, 2nd with 3,980, 3rd with 4,150.
In this case I want to get the maximum.
Hope you can help me.
Many thanks
Salinas