Programming Excel to do what?!

[jabberwocky]

i have some Excel programming to do that is a little trickier than what i'm used to.

Columns:
A= Name
B= Body Worker Group
C= Client Retention
D=Weekly Salary

i need Excel to return the value of 9 Names [col A] (out of a couple hundred or so) with the greatest collective Retention [col C] where the corresponding 9 Salaries [col D] collective sum is = to or < than 5000.

There is another limitation as well. Column B holds 5 different Group Types. i have to get Excel to perform the above 9 name selection [col A] while limiting selections from each Group Type to 2x GroupA, 2x GroupB, 2x GroupC, 2x GroupD, 1x GroupE.

Here's a link to a sample of the table i'm using.

This is definitely beyond my knowledge of Excel, but how hard is this to do?


i've accepted that i might have to reach outside of Excel to get this to work... either way, if you could point me in the right direction i'd be most greatful! What do i need to do to get this to work?

Thanks
☺

 

[Gerald Higgins]

Hi, welcome to the board.

Is this the same as simply saying
"give me the 2 highest from Group A, the 2 highest from Group B . . . . the 1 highest from Group E"
???

If it IS, that probably makes it much simpler.
Can you post back to confirm ?

 

[jabberwocky]

Hi GH,
Thanks for your reply!

Unfortunately it's not that quite simple.... the collective salary from all the 9 people cannot exceed $5000/wk. If i were to take the body workers with the top 2 retention rates from each group A through D and the top of group E... i would exceed $5000/wk.

i have to find the 9 people who match ALL of these criteria:
Select the 9 with the best collective retention rate
& with collective salaries not exceeding $5k
& limited to 2 body workers from group A,B,C & D and 1 from group E

Is this do-able in excel?

 

[Gerald Higgins]

OK I see.

So let's say, to begin with, you ignore the $5k issue.

Let's say you take the top 2 from A, top 2 from B, and so on.

Let's say then that the 9 you end up with break the collective $5k rule.
How do you decide which one (or more) of them to exclude from the initial 9, and replace with others ?
What if all 9 are on identical salaries ?

 

[jabberwocky]

Well great angle and great question... i'll see if i can talk myself into an answer because at first thought i don't know...

... but it might be logical to drop the person with the lowest salary/retention ratio.

example
Christina's (Chiro) ratio is $780/4.7 = 165.96
Lindy's (Chiro) ratio is $880/5.6 = 157.14
Svett's (Masso) ratio is $720/3.7 = 194.59
Martin's (Masso) ratio is $480/2 = 240.00

In this simple example maybe it would be intelligent to "drop" Martin who is the least "economical".

i'm only just bringing up the concept of using this ratio to our advantage if possible... because i think the way i've described using it is still not air tight logic.

Problems i see are
1. certain groups ie (Massos) generally have lower ratios.
2. some groups are smaller than others...

So i don't think this is a full answer to the question you were asking but perhaps it sparks your own brainstorm?

Cheers,

 

[Gerald Higgins]

Hi

I think at the end of the day, you're the one that has to be happy with the results, so you're the one who needs to decide what the best results are, even if you don't know exactly how to achieve that.

Other things that you could do...

- Drop whoever has the highest salary
- Drop whoever you need to drop, to get the best 3rd ranked person
- Simply select the 2 best people in each group who have salaries less than $5000/9

and there are others.

I don't know which of these is best for you.