Days between in- and outgoing for a total average.

[Audorin]

Hello everyone,

I`ve been going absolutely mad with an issue I just can't seem to get my head around.
I'll try to give an example to best detail the issue on hand: Imagine I get a penalty fee by having a too fast of a turnover of the cattle I trade, so If I re-sell the first day it's 17% fee and if I sell after 113 days it's as low as 7% fee.

(Example just given for context)

I've sorted all the transactions by date, name if it's sold or bought, price.

So all I want is the average amount of days between each transaction when the the cattle was bought, to the date the cattle was sold. Instead of calling them cattle, cattle1, and so on..I've given them personal names. (Tore Vagle, Rafal etc.) So I'm trying to get a formula to use the name as a criteria, this since the name can jump around on the list due to having sorting functions (sorting by date, name, value and so on). The only transactions I'm out after are the ones that have been bought and sold, not just bought. So how can I go about using the personal name as a criteria, taking the IN date, then the OUT date (on two seperate sheets) and calculating the number of days the cattle have been in my ownership.

The average days ownership per (all) cattle will help me calculate the fee on all the transactions in a very simple matter. It's just that the formula it self seems a bit hard to make now when my brain has completely shut down on the issue.

Hopeing for a helping hand here, at least to lead me in the right direction!

Thank you!

Per Å

 

[Audorin]

you are welcome

it should work with different sheets , but needs the $ as i posted on both ranges

Hey again. Sorry to bother you etaf. I've now pretty much finished off the dashboard to the excel sheet now. I've learned a lot the last few days, but it's still something I'm struggling with since we are doing the index searches now.
How should I go about finding the the best trade %-based. So I need the formula to search the sold list again, and find the the trade that gave me the most profit percentage-wise. Do you have any suggestion for this based on what I linked here earlier?

LLL.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1			STATS	SOLD	123%				RECORD SALE				$ 1,940,000					STATS	BUY
2			TOTAL SOLD		$ 12,727,095				Name				Tonino Chiappori					TOTAL BOUGHT		$ 8,281,869
3			AVG SOLD		$ 259,737				TOTAL FEE				$ 1,511,380					AVG BOUGHT		$ 116,646
4			TOTAL TRANSACTIONS		49				AVG DAYS IN CLUB / FEE		13.4	DAYS	$ 31,297					TOTAL TRANSACTIONS		71
5	Date	Week / Season	Players	Action	To / From	ImportedPrice	TSI then	®	Price	TSI	Days	Days owned	Fee	Income		Date	Column1	Players	Action	To/From	ImportedPrice	TSI then	®	Price	TSI
6	5/10/21	(7/79)	Xander Abbenhuis	Sold	Grolsche Boys	1 800 000 Kroner	1 760	(R)	$ 180,000.00	1,760	29/9/21	6	$ 22,194	$ 157,805.88		29/9/21	(6/79)	Xander Abbenhuis	Bought	Zanarkand Duggles	1 020 000 Kroner	1 710	(R)	102000	1,710
7	10/10/21	(7/79)	Willy Courtman	Sold	schleiferteam	1 610 000 Kroner		(R)	$ 161,000.00	750	29/9/21	11	$ 19,115	$ 141,884.79		29/9/21	(6/79)	Rafał Jaśkiewicz	Bought	GKS Najmansi	500 000 Kroner	1 290	(R)	50000	1,290
8	10/10/21	(7/79)	Rafał Jaśkiewicz	Sold	schleiferteam	1 050 000 Kroner	1 090	(R)	$ 105,000.00	1,090	29/9/21	11	$ 12,466	$ 92,533.56		29/9/21	(6/79)	Andreas Weißburg	Bought	TC Shanghai	140 000 Kroner	1 390	(R)	14000	1,390
9	10/10/21	(7/79)	Andreas Weißburg	Sold	Escudos Portadores Del Sur	798 000 Kroner	1 230	(R)	$ 79,800.00	1,230	29/9/21	11	$ 9,474	$ 70,325.50		29/9/21	(6/79)	Willy Courtman	Bought	The Rampaging Raccoons	140 000 Kroner		(R)	14000	780
10	10/10/21	(7/79)	Dillan Rossi	Sold	FC Plusvalenza	210 000 Kroner		(R)	$ 21,000.00	450	3/10/21	7	$ 2,570	$ 18,430.28		29/9/21	(6/79)	Hugo Palma	Bought	F.C.-Nox	99 999 Kroner	1 500	(R)	9999.9	1,500
11	14/10/21	(8/79)	Wesley Febres	Sold	Le Saviour Messi	500 000 Kroner	1 290	(R)	$ 50,000.00	1,290	1/10/21	13	$ 5,848	$ 44,151.88		29/9/21	(6/79)	Egidius Zilkens	Bought	Lampegat United	70 000 Kroner		(R)	7000	520
12	17/10/21	(8/79)	Beňadik Škodka	Sold	FC Vitrolles United	1 600 000 Kroner	1 820	(R)	$ 160,000.00	1,820	10/10/21	7	$ 19,579	$ 140,421.17		29/9/21	(6/79)	(Fired player)	Bought	Palokka City	10 000 Kroner		(R)	1000	650
13	17/10/21	(8/79)	Marc-Vivien Yapi	Sold	Squadretta tonda	1 280 000 Kroner		(R)	$ 128,000.00	830	2/10/21	15	$ 14,750	$ 113,250.33		29/9/21	(6/79)	Antônio Souza Cunha	Bought	Synbios Force	10 000 Kroner		(R)	1000	670
14	24/10/21	(9/79)	Adolfo Lamas Cabrera	Sold	FC Berolina Mitte	4 437 000 Kroner	6 280	(R)	$ 443,700.00	6,280	11/10/21	13	$ 51,896	$ 391,803.81		30/9/21	(6/79)	Gerben Bruneel	Bought	Cadbury Flake FC	30 000 Kroner		(R)	3000	940
15	24/10/21	(9/79)	Ángel David Fourcade	Sold	Herminator FC	2 200 000 Kroner	2 010	(R)	$ 220,000.00	2,010	18/10/21	6	$ 27,126	$ 192,873.85		1/10/21	(6/79)	Lasse Sejersbøl	Bought	Nongkhai Farmers FC	10 000 Kroner		(R)	1000	720
16	24/10/21	(9/79)	Egidius Zilkens	Sold	daviola	150 000 Kroner		(R)	$ 15,000.00	690	29/9/21	25	$ 1,606	$ 13,393.77		1/10/21	(6/79)	Wesley Febres	Bought	Pigi AF	10 000 Kroner	1 200	(R)	1000	1,200
17	24/10/21	(9/79)	Abdulrahman Haroon Al-Riyamy	Sold	Bazinga	30 000 Kroner		(R)	$ 3,000.00	550	3/10/21	21	$ 331	$ 2,669.27		1/10/21	(6/79)	Øivind Klette	Bought	Brave Bandidos	10 000 Kroner		(R)	1000	760
18	24/10/21	(9/79)	(Fired player)	Sold	Poli 1921	10 000 Kroner		(R)	$ 1,000.00	660	29/9/21	25	$ 107	$ 892.92		2/10/21	(6/79)	Marc-Vivien Yapi	Bought	Les Eléphants Des Montagnes	30 000 Kroner		(R)	3000	840
19	27/10/21	(10/79)	Fathi Saifi	Sold	Prison SRC	950 000 Kroner	1 500	(R)	$ 95,000.00	1,500	20/10/21	7	$ 11,625	$ 83,375.07		3/10/21	(6/79)	Dillan Rossi	Bought	Pressing Forward FC	30 000 Kroner		(R)	3000	620
20	30/10/21	(10/79)	Firdovsi Əhmədov	Sold	Iceland Tigers	5 100 000 Kroner	2 640	(R)	$ 510,000.00	2,640	25/10/21	5	$ 63,364	$ 446,636.01		3/10/21	(6/79)	Abdulrahman Haroon Al-Riyamy	Bought	Green Fury	10 000 Kroner		(R)	1000	560
Sold Bought

Cell Formulas
Range	Formula
E1	E1	=SUM(E3/T3)-1
E2	E2	=SUM(I6:I10003)
E3	E3	=AVERAGE(I6:I10003)
E4	E4	=COUNTA(C6:C10003)
K4	K4	=AVERAGE(DatasebaseSOLD[Days owned])
M1	M1	=MAX(DatasebaseSOLD[Price])
M2	M2	=INDEX(DatasebaseSOLD[Players],MATCH(MAX(DatasebaseSOLD[Price]),DatasebaseSOLD[Price],0))
M3	M3	=SUM(M7:M55)
M4	M4	=AVERAGE(DatasebaseSOLD[Fee])
T2	T2	=SUM(X$6:$X9999)
T3	T3	=AVERAGE(X6:$X$9999)
T4	T4	=COUNTA(T6:T10003)
I6:I20,X6:X20	I6	=VALUE(TEXTJOIN("",TRUE,IFERROR(MID(F6,SEQUENCE(20),1)+0,"")))/10
J6:J20,Y6:Y20	J6	=VALUE(TEXTJOIN("",TRUE,IFERROR(MID(G6,SEQUENCE(20),1)+0,"")))
K6:K20	K6	=IFNA(INDEX($P$6:$P$76,MATCH(C6,$R$6:$R$76,0)),"")
L6:L20	L6	=IFERROR([@Date]-[@Days],"")
M6:M20	M6	= (0.03*[@Price])+((9.91*0.99^[@[Days owned]])/100)*[@Price]
N6:N20	N6	=[@Price]-[@Fee]

 

[etaf]

setup a column with the profit & percent and any criteria you want to add , maybe time held , inflation etc
for example something thats held for 10years has that money losing money based on inflation etc
then once you have the the "real" profit, you can just use a MAXIFS () or max with a lookup to find the name

you can bring the price bought for over using the same index/match - but instead of date bought, bring over the TOTAL Amount bought for

now you have the price , you can subtract the price sold from the price purchased - less any fees and get a margin.
Now you can work out the % - depends on if you incluse the fees or not or any loss due to time owned

then a MAX() with an index
=Index( Name, match( Max(column of profits) , Column of profits, 0))
simplified example as posted earlier with the extra columns added

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2												profit name	Harry
3	Bought Sheet					Sold Sheet
4	date	Cattle Name	column 1	price		date	Cattle Name	column 1	price	Date Bought	Days Owned	Price purchased	profit
5	1/1/21	fred	Bought	1.5		10/1/21	fred	sold	200	1/1/21	273	1.5	198.5
6	10/1/21	Harry	Bought	2.75		11/1/21	Harry	sold	300	10/1/21	31	2.75	297.25
7
Sheet3

Cell Formulas
Range	Formula
M2	M2	=INDEX(G5:G6,MATCH(MAX(M5:M6),M5:M6,0))
J5:J6	J5	=INDEX($A$5:$A$6,MATCH(G5,$B$5:$B$6,0))
K5:K6	K5	=F5-J5
L5:L6	L5	=INDEX($D$5:$D$6,MATCH(G5,$B$5:$B$6,0))
M5:M6	M5	=I5-L5

 

[Audorin]

Very nice, thank you again for making sense of it all for me and using your spare time helping hopeless persons like my self.
I have the name displayed now, what if I also want the number, lets say 4500% extracted out of the sheet with the name that was the most profitable in terms of percentages?

 

[etaf]

just use the max() you used in the lookup
MAX(M5:M6)

only issue will be identical values, as index/match only returns 1
starts to get complicated to return multiple values,

 

[Audorin]

Thank you very much. That's it. I'm all done thanks to you. Very very grateful!

 

[etaf]

you are welcome