Question about Microsoft Excel for PC

__Hi there__

__I know how to solve this in your head it's a bit late but I've got the answer !!__

__9 mins and 15 secs = 9 1/4 mins__

__= Speed - distance divided by time = 19.24 / (9+1 / 4) = 2.08 __

__If this is useful then I'm glad i could help :)__

Posted on Oct 07, 2009

Hi, here is the solution

1. Select column you want to put times.

2. Right click and select format.

3. go to Number tab and select custom

4. from custom list select h:mm:ss

Thats it

type the times in this format and apply average formula e.gl =AVERAGE(A1:A2:A3)

I will work, I just tested.

Posted on Mar 10, 2009

You only need one formula for this.

Total handling time divided by the total number of emails answered.

Total handling time divided by the total number of emails answered.

May 13, 2013 | Microsoft Excel for PC

Try this. If any of the cells are zero (or blank), the word 'zero' will be displayed. Substitute anything you want for the text including blank ""

=IF(OR(F61=0,J61=0,N61=0),"zero",AVERAGE(F61,J61,N61))

=IF(OR(F61=0,J61=0,N61=0),"zero",AVERAGE(F61,J61,N61))

Aug 26, 2009 | Microsoft Office Excel 2007

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.

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.

Mar 09, 2009 | Microsoft Office Professional 2007 Full...

This solution on average add 10 new worksheets in less than 5 sec.

Hold down shift key, then hit F11 as many time as many sheet you want to add

Other than SHIFT+F11, ALT+SHIFT+F1 also Insert a new worksheet, it's just to you which is more convenient.

Regards,

Hold down shift key, then hit F11 as many time as many sheet you want to add

Other than SHIFT+F11, ALT+SHIFT+F1 also Insert a new worksheet, it's just to you which is more convenient.

Regards,

Sep 18, 2008 | Microsoft Office Standard for PC

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.

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

You can refer to cells that are on other worksheets by perpending the name of the worksheet followed by an exclamation point (**!**)
to the cell reference. In the following example, the AVERAGE worksheet
function calculates the average value for the range C1:C10 on the
worksheet named Marketing in the same workbook.

Refers to the worksheet named Marketing Refers to the range of cells between C1 and C10, inclusively

Refers to the worksheet named Marketing Refers to the range of cells between C1 and C10, inclusively

- Click the cell in which you want to enter the formula.
- In the formula bar (formula
bar: A bar at the top of the Excel window that you use to enter or edit
values or formulas in cells or charts. Displays the constant value or
formula stored in the active cell.)
, type
**=**(equal sign). - Click the tab for the worksheet to be referenced.
- Select the cell or range of cells to be referenced.

Jan 01, 2008 | Microsoft Office Standard for PC

Here is one way:

In this example, my numbers are in cells a1 through a4. My average is computed with the formula:

=SUM(A1:A4)/COUNTIF(A1:A4,"<>0")

I'm summing the range of numbers and dividing that by the count of nonzero numbers. With this formula, you'll get an error if there are no nonzero numbers.

In this example, my numbers are in cells a1 through a4. My average is computed with the formula:

=SUM(A1:A4)/COUNTIF(A1:A4,"<>0")

I'm summing the range of numbers and dividing that by the count of nonzero numbers. With this formula, you'll get an error if there are no nonzero numbers.

Dec 30, 2007 | Business & Productivity Software

try this site...i hope thats what you mean when you say combine both formula.

Oct 25, 2007 | Microsoft Excel for PC

The Data Analysis Add-in has a Moving Average fuction and wizard.
Moving Average

Aug 30, 2007 | Microsoft Office Standard for PC

Jan 28, 2016 | Microsoft Excel for PC

109 people viewed this question

Usually answered in minutes!

Sorry my mistake( it was very late when I sent the Q ). I left out some important info. Car A takes 9min 15 secs to travel 19.24 kms.What is his average speed over that distance? I can work it out if I put the time in as 9.25mins. (I have a of list secs *1.6666666. ie 15 secs =.25 min ,33secs= .55min etc). I would rather not have to check my list, I would rather type 19.24 one cell and 9:15 in the next then do my calc from there. Thank for the previos answer, I sent the wrong info :-(

×