Return the frequent value or the maximum value in one formula

[Israelinas]

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

 

[Peter_SSs]

I forgot to mention that in my case I don't have more than 3 rows for the same account number.

Thanks. In that case the formula can process much smaller ranges.
Try this copied down. Once again, like Eric's it must be confirmed with Ctrl+Shift+Enter before copying down.

Excel Workbook
	A	B	C	D
1	ACCOUT	DATE	AMOUNT	FREQ/MAX
2	11223344	30/09/2018	1000	1000
3	11223344	31/08/2018	1000	1000
4	11223344	31/07/2018	1100	1000
5	44332211	30/09/2018	100	1500
6	44332211	31/08/2018	1500	1500
7	55667788	30/09/2018	2000	2000
8	77889911	30/09/2018	1300	1300
9	22557788	30/09/2018	2000	2000
10	22557788	31/08/2018	2000	2000
11	22557788	31/07/2018	2000	2000
Mode or Max

 

[Israelinas]

Thanks. In that case the formula can process much smaller ranges.
Try this copied down. Once again, like Eric's it must be confirmed with Ctrl+Shift+Enter before copying down.

Mode or Max

	A	B	C	D
1	ACCOUT	DATE	AMOUNT	FREQ/MAX
2	11223344	30/09/2018	1000	1000
3	11223344	31/08/2018	1000	1000
4	11223344	31/07/2018	1100	1000
5	44332211	30/09/2018	100	1500
6	44332211	31/08/2018	1500	1500
7	55667788	30/09/2018	2000	2000
8	77889911	30/09/2018	1300	1300
9	22557788	30/09/2018	2000	2000
10	22557788	31/08/2018	2000	2000
11	22557788	31/07/2018	2000	2000

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 75px;"><col style="width: 83px;"><col style="width: 74px;"><col style="width: 86px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D2 {=IF(A2=A1,D1,IFERROR(MODE(IF(A2:A4=A2,C2:C4)),MAX(IF(A2:A4=A2,C2:C4))))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Yay! It works!

Many many thanks

 

[Peter_SSs]

Yay! It works!

Many many thanks

You're welcome. Eric did the hard work early though.