Macro to calculate values to hit a certain limit / target

[y3kesprit]

Hi all,

Per below example, how can I design a macro that when clicked, will calculate the values in Column D, such that the current percentage reaches 50%.

In below example, to achieve 50%, the sum in Column B should be 15/30, meaning I am lacking 6 hours (from the existing 9 hours). The macro should thus run, and calculate that these 6 hours should be allocated to the Person A. Note that the maximum hours allocated is subject to the maximum of the value in Column C. If the limit is reached (e.g. 8 for Person A), it should go on to the next row to determine how much additional to hit the current percentage of 50%

--------------------------

Current Percentage: 30% [ (sum of Row A + Row D) / sum of Row D]
[TABLE="width: 100%"]
<tbody>[TR]
[TD]
[/TD]
[TD](A)
[/TD]
[TD](B)
[/TD]
[TD](C)
[/TD]
[TD](D)
[/TD]
[/TR]
[TR]
[TD]Name
[/TD]
[TD]Actual Hours
[/TD]
[TD]Maximum Hours
[/TD]
[TD]Differential (B) – (A)
[/TD]
[TD]Calculation Logic
[/TD]
[/TR]
[TR]
[TD]Person A
[/TD]
[TD]2
[/TD]
[TD]10
[/TD]
[TD]8
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Person B
[/TD]
[TD]3
[/TD]
[TD]10
[/TD]
[TD]7
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Person C
[/TD]
[TD]4
[/TD]
[TD]10
[/TD]
[TD]6
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD]9
[/TD]
[TD]30
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]9/30 = 30%
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

Why a macro when a simple formula will suffice:

D2: =MEDIAN(B2,C2,B2+Target*C$5-SUM(B2:B$4)-SUM(D$1:D1))

Book1
	A	B	C	D
1	Name	Actual Hours	Maximum Hours	Target hours
2	Person A	2	10	8
3	Person B	3	10	3
4	Person C	4	10	4
5	Total	9	30
6
7	Target	50%
Sheet1

 

[y3kesprit]

Not sure why, but I'm getting a #name error when I copy your formula.

 

[y3kesprit]

Please ignore my previous message.

Column D should be showing the maximum hours (per column C), and move on to the next row if the limit is reached and the target % is not achieved.

In your example, its showing 8 hours instead of 10.

Not sure if I'm doing Anything wrong.

 

[StephenCrump]

Post #1 said that to meet your target, you needed to allocate +6 hrs to poor Person A.

Hours then would be:

A: 8 (i.e. 2+6 extra)
B: 3 (unchanged)
C: 4 (unchanged)

Total = 15
= your target 50% of the maximum 30.

Did you want something different?

 

[y3kesprit]

reserved

 

[y3kesprit]

Please ignore my previous message again. I meant to say:

No, you are right actually. Sorry, I wasn't looking at it properly.

I have expanded your formula to my actual template, but am encountering some difficulties.

I have inserted the below formula (assuming the columns are H-J, and rows data run from 9 to 27)

=MEDIAN(I9,H9,I9+$C$3*H$28-SUM(I9:I$27)-SUM(J$8:J8))

In this case, the hours are allocated correctly in total (1042.44) to reach the 73% target. However, the allocation pattern is weird (where I have highlighted in red boxes in the image) For e.g there is one row which is allocated 38.44 instead of 56 hours, or allocated 0 where it should be 72 hours.

Appreciate your support on this!

 

[StephenCrump]

... However, the allocation pattern is weird ...

The allocation pattern follows my understanding of your description in Post 1:

Target is 73% x 1,428 = 1,042.44 hours, so you need to allocate an additional 1,042.44 - 848 = 194.44 hours.

This is allocated successively down the list of people:
+76 in row 17
+80 in row 18
+38.44 in row 20.
= 194.44, therefore = end of allocation.

Perhaps you wanted something different? If so, how should the allocation have been made?