Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Multiplication problem- Please Help!
Hello all. I am a novice Excel user, and cannot seem to locate the source of my problem. Please help! Here is my sheet: C4 D4 E4 F4 G4 H4 9/22/2005 7:10 PM___10/1/2005 12:13 PM___ 8.7__52.6__6.0__317.64 Start date is entered into C4. End date is entered into D4. The following formula is in E4 to determine elapsed days: =IF(D4=0,"",IF(C4=0,"",(IF(ISERROR((D4-C4)*24),"",(D4-C4)*24)/24))) F4 contains "FLAGED HOURS" (for a body shop estimate) G4 contains a value used for "cycle time" and is using the following formula: =IF(F4=0,"",F4/E4) H4 contains a simple multiplication formula: =(F4*G4), but returns 317.64 INSTEAD of 315.6 (which is 52.6 x 6.0). I need the 315.6 result in H4. Would someone please tell me what I am doing wrong???? Thanks very much! -- millzenator ------------------------------------------------------------------------ millzenator's Profile: http://www.excelforum.com/member.php...o&userid=28042 View this thread: http://www.excelforum.com/showthread...hreadid=475490 |
#2
|
|||
|
|||
The cells contain trailing digits which you are not seeing.
You could try =(ROUND(F4,2)*(ROUND(G4,1))) -- HTH Bob Phillips "millzenator" wrote in message ... Hello all. I am a novice Excel user, and cannot seem to locate the source of my problem. Please help! Here is my sheet: C4 D4 E4 F4 G4 H4 9/22/2005 7:10 PM___10/1/2005 12:13 PM___ 8.7__52.6__6.0__317.64 Start date is entered into C4. End date is entered into D4. The following formula is in E4 to determine elapsed days: =IF(D4=0,"",IF(C4=0,"",(IF(ISERROR((D4-C4)*24),"",(D4-C4)*24)/24))) F4 contains "FLAGED HOURS" (for a body shop estimate) G4 contains a value used for "cycle time" and is using the following formula: =IF(F4=0,"",F4/E4) H4 contains a simple multiplication formula: =(F4*G4), but returns 317.64 INSTEAD of 315.6 (which is 52.6 x 6.0). I need the 315.6 result in H4. Would someone please tell me what I am doing wrong???? Thanks very much! -- millzenator ------------------------------------------------------------------------ millzenator's Profile: http://www.excelforum.com/member.php...o&userid=28042 View this thread: http://www.excelforum.com/showthread...hreadid=475490 |
#3
|
|||
|
|||
This appears to be a rounding problem....Try changing the foemula in H:4 to =F4*ROUND(G4,0) -- LGW ------------------------------------------------------------------------ LGW's Profile: http://www.excelforum.com/member.php...o&userid=28046 View this thread: http://www.excelforum.com/showthread...hreadid=475490 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplication problem- Please Help!
Well, I need more help on these sheets. I've made two attachments (I wish I could figure out how to imbed an image directly into the message here...) On sheet 1, this is the code to figure the required date for a cycle time. This is from column F- =IF(C3=0,"",C3+(D3/E3)) This is the code from column G, that tells me how many days between C and F- =IF(F3=0,"",IF(C3=0,"",(IF(ISERROR((F3-C3)*24),"",(F3-C3)*24)/24))) I need the formula to exclude Saturdays and Sundays. Is this possible? If possible, it would be great to have it display " x days x hours" instead of "5.8" days. On sheet 3, I am using the same code- =IF(D3=0,"",IF(C3=0,"",(IF(ISERROR((D3-C3)*24),"",(D3-C3)*24)/24))) ...to tell me how many elapsed days between C and D, and still need the same fix, as above. Please help! +-------------------------------------------------------------------+ |Filename: sheet3.jpg | |Download: http://www.excelforum.com/attachment.php?postid=4700 | +-------------------------------------------------------------------+ -- millzenator ------------------------------------------------------------------------ millzenator's Profile: http://www.excelforum.com/member.php...o&userid=28042 View this thread: http://www.excelforum.com/showthread...hreadid=475490 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplication problem- Please Help!
Try using Excel formula: =networkdays(start date,end date). You can even
exclude holidays using this formula. First, though, you have to go to Tools, Add-Ins... and select the Analysis ToolPak. Takes about two seconds to do. The Analysis ToolPak give you a choice of lots more formulas. "millzenator" wrote: Well, I need more help on these sheets. I've made two attachments (I wish I could figure out how to imbed an image directly into the message here...) On sheet 1, this is the code to figure the required date for a cycle time. This is from column F- =IF(C3=0,"",C3+(D3/E3)) This is the code from column G, that tells me how many days between C and F- =IF(F3=0,"",IF(C3=0,"",(IF(ISERROR((F3-C3)*24),"",(F3-C3)*24)/24))) I need the formula to exclude Saturdays and Sundays. Is this possible? If possible, it would be great to have it display " x days x hours" instead of "5.8" days. On sheet 3, I am using the same code- =IF(D3=0,"",IF(C3=0,"",(IF(ISERROR((D3-C3)*24),"",(D3-C3)*24)/24))) ...to tell me how many elapsed days between C and D, and still need the same fix, as above. Please help! +-------------------------------------------------------------------+ |Filename: sheet3.jpg | |Download: http://www.excelforum.com/attachment.php?postid=4700 | +-------------------------------------------------------------------+ -- millzenator ------------------------------------------------------------------------ millzenator's Profile: http://www.excelforum.com/member.php...o&userid=28042 View this thread: http://www.excelforum.com/showthread...hreadid=475490 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplication problem- Please Help!
I've tried incorporating the NETWORKDAYS function into the current formulas, but can't get it to work. Playing with NETWORKDAYS on a blank sheet, it rounds to the day. I need results to the hour. Any further help, or examples using my formulas? Thanks! Terry -- millzenator ------------------------------------------------------------------------ millzenator's Profile: http://www.excelforum.com/member.php...o&userid=28042 View this thread: http://www.excelforum.com/showthread...hreadid=475490 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplication problem- Please Help!
Can I confirm what you want to do? Do you want to calculate the number of days and hours between two date/times (excluding weekends) or do you want to generate a date/time based on a number of days (possibly fractional) added to a start date/time? (or both :) ) If the latter then how many hours constitute a day? If you add 1.2 days to 18:00 on a Monday what should the result be? Do you want the time returned to be any time of the day or should it be within specific hours, e.g. your working hours, if so what are these? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=475490 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplication problem- Please Help!
Looking at your sheet1 looks like you want to add the number of days in G3 (5.8) to the date in C3, excluding weekends, If any time on a working day is acceptable perhaps =WORKDAY(C3,G3+MOD(C3,1))+MOD(C3+G3,1) although, depending on your answers to my questions above, you might need a more complex formula..... -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=475490 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplication problem- Please Help!
Thanks for the help so far guys... I am by no means an expert at Excel, and am only trying to help a friend who owns a body shop. Believe me, he is not guru at computers, but can bring a car back to life Chris Foose. Basically, Progressive Insurance requires that their jobs be completed within a certain "cycle time", based on when the car arrives at the shop and when the repairs are completed. A cycle time of 4.0 or 4.5 is acceptable, but anything below that, and Progressive starts to limit the number of vehicles that are brought to my friends shop for repairs. His business is new, and I am doing all that I can to help him get started. I created one sheet where you input a start date and time and an end date and time. This sheet calculates the cycle time based on "flag hours (the number of hours Progressive estimates that the repairs will take)". I created another sheet where you input a start date and time, desired cycle time, and it will tell you when the vehicle is due to maintain the cycle time that you input in the cell. What I need is to exclude weekend days, and the work hours for the week can include all 24 hours of each day. I only need to exclude weekend days if they fall into the date ranges that are manually added. I did find this VB code on the net, but I have no idea how to use it: '************************************** 'Windows API/Global Declarations for :Ne ' tWorkDays '************************************** - '************************************** ' Name: NetWorkDays ' Description:The code is capable of cal ' culating the NETWORK DAYS LIKE it does i ' n excel. The Weekends gets removed and the days betwwen the 2 dates is the output in days ' By: Brijesh ' ' ' Inputs:this is the function call checkAvailableDays = dayscalculation(CalculationStartDate, WPAEmpEndDate) ' ' Returns:Returns days excluding weekday ' s. Holidays are included....working on a code to remove that ' 'Assumes:- ' 'Side Effects:I dont see any except for ' the error handling which can be customis ' ed 'This code is copyrighted and has limite ' d warranties. 'Please see http://www.Planet-Source-Cod ' e.com/xq/ASP/txtCodeId.64390/lngWId.1/qx ' /vb/scripts/ShowCode.htm 'for details. '************************************** 'call to the function dim checkAvailableDays as integer checkAvailableDays = dayscalculation(CalculationStartDate, WPAEmpEndDate) '---Amazing this code actially works Private Function dayscalculation(CalculationStartDate As Date, WPAEmpEndDate As Date) Dim workingdays As Integer StDateValue = CDate(CalculationStartDate) EndDateValue = CDate(WPAEmpEndDate) If (StDateValue EndDateValue) Then MsgBox "Sorry Invalid Date Value", vbCritical, "VOID" workingdays = 0 Else If (StDateValue = EndDateValue) Then MsgBox "Sorry Dates are Same", vbCritical, "VOID" workingdays = 0 Else workingdays = DateDiffW(StDateValue, EndDateValue) End If End If dayscalculation = workingdays End Function Private Function DateDiffW(StDateValue, EndDateValue) For i = StDateValue To EndDateValue If (Weekday(i) < 1) And (Weekday(i) < 7) Then WorkingDaysValue = WorkingDaysValue + 1 End If Next DateDiffW = WorkingDaysValue End Function If you guys are will to help, I can email my small Excel files to you for your expert diagnosis :) -- millzenator ------------------------------------------------------------------------ millzenator's Profile: http://www.excelforum.com/member.php...o&userid=28042 View this thread: http://www.excelforum.com/showthread...hreadid=475490 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Display Problem | Excel Discussion (Misc queries) | |||
EXCEL 2003 PROBLEM | Excel Worksheet Functions | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) | |||
Paper Tray selection Problem, | Excel Discussion (Misc queries) |