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 Å

 

[etaf]

so if you use the SOLD sheet only to get the average - then that solves issue

The only transactions I'm out after are the ones that have been bought and sold, not just bought.

Next you could add a column to the sold data with Days
Then you could lookup the date from the Bought Sheet
Index(Sheet-Bought!date-column, match ( cattle name cell , Sheet-Bought!name-column,0))
that returns the date
now subtract that from the sold date

Sold date for name - Index(Sheet-Bought!date-column, match ( cattle name cell , Sheet-Bought!name-column,0))
Then you have the days

heres is a simple mock up in 1 sheet so you can see the idea

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	Bought Sheet					Sold Sheet
2	date	Cattle Name	column 1	price		date	Cattle Name	column 1	price	Date Bought	Days Owned
3	1/1/21	fred	Bought			10/1/21	fred	sold		1/1/21	273
4	10/1/21	Harry	Bought			11/1/21	Harry	sold		10/1/21	31
5
Sheet3

Cell Formulas
Range	Formula
J3:J4	J3	=INDEX($A$3:$A$4,MATCH(G3,$B$3:$B$4,0))
K3:K4	K3	=F3-J3

 

[Audorin]

Thank you very much etaf. It's a bit late, so I just skimmed over it, but this looks like the sollution on the first glance. Appreciate the fact that you probaly saved my sleep for the entire week right here and now. Much love!

 

[etaf]

you are welcome
if you post a sample sheet with actual data , ranges etc , using the XL2BB Add-in see menu or my signature
maybe able to write something specifically using your data
Or put a sample onto a share like onedrive/dropbox/google drive

otherwise post any more clarification questions here
I'm out a lot of tomorrow, so others may answer, or it will be late monday/tuesday

 

[Audorin]

Hoping this works. Something fishy going on with the days. Feel free to take a look at it.Need to hit the sack, will look more on it in a few hours!

LLL.xlsm
	A		C	D	E				I	J	K	L	M	N	O	P	Q	R		T	U	V			Y	Z	AA
1	STATS		SOLD															STATS		BOUGHT
2	TOTAL SOLD		$ 12,642,095															TOTAL SOLD		$ 8,281,869
3	AVG SOLD		$ 268,981															AVG SOLD		$ 116,646
4	TOTAL TRANSACTIONS		47															TOTAL TRANSACTIONS		71
5	Date		Players	Action	To/From				Price	TSI	Days	Days owned		2021	Revenue	Investments		Date		Players	Action	To/From			®	Price	TSI
6	5/10/21		Xander Abbenhuis	Sold	Grolsche Boys				1800000	1,760	28/11/21	-54		september 2021	$ -	$ 202,000		29/9/21		Xander Abbenhuis	Bought	Zanarkand Duggles			(R)	1020000	1,710
7	10/10/21		Willy Courtman	Sold	schleiferteam				1610000	750	28/11/21	-49		oktober 2021	$ 2,469,500	$ 1,879,811		29/9/21		Rafał Jaśkiewicz	Bought	GKS Najmansi			(R)	500000	1,290
8	10/10/21		Rafał Jaśkiewicz	Sold	schleiferteam				1050000	1,090	28/11/21	-49		november 2021	$ 10,172,595	$ 6,200,058		29/9/21		Andreas Weißburg	Bought	TC Shanghai			(R)	140000	1,390
9	10/10/21		Andreas Weißburg	Sold	Escudos Portadores Del Sur				798000	1,230	11/10/21	-1		desember 2021	$ -	$ -		29/9/21		Willy Courtman	Bought	The Rampaging Raccoons			(R)	140000	780
Sold Bought

Cell Formulas
Range	Formula
C2,T2	C2	=SUM(I6:I9999)/10
C3,T3	C3	=AVERAGE(I6:I9999)/10
C4	C4	=COUNTA(I6:I9999)
T4	T4	=COUNTA(X6:X9999)
I6:J9,Z6:AA9	I6	=VALUE(TEXTJOIN("",TRUE,IFERROR(MID(F6,SEQUENCE(20),1)+0,"")))
K6	K6	=INDEX($R$6:$R$76,MATCH(C6,T6:T76))
L6:L9	L6	=[@Date]-[@Days]
K7:K9	K7	=INDEX(R7:R77,MATCH(C7,T7:T77))
O6,O9	O6	=SUMIFS(Table5[Price],Table5[Date],">="&N6,Table5[Date],"<="&EOMONTH(N6,0))
P6:P9	P6	=SUMIFS(Table4[Price],Table4[Date],">="&N6,Table4[Date],"<="&EOMONTH(N6,0))/10
O7:O8	O7	=SUMIFS(Table5[Price],Table5[Date],">="&N7,Table5[Date],"<="&EOMONTH(N7,0))/10

 

[Audorin]

Also just to add, there are a few hidden collums there. It's data I don't need to see, but collums that needs to be there since the database I copy it from comes in a certain format.

 

[etaf]

xl2bb does not show the data in hidden columns - so i just get value
also need to understand a little more about the sheet layout

date in column r = bought date
date in column a = sold date

so somewhere associated with sold
=INDEX($r$6:$r$9,MATCH(c6,$t$6:$t$9,0))
returns the bought date

the a6- INDEX($r$6:$r$9,MATCH(c6,$t$6:$t$9,0))
is days old

5/10/21 - 29/9/21
= 6days

 

[etaf]

which i see you do have in K6 , but NO $ for the T range and so it will change as you copy down - although a larger range
AND 0 for an exact match have to lookup what the default is - probably 0

=INDEX($R$6:$R$76,MATCH(C6,$T$6:$T$76,0))

then
A6 - INDEX($R$6:$R$76,MATCH(C6,$T$6:$T$76,0))

As you can see in M6 - i get the 6 days as expected

Book4
	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	Z	AA
1	STATS		SOLD															STATS		BOUGHT
2	TOTAL SOLD		#VALUE!															TOTAL SOLD		#VALUE!
3	AVG SOLD		#VALUE!															AVG SOLD		#VALUE!
4	TOTAL TRANSACTIONS		4															TOTAL TRANSACTIONS		0
5	Date		Players	Action	To/From				Price	TSI	Days	Days owned		2021	Revenue	Investments		Date		Players	Action	To/From			®	Price	TSI
6	44474		Xander Abbenhuis	Sold	Grolsche Boys				#VALUE!	#VALUE!	44468	=[@Date]-[@Days]	6	44440	=SUMIFS(Table5[Price],Table5[Date],">="&N6,Table5[Date],"<="&EOMONTH(N6,0))	=SUMIFS(Table4[Price],Table4[Date],">="&N6,Table4[Date],"<="&EOMONTH(N6,0))/10		44468		Xander Abbenhuis	Bought	Zanarkand Duggles			(R)	#VALUE!	#VALUE!
7	44479		Willy Courtman	Sold	schleiferteam				#VALUE!	#VALUE!	44468	=[@Date]-[@Days]		44470	=SUMIFS(Table5[Price],Table5[Date],">="&N7,Table5[Date],"<="&EOMONTH(N7,0))/10	=SUMIFS(Table4[Price],Table4[Date],">="&N7,Table4[Date],"<="&EOMONTH(N7,0))/10		44468		Rafał Jaśkiewicz	Bought	GKS Najmansi			(R)	#VALUE!	#VALUE!
8	44479		Rafał Jaśkiewicz	Sold	schleiferteam				#VALUE!	#VALUE!	44468	=[@Date]-[@Days]		44501	=SUMIFS(Table5[Price],Table5[Date],">="&N8,Table5[Date],"<="&EOMONTH(N8,0))/10	=SUMIFS(Table4[Price],Table4[Date],">="&N8,Table4[Date],"<="&EOMONTH(N8,0))/10		44468		Andreas Weißburg	Bought	TC Shanghai			(R)	#VALUE!	#VALUE!
9	44479		Andreas Weißburg	Sold	Escudos Portadores Del Sur				#VALUE!	#VALUE!	#N/A	=[@Date]-[@Days]		44531	=SUMIFS(Table5[Price],Table5[Date],">="&N9,Table5[Date],"<="&EOMONTH(N9,0))	=SUMIFS(Table4[Price],Table4[Date],">="&N9,Table4[Date],"<="&EOMONTH(N9,0))/10		44468		Willy Courtman	Bought	The Rampaging Raccoons			(R)	#VALUE!	#VALUE!
Sheet1

Cell Formulas
Range	Formula
C2,T2	C2	=SUM(I6:I9999)/10
C3,T3	C3	=AVERAGE(I6:I9999)/10
C4	C4	=COUNTA(I6:I9999)
T4	T4	=COUNTA(X6:X9999)
I6:J9,Z6:AA9	I6	=VALUE(TEXTJOIN("",TRUE,IFERROR(MID(F6,SEQUENCE(20),1)+0,"")))
K6	K6	=INDEX($R$6:$R$76,MATCH(C6,T6:T76))
K7:K9	K7	=INDEX(R7:R77,MATCH(C7,T7:T77))
M6	M6	=A6 - INDEX($R$6:$R$76,MATCH(C6,$T$6:$T$76,0))

 

[Audorin]

xl2bb does not show the data in hidden columns - so i just get value
also need to understand a little more about the sheet layout

date in column r = bought date
date in column a = sold date

so somewhere associated with sold
=INDEX($r$6:$r$9,MATCH(c6,$t$6:$t$9,0))
returns the bought date

the a6- INDEX($r$6:$r$9,MATCH(c6,$t$6:$t$9,0))
is days old

5/10/21 - 29/9/21
= 6days

The hidden columns have data that should be displayed as numbers, but are registered as text. So the columns you can see have codes to extract the text from the hidden columns and make them numbers. "Price" And TSI (Total Skill Index), it's really just some data that doesn't matter much at all. I could as well hide that.

Just tried that code of yours, and it worked like a charm!
I think I should be able to start making graphs and extract the data to a dashboard then. The reason I'm doing this is mostly to just improve in excel, and the excel sheet is based on a virutal football club.

Appreciate this a lot. Very pleased. I've really tried a lot of sollutions, but just couldn't make it happen.
Many many obstacles, but instead of having a "bought" sheet, and a "sold" sheet just gathered all the data in the same sheet. First then things started to work out. When I did the (sheet!xx, it just didn't really add up. Some of it worked, some did not.

So I'm happy most of the data is getting complete now and I can start creating the dashboard for a more visual view of the data!

Thank you again!

Good night.

 

[etaf]

you are welcome

When I did the (sheet!xx, it just didn't really add up. Some of it worked, some did not.

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