Return the frequent value or the maximum value in one formula

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
 
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
ABCD
1ACCOUTDATEAMOUNTFREQ/MAX
21122334430/09/201810001000
31122334431/08/201810001000
41122334431/07/201811001000
54433221130/09/20181001500
64433221131/08/201815001500
75566778830/09/201820002000
87788991130/09/201813001300
92255778830/09/201820002000
102255778831/08/201820002000
112255778831/07/201820002000
Mode or Max
 
Last edited:
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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

ABCD

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="align: right"]ACCOUT[/TD]
[TD="align: right"]DATE[/TD]
[TD="align: right"]AMOUNT[/TD]
[TD="align: right"]FREQ/MAX[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]11223344[/TD]
[TD="align: right"]30/09/2018[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: right"]11223344[/TD]
[TD="align: right"]31/08/2018[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]11223344[/TD]
[TD="align: right"]31/07/2018[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"]1000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: right"]44332211[/TD]
[TD="align: right"]30/09/2018[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: right"]44332211[/TD]
[TD="align: right"]31/08/2018[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]1500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: right"]55667788[/TD]
[TD="align: right"]30/09/2018[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: right"]77889911[/TD]
[TD="align: right"]30/09/2018[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"]1300[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: right"]22557788[/TD]
[TD="align: right"]30/09/2018[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]
[TD="align: right"]22557788[/TD]
[TD="align: right"]31/08/2018[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]
[TD="align: right"]22557788[/TD]
[TD="align: right"]31/07/2018[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2000[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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 :)
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,214
Members
453,024
Latest member
Wingit77

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