Find the Largest number based on date and trade

[Garvey1973]

I am trying to find the largest number based on the date, then quantity on trade for each day of the month. I currently do it manually each day but am looking for a formula to do it for me without using the ctrl+shift+enter function. Looking for the formula in largest trade column

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Quantity[/TD]
[TD]Quantity on trade[/TD]
[TD]Largest trade[/TD]
[/TR]
[TR]
[TD]1/1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1[/TD]
[TD]14[/TD]
[TD]23[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]1/2[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2[/TD]
[TD]32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2[/TD]
[TD]8[/TD]
[TD]40[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]1/3[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/3[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/3[/TD]
[TD]3[/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/3[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/3[/TD]
[TD]11[/TD]
[TD]11
[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be appreciated.

 

[Aladin Akyurek]

Is the location of 14 deliberate in the column for Largest trade as it does not align with the location in Quantity on trade?

 

[Garvey1973]

yes --- the 14 is the total amount of the parts in the individual trades in the previous column --- for example if I buy 7, buy 4 and sell 3 --- the absolute is 14

 

[Aladin Akyurek]

yes --- the 14 is the total amount of the parts in the individual trades in the previous column --- for example if I buy 7, buy 4 and sell 3 --- the absolute is 14

This 14 is reported at the row of 1/3, 11, and 11, not in the row of 1/3, 3, and 14. Is that intended?

 

[Garvey1973]

yes --- so each trade may consist of more than 1 part - on 1/3, the 7, 4 and 3 in the quantity column are all part of 1 trade, making it 14 in the quantity on trade column ---- i then need to find the largest number in the largest trade column based on the date.

 

[Aladin Akyurek]

yes --- so each trade may consist of more than 1 part - on 1/3, the 7, 4 and 3 in the quantity column are all part of 1 trade, making it 14 in the quantity on trade column ---- i then need to find the largest number in the largest trade column based on the date.

If you have MAXIFS on your system, just enter in D2 and copy down:

=IF(MAXIFS($D$2:$D$13,$A$2:$A$13,A4)=C4,MAXIFS($D$2:$D$13,$A$2:$A$13,A4),"")

Otherwise, in D2 control+shift+enter, no just enter, and copy down:

=IF(MAX(IF($A$2:$A$13=A4,$C$2:$C$13))=C4,MAX(IF($A$2:$A$13=A4,$C$2:$C$13)),"")

 

[Garvey1973]

If you have MAXIFS on your system, just enter in D2 and copy down:

=IF(MAXIFS($D$2:$D$13,$A$2:$A$13,A4)=C4,MAXIFS($D$2:$D$13,$A$2:$A$13,A4),"")

Otherwise, in D2 control+shift+enter, no just enter, and copy down:

=IF(MAX(IF($A$2:$A$13=A4,$C$2:$C$13))=C4,MAX(IF($A$2:$A$13=A4,$C$2:$C$13)),"")

So I cant get this to work --- what I am looking to do is to get the largest number in column c (quantity on trade) for each trade date --- to be displayed in column d on the last line for that particular date. In my example there would be 3 (23 30 14) the line inbetween would be blank because of the same date

 

[Aladin Akyurek]

Book1
	A	B	C	D	E
1	Date	Quantity	Quantity on trade	Largest trade
2	1-Jan	3	3
3	1-Jan	9
4	1-Jan	14	23	23	23
5	2-Jan	10
6	2-Jan	10	20
7	2-Jan	32
8	2-Jan	8	40	40	40
9	3-Jan	7
10	3-Jan	4
11	3-Jan	3	14	14	14
12	3-Jan	7	7
13	3-Jan	11	11
14
15				MAXIFS	MAX/IF
Sheet1

Do you have MAXIFS? If yes...

=IF(MAXIFS($C$2:$C$13,$A$2:$A$13,A2)=C2,MAXIFS($C$2:$C$13,$A$2:$A$13,A2),"")

Othwerwise, control+shift+enter…

=IF(MAX(IF($A$2:$A$13=A2,$C$2:$C$13))=C2,MAX(IF($A$2:$A$13=A2,$C$2:$C$13)),"")

If the results must be aligned with the last occurrences of dates (I kept asking asking about this)…

=IF($A2="","",IF(COUNTIFS($A$2:$A2,A2)=COUNTIFS($A$2:$A$13,A2),MAXIFS($C$2:$C$13,$A$2:$A$13,A2),""))