Hi all,
I am new to this forum and to Excel so am definitely a beginner.
As my example shows I currently have two sheets, one for spend and one for balances.
The first (in the format of the pictures) contains rows of a persons name, the date, and the spend on that date by that person. It is in order of date, everyday, from 15/06/2020 to 27/09/2020 and each person appears everyday they spend. So in the example, Elizabeth spends on both the 18/09/2020 and the 27/09/2020. The real sheet has a long list of names appearing most days with their stake for that day.
The second contains the balances of each person on each day (as shown in pictures). It has a row of each person along the top and a column of every day on the left hand side. On each day from 14/06/2020 to 27/09/2020 the balance of each person is shown.
I want to create a drop down list for each name. Within each drop down list I want to be able to calculate the following: the top 1%, 5% and 10% percentile spends, the average value of their spends above those percentile values, the number of days they spend over that percentile and the difference between the 'average above the percentile' and the 'percentile value'. I can easily do formulas to compute all these individually with the data for the persons spend and balances on each day all in the one sheet. However, I don't want to have to make a new sheet for each person and copy and paste each of their spends and balances into a new table within that sheet in order to calculate this data.
Does anyone have any suggestions for how I should go about this? Or is there a way to write a formula to take the data for each name and each date from the two seperate sheets (e.g. compare balance and spend on the same date/by the same name even though they are in different sheets) and use them in the new sheet to calculate the new data for each of these people in an expandable drop down list form. I appreciate any help you can offer, and it would be great if you could also explain any formula you use/suggest so that I know how to adapt it myself.
Thank you
I am new to this forum and to Excel so am definitely a beginner.
As my example shows I currently have two sheets, one for spend and one for balances.
The first (in the format of the pictures) contains rows of a persons name, the date, and the spend on that date by that person. It is in order of date, everyday, from 15/06/2020 to 27/09/2020 and each person appears everyday they spend. So in the example, Elizabeth spends on both the 18/09/2020 and the 27/09/2020. The real sheet has a long list of names appearing most days with their stake for that day.
The second contains the balances of each person on each day (as shown in pictures). It has a row of each person along the top and a column of every day on the left hand side. On each day from 14/06/2020 to 27/09/2020 the balance of each person is shown.
I want to create a drop down list for each name. Within each drop down list I want to be able to calculate the following: the top 1%, 5% and 10% percentile spends, the average value of their spends above those percentile values, the number of days they spend over that percentile and the difference between the 'average above the percentile' and the 'percentile value'. I can easily do formulas to compute all these individually with the data for the persons spend and balances on each day all in the one sheet. However, I don't want to have to make a new sheet for each person and copy and paste each of their spends and balances into a new table within that sheet in order to calculate this data.
Does anyone have any suggestions for how I should go about this? Or is there a way to write a formula to take the data for each name and each date from the two seperate sheets (e.g. compare balance and spend on the same date/by the same name even though they are in different sheets) and use them in the new sheet to calculate the new data for each of these people in an expandable drop down list form. I appreciate any help you can offer, and it would be great if you could also explain any formula you use/suggest so that I know how to adapt it myself.
Thank you
