Multi Value Solver update weekly - for KRA measures - What can I acheive

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
Hi

Can I please ask for a little help regarding solver as I cannot figure how to use it in this scenario.

I have these people and each week their results will update, for example I have filled in wk1 and wk2.
Code:
Person	wk1	wk2	wk3	wk4	wk5	wk6
Bill	48.4	62.3				
Mary	71.4	55.3

If I have these KRA levels
Code:
Target	Result
Base	60
Exceed	75
Outstanding	88

Is there a way I can have the values in the remaining weeks reflect a Base success and then if selected an Exceeding result and outstanding and so on.

My confusion with solve is that the changing cells wont be changing as new data comes in, how do I achieve this?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Possibly another aopproach is to use opportunities as an estimate for each week until known and then calculate needed get for estimated avgopp.

In my head the formula is something like given x get and y opps and a target 60 for each week I need to obtain the current result sum of x divided by sum of y to obtain current result and then vary the sum of the to get over the avgopp.

Code:
	wk1	wk2	wk3	wk4	wk5				60
Bill	0.545454545								75
									88
									
wk1		wk2		wk3		wk4		wk5	
get	opp	get	avgopp	get	avgopp	get	avgopp	get	avgopp
6	11	X	10	X	10	X	10	X	10
 
Upvote 0
I sort of have solved it dodgily with separate tables any advice would be appreciated.

Code:
		strike	OPP			strike	Opp					
	wk1	3	7			4.25	7.5					
	wk2	5	8		est	25.5	45	56.67%			Extra Needed	Extra Per week
	wk3	6	11						60%	27	2	1
	wk4	3	4		NextWeek	5	66.7%		75%	34	8	5
	wk5								88%	40	14	8
	wk6											
		17	30						count of weeks		6	
									less weeks done		4	
									remaining		2

Showing Forumlas
Code:
		strike	OPP			strike	Opp					
	wk1	3	7			=AVERAGE(D17:D22)	=AVERAGE(E17:E22)					
	wk2	5	8		est	=6*H17	=6*I17	=H18/I18			Extra Needed	Extra Per week
	wk3	6	11						0.6	=$I$18*K19	=$L19-$H$18	=ROUNDUP(M19/$M$25,0)
	wk4	3	4		NextWeek	=ROUND(H17+N19,0)	=H20/I17		0.75	=$I$18*K20	=$L20-$H$18	=ROUNDUP(M20/$M$25,0)
	wk5								0.88	=$I$18*K21	=$L21-$H$18	=ROUNDUP(M21/$M$25,0)
	wk6											
		=SUM(D17:D22)	=SUM(E17:E22)						count of weeks		6	
									less weeks done		=COUNT(D17:D22)	
									remaining		=M23-M24
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top