Question about Microsoft Office Professional 2007 Full Version for PC

1 Answer

Working hours calculation (with Neg time) in Excel

Hi
I have a database of a person's working hours where I have his three months
extra or less working hours. I want an average of all the three months.

Can someone help me with the formula for this average. Here the average
formula is not giving the correct value.

Month 1 Month 2 Month 3 Average
-08:31:00 -24:50:00 21:01:00 ?

Posted by on

1 Answer

  • Level 2:

    An expert who has achieved level 2 by getting 100 points

    MVP:

    An expert that got 5 achievements.

    Vice President:

    An expert whose answer got voted for 100 times.

    Governor:

    An expert whose answer got voted for 20 times.

  • Expert
  • 139 Answers

Hi Aviks,
Normal average works well for linear distribution, but here is non-linear distribution of work. So here is the formula that has been taken from project management concepts. Please try for different values of x and y

Try to keep x+y=6

=(StandardWorkingHr*x+(ActualWorkingHr-StandardWorkingHr)*y)/(x+y)

e.g.
=(StandardWorkingHr*5+(ActualWorkingHr-StandardWorkingHr)*1)/5)
if you can share data with me, I can give you the exact formula.

Posted on Mar 09, 2009

×

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

Wages bill reg


Format both cells with the time format "h:mm AM/PM".
Lets say the start time is in cell A2 and the stop time is in cell B2. In cell C2 put the formula =B2-A2 and custom format the cell C2 as: "h:mm"

Apr 16, 2009 | Microsoft Excel for PC

1 Answer

I want my clients' age to automatically calculate using Date of Birth. In previous versions of Works Database it was a simple formula, but I can't seem to replicate it in Office Excel 2007.


Its the same only the interface is different. Use the same technique of the Mathematical algorithm keeping in mind the way the date, time are displayed in their format. Also switch to 24 Hr mode....sodeep

Mar 20, 2009 | Microsoft Office Excel 2007

1 Answer

How to calculate the difference of two dates by year, month and day using the formula in excel?


To calculate the difference in Years use =Year(c,r)-Year(c,r)
To calculate the difference in Months use =Month(c,r)-Month(c,r)
To calculate the difference in Months use =(c,r)-(c,r)

c= column, r= row

You will need to format the source cells as dates

Mar 15, 2009 | Microsoft Office Excel 2003 for PC

1 Answer

Converting date of a month to last working day of the month


use the below formula to get the date of the LWD (Monday thru Friday) of the month.
Assumed that if the date in cell A1 is 6-May-2005. then formula will be
=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5))

Jan 30, 2009 | Microsoft Excel for PC

1 Answer

WHAT IS THE DIFFERENCE BETWEEN MICROSOFT EXCEL FROM DATA BASE


Excel is a spreadsheet - it is less structured than a database and all of the data appears on one (or more) pages along with many calculations and summaries of data.
A database is structured so that all the information is kept in the same format for each member record of the database - Databases are better at processing larger volumes of information.
Some tasks can be performed equally well in either spreadsheet or database -
Often data is stored in a database but analysis is done in the spreadsheet.

Nov 20, 2008 | Microsoft Excel for PC

2 Answers

Excel formula


Hello,
The formula should go like this:
=IF(C11>40,(C11-40)*D11,0)
This one will give you the result of (ONLY overtime hours)*(hourly wage), and if there are no overtime hours the result is 0.
Now, if you need to multiply that result with say 1,5 or whatever - insert the number you need like this:
=IF(C11>40,(C11-40)*D11*1.5,0)

If you need more help, please ask.

Sep 14, 2008 | Business & Productivity Software

1 Answer

Average handle time


I have created a spreadsheet for you to a) use and b) to learn from.

It is an Automated spreadsheet (as they should be) which calculates the number of minutes in a working week or month and calculates the average time per email giving Daily, Weekly and Monthly Outputs. It takes into account Public Holidays (or for time off). You can use the Output to create Graphs etc to visually display the Output.

It also allows you to calculate a Part Month average.

I have displayed it as it was CONSTRUCTED and as it would be USED.

The As Used worksheet is Protected and the only Inputs that can be done are in the Green Boxes (also the Saturday and Sunday boxes but you will need to Unhide the Validation List to include these and then to add 2 more columns titled Is Saturday? and Is Sunday? with the appropriate If Statement.

To unprotect the sheet go to Tools - Protection - Unprotect. There is no password so leave this blank.

All the workings are still there, the columns are just Hidden. To Unhide them, highlight the columns to the left and right of the hidden columns, click on Format - Columns - Unhide. To hide them again, highlight the columns that you want hidden, click on Format - Columns - Hide.

The LOGIC used (as in Functions) may seem complex but if you read the Descriptions in the first row you should be able to work out what and why it was done that way. Click on a cell to see what Function was used where.

You said that your spreadsheet was becoming a real mess, well I have created a monster for you (but not a mess).

I have uploaded the file to here:

http://users.tpg.com.au/lesliecl/

Hope this gives you the push to really start using Excel.

Apr 04, 2008 | Microsoft Excel for PC

1 Answer

Excel calculations


Just enter using this style


TIME IN TIME OUT HOURS 10:00 16:50 6:50 10:22 17:34 7:12 9:00 10:00 1:00
the formula in HOURS is Time Out - Time In

Mar 20, 2008 | Business & Productivity Software

1 Answer

Export data in excel shld yoeet through VB


When i first figured out how to pull data from SQL and put the results in an excel file i referenced these two articles....
Reading and writing excel file using VB.NET (http://www.codeproject.com/KB/vb/Work_with_Excel__VBNET_.aspx)
Get the Values From DataBase and Stored into excell Sheet (http://www.codeproject.com/KB/vb/Getvaluesfromdatabase.aspx)

This is the code i ended up using.... (check out those links to see how you need to import the ms office excel reference file with visual basic)

Const stcon As String = "Provider=SQLNCLI;server=xxxxx;database=xxxxx;uid=xxxxx;pwd=xxxxx;DataTypeCompatibility=80"
Dim stSQL As String = "select * from scs_rate_class_money where irate_book = 124 and snew_used = 'U' and sclass = '2' and splan = 'T4' and sopt_code = 'F1'"
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
'Open the connection.
cnt.Open(stcon)

'Open the recordset.
With rst
.CursorLocation = ADODB.CursorLocationEnum.adUseClient
.Open(stSQL, cnt, ADODB.CursorTypeEnum.adOpenForwardOnly, _
ADODB.LockTypeEnum.adLockReadOnly , _
ADODB.CommandTypeEnum.adCmdText)
.ActiveConnection = Nothing 'Disconnect the Recordset.
End With
'Close the connection
cnt.Close ()
Dim exp As Export = New Export()
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
Dim xlWBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet )
Dim xlWSheet As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
Dim xlRange As Microsoft.Office.Interop.Excel.Range = CType(xlWSheet, Microsoft.Office.Interop.Excel.Worksheet).Range("A2")
Dim xlCalc As Microsoft.Office.Interop.Excel.XlCalculation
Dim i As Short

'Turn off Excel's calculation.
With xlApp
xlCalc = .Calculation
.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual
End With
'Write the fieldnames.
For Each fld In rst.Fields
xlRange.Offset(0, i).Value = fld.Name
i = i + 1
Next
'Populate the range.
xlRange.Offset(1, 0).CopyFromRecordset(rst)
'Close the recordset.
rst.Close()
'Make Excel available to the user.
With xlApp
.Visible = True
.UserControl = True
'Restore the calculation mode.
.Calculation = xlCalc
End With
'Release variables from memory.
fld = Nothing
rst = Nothing
cnt = Nothing
xlRange = Nothing
xlWSheet = Nothing
xlWBook = Nothing
xlApp = Nothing

Jan 03, 2008 | Business & Productivity Software

3 Answers

Excel argument


If I understand correctly, you want to figure the normal wages at 40 hours and less. If there is more then 40 hours, calculate the normal 40 hours, then calculate the hours overtime (time and a half) and add them to get a total.

2bef84b.png

B5 columns are filled with this:
=IF(A5>$B$1,($B$2*$B$1)+((A5-$B$1)*($B$3)),A5*$B$2)

NOTE: The $ sign doesn't refer to money, it refers to an absolute reference so when copying a formula, the cell references with a $ sign are fixed and don't adjust according to the relative position to where it's being pasted.

Please rate as FixYa! or ask please clarify. ThankYa!

Oct 22, 2007 | Microsoft Excel 2003 (06503995)

Not finding what you are looking for?
Microsoft Office Professional 2007 Full Version for PC Logo

220 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18299 Answers

Tony

Level 3 Expert

2598 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...