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

 

[Eric W]

Try:

	A	B	C	D	E	F	G
1	Account	Date	Amount		Account	Mode or Max
2	571638	9/30/2018	3880		571638	3880
3	571638	8/31/2018	3880
4	571638	7/31/2018	5144
5	1234566	6/30/2018	1234	>

<tbody>
</tbody>

Sheet11​

Array Formulas Cell Formula F2 {=IFERROR(MODE(IF($A$2:$A$10=E2,$C$2:$C$10)),MAX(IF($A$2:$A$10=E2,$C$2:$C$10)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Israelinas]

Try:

	A	B	C	D	E	F	G
1	Account	Date	Amount		Account	Mode or Max
2	571638	9/30/2018	3880		571638	3880
3	571638	8/31/2018	3880
4	571638	7/31/2018	5144
5	1234566	6/30/2018	1234	>

<tbody>
</tbody>

Sheet11​

Array Formulas Cell Formula F2 {=IFERROR(MODE(IF($A$2:$A$10=E2,$C$2:$C$10)),MAX(IF($A$2:$A$10=E2,$C$2:$C$10)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Many thanks Eric!

The formula is what I am looking for, but in my case, I have a thousands of different account numbers (for each one can be more than one row), so I think I need to combine this formula with MATCH or VLOOKUP or any other formula? How can I do it?


Thanks,
Salinas

 

[Eric W]

I'm not entirely sure what you're asking, but if you want to perform the same formula for multiple accounts, just put the next account in E3, and copy the F2 formula to F3. You can add an entire column of accounts this way. Just make sure the ranges in the formula match your source data range.

 

[Israelinas]

Hi,

I mean I have a list of almost 50k rows with different account numbers when some of them are duplicated with a different date and different or same amount.
I tried to copy and paste transpose, but I have too many rows, so it cannot be converted to columns.

COLUMN [A] ACCOUNT
11223344
11223344
11223344
44332211
44332211
55667788
77889911
22557788
22557788

COLUMN DATE (different date for every row)

COLUMN [C] AMOUNT

1000
1000
1100
100
1500
2000
1300
2000
2000

Hope now it's more clear.

Many thanks,
Salinas

 

[Peter_SSs]

I'm not sure that it is yet clear.

What result (& why) do you want for these values of columns A & C? (I note that Eric's formula returns 5. Whilst 5 is one of the most frequent numbers, there are others just as frequent. Nor is it the highest number)

In case it isn't clear from the previous answer, what result do you want if the column D numbers were moved to column C?

Excel 2016
	A	B	C	D
1
2	11223344		5	5
3	11223344		5	5
4	11223344		9	5
5	11223344		9	16
6	11223344		6	7
7	11223344		7	7
8	11223344		7	7
Sample

 

[harshshastri]

you can use following array formula.

=IF(ISNA(MODE(IF(A$2:A$9=A2,IF(B$2:B$9=B$2:B$9,B$2:B$9)))),MAX(IF(A$2:A$9=A2,B$2:B$9)),MODE(IF(A$2:A$9=A2,IF(B$2:B$9=B$2:B$9,B$2:B$9))))

 

[Israelinas]

ACCOUT	DATE	AMOUNT	WHAT I NEED TO BE RETURNED	REASON
11223344	30/09/2018	1000	1000	Frequent amount for the same account
11223344	31/08/2018	1000	1000	Frequent amount for the same account
11223344	31/07/2018	1100	1000	Frequent amount for the same account
44332211	30/09/2018	100	1500	Maximum amount for the same account
44332211	31/08/2018	1500	1500	Maximum amount for the same account
55667788	30/09/2018	2000	2000	Only one row for this account
77889911	30/09/2018	1300	1300	Only one row for this account
22557788	30/09/2018	2000	2000	Frequent amount for the same account
22557788	31/08/2018	2000	2000	Frequent amount for the same account
22557788	31/07/2018	2000	2000	Frequent amount for the same account

<tbody>
</tbody>

Hi Peter,

The formula that Eric wrote is correct but only for static values.
I need to drag the formula down and the range is changed, and if I choose the whole column as a range it still returns incorrect values.
I need to find a way to make a group for every account, so the formula range will be only rows for the relevant account number.
Please see the table above as an example (in the real table I have 50K rows).


Thanks,
Salinas

 

[Peter_SSs]

Please see the table above as an example

Your table does not address the questions that I asked.

What result (& why) do you want for these values of columns A & C?

In case it isn't clear from the previous answer, what result do you want if the column D numbers were moved to column C?

Excel 2016
	A	B	C	D
1
2	11223344		5	5
3	11223344		5	5
4	11223344		9	5
5	11223344		9	16
6	11223344		6	7
7	11223344		7	7
8	11223344		7	7
Sample

 

[Israelinas]

Your table does not address the questions that I asked.

Hi Peter,

I understand your question, it is very good question.
I forgot to mention that in my case I don't have more than 3 rows for the same account number.

Hope this can help.


Many thanks for your help and reply.
Salinas