financeintern
New Member
- Joined
- Apr 13, 2011
- Messages
- 1
My problem is pretty simple but the solution seems pretty complicated.
I have a list of clients and I have data about each one (gender, age, location, profit margin, fin. ratios, industry, favorite color, etcc.. ) I work for a lending institution so I also know how late they were in making their loan payments (ranges from 3 days to 300 days.)
I need to find out which group of clients is the riskiest.
I created a pivot table with all the data. I am starting simple so I am only using 3 catagories: AgeGroup, Gender, and Location plus daysLate. The catagories and sub catagories are all piled up on the upper rows. (It's a mess, but I think it's what I need in order to have every combination). The left column is Avg. days Late. Its hard to explain but I can see for example that Males between 25 and 30 from County A on average pay back their loans 14 days late on average. And depending on how I morph the pivot table I can focus on specific variables.
Problem is, is when I add all the variables, it's going to be a huge mess.
WHAT I NEED is to know what are the top 5 group of clients that are the riskiest and the top 5 that are the least riskiest. Thats to say... I need to look up the 5 highest (max) and lowest (min) values in the pivottable. And then I need excel to tell me to which group they pertain to. I havent been able to get Vlookup to work and I don't think getpivotdata is going to help me either. So I have been looking into macros.
To give you an idea of my level: If VBA macros is calculus, I know a little bit of algebra.
I hope my explaination made sense. If it doesn't please let me know and I'll go more into detail.
Any suggestion is appreciated.
I have a list of clients and I have data about each one (gender, age, location, profit margin, fin. ratios, industry, favorite color, etcc.. ) I work for a lending institution so I also know how late they were in making their loan payments (ranges from 3 days to 300 days.)
I need to find out which group of clients is the riskiest.
I created a pivot table with all the data. I am starting simple so I am only using 3 catagories: AgeGroup, Gender, and Location plus daysLate. The catagories and sub catagories are all piled up on the upper rows. (It's a mess, but I think it's what I need in order to have every combination). The left column is Avg. days Late. Its hard to explain but I can see for example that Males between 25 and 30 from County A on average pay back their loans 14 days late on average. And depending on how I morph the pivot table I can focus on specific variables.
Problem is, is when I add all the variables, it's going to be a huge mess.
WHAT I NEED is to know what are the top 5 group of clients that are the riskiest and the top 5 that are the least riskiest. Thats to say... I need to look up the 5 highest (max) and lowest (min) values in the pivottable. And then I need excel to tell me to which group they pertain to. I havent been able to get Vlookup to work and I don't think getpivotdata is going to help me either. So I have been looking into macros.
To give you an idea of my level: If VBA macros is calculus, I know a little bit of algebra.
I hope my explaination made sense. If it doesn't please let me know and I'll go more into detail.
Any suggestion is appreciated.
