Add to one category and substract from the other based on % allocation.

[spiwere]

Sorry members: This is a duplicate post. It got posted twice somehow. Please refer to the post as this location:

http://www.excelforum.com/excel-prog...llocation.html



Good day everyone!

I need some help on a piece of analysis that has been taking a lot of time of my daily work. I’ve provided the explanation and expected output in the tab Named Report. My data is usage, and therefore I’m requesting some assistance with a Macro that can help me accomplish this. Here’s my request in a nutshell.

The process:

Calculate % for each team member to provide benefit for entries tagged as “Not Found” in column B and “Negative” in column C of the Tab named “DATA”
The % benefit is to be calculated from Column E of Tab 1, and added to the total Positive Count, and at the same time excluded from the total Negative Count
The logic is to also help me update the change in status for each entry in the Data tab.

Priority on converting Negatives to Positives under each category:

Priority 1: AD
Priority 2: WP
Priority 3: AT
Priority 4: VM

Examples:

1:If there are 4 Not Found + Negatives: The Negatives should be equally divided and subtracted from each Negative category, and the benefit is added to Positive (In this case: 1 Not Found Negative will be subtracted from LD, WP, AT and VM)
2:If there are 3 Not Found + Negatives: The Negatives should be equally divided and subtracted from each Negative category in order of Priority listed above, and the benefit is added to Positive (In this case: 1 Not Found Negative will be subtracted from LD, WP and AT)
3: If there are 5 Not Found + Negatives: The Negatives should be divided and subtracted from each Negative category, and the benefit is added to Positive in order of Priority (In this case: Not Found Negative will be subtracted as follows 2 LD, 1 WP, 1 AT and 1 VM)

The same logic is to be run on 60%, 70% as well. I was hoping for a dynamic dropdown or something wherein I can easily change the % allocation based on the requirement.

Please help me counter this. Thanks a lot!

Happy New Year!

Best,
Spi