Goal Seek, Daily goals to meet weekly targets

[foghornsedgy]

ok I've been trawling through google to find the solution to no avail.
id like to find a formula to tell me what i need over the next few days to achieve a target.

so..

Target Labour 20%

Day Monday Tuesday Wednesday
Actual Labour % 25%
Target needed% 20.0% 15.% 15.0%


basically i want the targets to change so i know what daily goals i need to meet weekly targets..
actual labour will be inputted at the end of the night when actual is known. so for tuesday my new target would be lower ....

i do hope this makes sense

 

[oldbrewer]

7 days at 20 is 140

day1 = 27 bal = 140-27 =113 spread over 6 days = 18.5 ish

when actual day 2 available say 17% bal = 140-27-17 = 96 over 5 days = 19.1 and so on

 

[foghornsedgy]

how would i write this as a formula? i don't see where your coming from.... sorry maybe just my ignorance

 

[oldbrewer]

				day1	day2	day3	day4	day5	day6	day7
day1	19		target	20	20	20	20	20	20	20
			actual	19
			remaining target		20.17
				formula giving 20.17
				=(140-SUM($E$8:$K$8))/(7-VALUE(RIGHT(F6,1))+1)
				day1	day2	day3	day4	day5	day6	day7
			target	20	20	20	20	20	20	20
day2	26		actual	19	26
			remaining target			19.00
				in the second table the day2 value goes in
				and I have made the formula automatic for
				future days (detects new actuals as they are added each day)
				new formula giving 19.00
				=IF(AND(G19="",F19<>""),IF(G19<>"","",(140-SUM($E19:$K19))/(7-VALUE(RIGHT(G17,1))+1)),"")
				this can be dragged to the right for the next 4 cells

<colgroup><col span="3"><col><col><col><col span="9"></colgroup><tbody>
</tbody>

 

[foghornsedgy]

on here you have target of 20% every day. on day1 you had a actual of 19% therefore day2's target would be 21% allowance, if for day 2 they get below that again day 3 allowance would be lower. day 4 they have an actual of 38% day 4 target would be lower... thats the figure I'm trying to get, its a labour management tool.. so staff can see what labour they get to achieve their weekly goal, which can be different..

also what cell does the formula go in..


thank you for your continued support.

 

[oldbrewer]

19 is in the row giving remaining targets and is the target for EACH REMAINING DAY. ( to meet the overall weekly target)

in my example it is in row 9

easy to display the new target for all of the remaining days - if that is what you want........