Using Excel 2010 Solver and calculating two values

[Imvarda]

Hello all!

I have recently been posed with the attached problem. The attached spreadsheet is something my manager work like to use and has 'delegated' it to me to find out the solution (I don't know why and I don't know where he acquired it from!!). Unfortunately I am not an expert in Excel and this is really stressing me out (it is probably something simple). I was hoping to hone in my skills but this problem (attached) has me a little bit stumped. Can I please ask for any support/advice/ways in which it could be solved? This will really help me to learn and not come under fire from my boss!

Thanks for your time!

[Pepe Le Mokko]

Hi

I'm afraid you'll have to change your thread title as per forum rules before we can help

[punna111]

Copy of Spreadsheet.xlsx

Hi,

change the title as per our forum rules.

and here is the answer for first question.

Regards,
PRB.

[Imvarda]

Terribly sorry, I posted in haste. I will change it now...thanks very much Punna111

[punna111]

Hi,


did you understand the first question answer. or is there any disconnect.
mail me @ punna111@gmail.com. or tell me your mail id we can contact.

Regards,
PRB.

[shg]

I have recently been posed with the attached problem. The attached spreadsheet is something my manager work like to use and has 'delegated' it to me to find out the solution

That looks a lot more like homework than work-work.

[Alf]

That looks a lot more like homework than work-work.

It certainly does but perhaps his manager/boss goes to night school to improve his knowledge or could it be he is unable to help his children with their home work and order his employees to help him out? Interesting!

Alf

[Pepe Le Mokko]

Hi,


did you understand the first question answer. or is there any disconnect.
mail me @ punna111@gmail.com. or tell me your mail id we can contact.

Regards,
PRB.

Your post does not comply with Rule 4 of our Forum RULES. Don't Private Message, Visitor message or email questions to moderators or other members. All questions and answers will benefit other posters like yourself. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

[Imvarda]

Homework...ah...gone are those days I can assure you! Hence why I thought it odd and wondered where he got it from!


Punna: is this along the right lines...

You wrote in the workings 20=min employed each day, 1=excess for part b. But on the answer slide you wrote (a)=21?

should it be (a)=20 (b)=1?

(By the way I am a 'she' not a 'he')...thanks everybody for their input!

[Alf]

Solver solution for the education of a boss.

Alf

[punna111]

Hi alf,

I have seen your attached excel sheet.
but, I didn't understand what your explaining in the sheet.
can you explain.

Regards,
PRB.

[Alf]

No problem, I hope this makes it easier to understand the model.

The matrix in range F5:L11 is just to show when staff work and when they are off. This is the grid I set up manually to have five days of work and two days being off.

Looking at cell E5 there are 7 people who start the week by being off Monday and Tuesday. The rest of the week Wednesday to Sunday they work.

Value of cell 6 is 0 so you don’t need anybody who is off Tuesday and Wednesday and can work the rest of the week.

Value of cell E7 is 5 so you need 5 persons who works Monday and Tuesday are off Wednesday and Thursday and works Friday to Sunday.

As cell E8 are 5 you need 5 persons who are off Thursday and Friday but work the rest of the week.

You could call the values in D5 to D11 shift cycles so starting with 7 of them you only need 5 to meet the staff demand during a week.

Alf

[punna111]

still i am not clear.....sorry for that..

can you attach a sheet.

Regards,
PRB.

[Alf]

can you attach a sheet.

????

I've uploaded a workbook see post 10. Did you run solver on my uploaded file?

Alf

[punna111]

what is uploaded file.

PRB

[Alf]

Look at post 10. Find "Attached file" and click on Solver_staff_plan.xlsx and run Solver.

Alf

[punna111]

Hi alf,

I have seen your attached excel sheet.
but, I didn't understand what your explaining in the sheet.
can you explain.

Regards,
PRB.

That's what i am saying nothing is there on that excel sheet.
can you explain.

Regards,
PRB.

[Alf]

I've setup a solver model on the sheet. To see the result click tab "Data" then top - right find "Data Analysis" and below that "Solver"

Click "Solver" and when the Solver dialog box opens click "Solve" and after some seconds Solver will come up with a solution to the problem.

If you don't find Solver under the data tab you need to install it as Solver is not installed by default in Excel 2007

Alf

[punna111]

there is nothing to run..

PRB

[Alf]

Perhaps the 3 png files will make it easier to understand?

Alf

[punna111]

Fine, Now i understood.
Solver is not available from your uploaded file for me.

Here is the best solution for this.

please find the attached file.

Regards,
PRB