Take two criteria and compare across two sheets

[RoseR12]

I have a sheet of balances and a sheet of spends. The sheet of balances has a column for each name and a row for each date. The sheet of spends is a list in order of date of each spend with each row indicating a name, date and spend. The list includes a name multiple times (one for every date they spent on). If I want to do calculations based on the data for both balance and spend for each name how would I get excel to understand that when I refer to a name and date in one sheet I also want to refer to that same name and date in the other sheet to take both spend and balance for the name and date. Or is there a quick way to make a new list with both spend and balance for each name and date?

 

[alansidman]

Please provide us with some sample data and a mocked up solution. Use the XL2BB function on this site to upload that sample data.

 

[RoseR12]

Name	Date	Spend
Charles	15/06/2020	10000
Andrew	15/06/2020	1000
Elizabeth	18/06/2020	5000
Elizabeth	27/09/2020	20000

 

[RoseR12]

and a second sheet with:

Example.xlsx
	A	B	C	D
1	Date	Charles	Andrew	Elizabeth
2	14/06/2020	100000	30000	200000
3	15/06/2020	120000	20000	150000
4	27/09/2020	90000	40000	100000
Balance

 

[alansidman]

What do you want the results to look like for the information provided. Mock it up so that we understand exactly what you are looking for as asked earlier.

 

[RoseR12]

Sorry didn't see the mockup of solution bit.

Well I've made a drop down list like in the mockup i've attached. I also would like to know how to make the drop down list only show unique names from the list of names as each name appears multiple times. I want to be able to select a name and then for it to show me the calculations based on spends shown in J3:N6 in my mockup but just for the name selected in drop down list. In the mockup I have only made it able to show me the data in the table from A1:C5 for that name using the filter function.

I also want it to show the average spend/balance ratio for that name something like how I've formatted in J8 where the average spend/balance ratio is the average of the spend divided balance for each day. I'd then also like to calculate an overall average spend/balance ratio outside of the drop down list which isnt for every person individually but for all the data. I'm sorry I don't know how to use the balance data to show this properly in my mockup. Ideally this would all be on its own seperate worksheet without having all the data in that sheet.

If you can help at all that would great as I'm sure you can tell I am a beginner.

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Date	Spend
2	Charles	15/06/2020	10000		Name:
3	Andrew	15/06/2020	1000		Elizabeth	Elizabeth	18/06/2020	5000		Percentile	Value	Average Above Value	No. Of Days Above Value	Spend Difference
4	Elizabeth	18/06/2020	5000			Elizabeth	27/09/2020	20000		1%	19700	20000	1	300
5	Elizabeth	27/09/2020	20000							5%	18500	20000	1	1500
6										10%	17000	20000	1	3000
7
8										Spend/Balance Ratio:
Spend

Cell Formulas
Range	Formula
F3:H4	F3	=FILTER(A2:C5, A2:A5=E3)
K4	K4	=PERCENTILE.INC($C$2:$C$5, 0.99)
L4:L6	L4	=AVERAGEIF($C$2:$C$5, ">" & K4)
M4:M6	M4	=COUNTIF($C$2:$C$5, ">" & K4)
N4:N6	N4	=L4-K4
K5	K5	=PERCENTILE.INC($C$2:$C$5, 0.95)
K6	K6	=PERCENTILE.INC($C$2:$C$5, 0.9)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2:A5	List	=$A$2:$A$5

 

[RoseR12]

Drop down list is in E3 btw