How to distribute values??? Tough one!

  • Thread starter Thread starter tenb
  • Start date Start date
T

tenb

I am building a worksheet containing data on individuals identified by a
6 character code. However some codes (30 out of 230) represent more
than one individual. The individuals represented by the "group" code
have their own individual numbers that are included in the list.

So, for those "group" codes, I need to distribute point values to those
individuals who make up the group.

Thus, ABC123 is comprised of AAA111 (30%), AAA222 (30%), AAA### (30%),
and AAA444 (10%). If the value for ABC123 is 100, how do I distribute
the pro rata portion of that 100 to the indivuals using another
spreadsheet laying out the percentage splits (adding it to the
individuals existing value).

So, I need to recalculate sheet 2 using the breakdowns in sheet 1. Any
advice will be greatly appreciated.
Thanks.
Jerald

Attachment filename: sample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=380390
 
I think this will do what you want:
=GroupScore*Individual%

If the group got a score of 100, and one individual got 47%, the value
returned above would be the answer.

To find the individual %, divide the individual number by the group number:
=IndividualNumber/GroupNumber
 
Thanks for the tip. If you take a look at the sheets, however, I don't
think that approach would eliminate an onerous manual effort.

I want to come up with a formula that would use vlookup to match the
group and individual codes on the master sheet and redistribute points
on the points sheet. I've tried to get my brain around it, but I can't
come up with a way to do it that would take points from the group
number and redistribute automatically to the individual based on the
criteria on the first sheet.

I'm probably not being very clear. I appreciate your help.
Regards,
tenb
 
If you'd like, send the file, removing the obvious garbage from th
address. I suppose I do not have a clear concept of the difficult
involved. I will help if I can, using worksheet formulas and get th
file back as soon as possible.

All the best,
Ji
 
Jim,
The relevant pages of the workbook are posted to the original thread.
Thanks again.
ten
 
This would be possible, I believe, if more info were available in your data
(or the sample you provided.) For instance, we have no way to know which
groups particpated in the Number (Job?). Further, we have no way to discern
the level of participation for the individuals...what their numbers were.

If you do not currently gather that data, I do not see how you could expect
to break it down. If you DO gather the data, it should be shared with anyone
trying to help.
 
Back
Top