Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using the solver in excel 2000
I use excel to record my timesheets for work. I write in the time i arrive, the time i leave and the time spent on my lunch break. I use basic formulas to calculate the hours for each day and the total for the week, which is a maximum of 36 hours. DAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY START 8:30 8:30 8:15 8:00 8:00 FINISH 17:00 17:00 16:00 16:30 14:30 LUNCH 0:45 0:45 0:45 0:45 0:45 TOTAL 7:45 7:45 7:00 7:45 5:45 GRAND TOTAL 7:45:00 15:30:00 22:30:00 30:15:00 36:00:00 I want to use the solver to allow me to see what time i could finish later in the week by solving the timesheet to reach my target hours of 36. When i try to use a solver it says it cannot get an answer. Any help would be much appreciated Jody Williams |
#2
|
|||
|
|||
No need to use solver, try the following. Assuming your data you posted is
in range A1:F5 Enter the following in B8: 36:00:00 C8: =$B$8-B5 D8: =$B$8-C5 E8: =$B$8-D5 F8: =$B$8-E5 Modify your total row (row 4) to =IF(AND(B1<"",B2<""),B2-B1-B3,0) and copy across Row 7 will have expected finish times. Enter in B7 =IF(B2="",IF(B1TIME(4,0,0),B1+B8/(5-COUNT($B$2:$F$2))+B3,B1+B8/(5-COUNT($B$ 2:$F$2))),"") and copy across for all days. Mangesh "traineeross" wrote in message ... I use excel to record my timesheets for work. I write in the time i arrive, the time i leave and the time spent on my lunch break. I use basic formulas to calculate the hours for each day and the total for the week, which is a maximum of 36 hours. DAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY START 8:30 8:30 8:15 8:00 8:00 FINISH 17:00 17:00 16:00 16:30 14:30 LUNCH 0:45 0:45 0:45 0:45 0:45 TOTAL 7:45 7:45 7:00 7:45 5:45 GRAND TOTAL 7:45:00 15:30:00 22:30:00 30:15:00 36:00:00 I want to use the solver to allow me to see what time i could finish later in the week by solving the timesheet to reach my target hours of 36. When i try to use a solver it says it cannot get an answer. Any help would be much appreciated Jody Williams |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Setting tab colours on Excel spreadsheets (for Excel 2000) | Excel Discussion (Misc queries) | |||
Excel 2000 to Excel 97 | Excel Discussion (Misc queries) | |||
can you tell me the difference between excel 2000 and excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2000 file when opened in Excel 2003 generates errors? | Excel Discussion (Misc queries) |