Free Microsoft Excel 2013 Quick Reference

Excel formula to calculate the average flow per hour

I have attached the excel sheet with sample data. I need more generalised formula for flows per hour and flows per day since the existing formula in the sheet has many drawbacks:
1. At the end it shows a negative value and cant avoid it (since the number of rows is not fixed, I get this data from database by using a macro)
2. If the flow at any particular time( in this case say 19032012 00:36 is missing) fails then the whole formula for flow per hour goes wrong

Can I have a formula which calculates flows per hour depending on the time ( in this case 00:06 to 00:51)

Related Results

How do I create a formula to calculate the average percentage rat

How do i create a formula in Excel to calculate the average percentage rate
of a 30 year financing period
For example Loan amount 135,000 montly payment amount 684.03 number of
payments 360 intrest rate 4.5% and prepaid finance charge of 1770.57. The
first 74 payments the monthly payment amount will be 742.53 and for the
remaining payments of 286 the monthly note will be 684.03

Formula to calculate the average of a range basing on condition

Hi,
I have a situation like this below..
18-Feb-02 02-Jun-04
26-Jul-02 08-Jun-04
29-Jul-03 10-Jun-04
22-Oct-03 05-Jun-04
01-Nov-03 10-Jun-04
11-Nov-03 14-Jun-04
18-Nov-03 04-Jun-04
20-Dec-03 21-May-04
30-Mar-04 10-Jun-04
08-May-04 22-Jun-04
29-May-04 15-Aug-04

11-Jun-04 11-Jul-04
12-Jun-04 16-Jun-04
22-Jun-04 28-Jun-04
24-Jun-04 26-Jun-04

10-Dec-04 23-Dec-04

28-Aug-04 01-Sep-04
28-Sep-04 29-Sep-04
08-Dec-04 22-Dec-04

20-Aug-04 29-Aug-04

25-Aug-04 31-Aug-04
30-Aug-04 10-Sep-04
03-Jan-05 10-Jan-05
27-Sep-04 05-Oct-04
30-Sep-04 07-Oct-04
03-Oct-04 04-Oct-04

The above are two columns of excel. I want to calculate the average of the
difference for each month in another work sheet like bwlow

Month Difference..

Can some one help me on how I can do that

Excel formula to calculate vaction time

Hello all,
i am trying to figure out an excel formula to calculate vacation time here is my company's policy:
Vacation time does not roll over.

years of service monthly vacation accrual total days/year less than 6 0.83 10 6 through 10 1.25 15 11 or more 1.66 20

new hire must complete 6 months. Ee will earn 1 day per completed month until the end of the calendar year, at the beginning of the following year ee will use schedule above not to exceed 10 days. if employee was hired after july 1st he must wait until 3rd year to use the schedule above.

Formula to calculate the number of books (same size) can be put into a box

Hi All,

I am stuck with getting a feasible formula which is able to calculate the number of books (Length x Width x Height) that can fit into a carton box (Length x Width x Height)

Data Inside the Excel
- Carton Box Size (L x W x H)

Input Data
- Book Size (L x W x H)
- Book Quantity

Output Data
- Carton Box Quantity

By placing all the books in one position inside, I could use the below formula (six), if
- Carton Box Length -- CL
- Carton Box Width -- CW
- Carton Box Height -- CH
- Book Length -- BL
- Book Width -- BW
- Book Height -- BH

=Rounddown(CL/BL)*Rounddown(CW/BW)*Rounddown(CH/BH)
=Rounddown(CL/BL)*Rounddown(CH/BW)*Rounddown(CW/BH)
=Rounddown(CW/BL)*Rounddown(CH/BW)*Rounddown(CL/BH)
=Rounddown(CW/BL)*Rounddown(CL/BW)*Rounddown(CH/BH)
=Rounddown(CH/BL)*Rounddown(CL/BW)*Rounddown(CW/BH)
=Rounddown(CH/BL)*Rounddown(CW/BW)*Rounddown(CL/BH)

However, things get tricky, if we take into consideration the extra space that could allow the books to be inserted into the carton box in a different position. I could not think of a good formula to calculate the space, as I tried use the "((volume of box) - (number of books * volume of book)) / volume of book" it does not work if the book do not fit the size of space, despite the volume of the empty space shows at least 1.

I need a formula to calculate the ratio of #'s in a row and column

Hello. I'm using Excel 2000 and need help finding a formula to calculate the
ratio in columns and rows.

For example: I'm using 1's and 0's ( 1 if the item is present and 0 if it is
not present). So, I need a formula that will show 8/10 of the items were
present. See my example below.

Column C
1
0
1
0
1
=3 (sum)
=3/5 (ratio)

Excel formula to calculate % gain when adding cash equity to accou

I maintain an Excel spreadsheet that is updated daily with the % gain/loss on
my stock brokerage accounts. How do I change the formula to calculate the
accurate % gain in my stock portfolio when I add cash equity to one of the
accounts? If I don't change the cost basis in the formula, the % gain in the
account increases excessively because it thinks I have gained all of the
added money as interest over the preceding 24 hours. If I change the cost
basis to account for the new money added to the account, my % gain year to
date decreases (from what it was the day before adding the cash equity)
because the cost basis is now higher than it was the day before. I want to
be able to change the formula going forward each time I add cash equity, but
still have an accurate year-to-date % gain from interest I have earned.
Thanks.

Formula to calculate the hours worked and gross amount earned

Hi,

I would like some help with a formula.

I have a spreadsheet which has columns with date ranges and an amount of hours worked during that date range. I then have other columns with different date ranges. These date ranges cross over with the date ranges of the first ones. I need to find out how much is earned in the second lot of date ranges from the other information.

It's hard to explain, but if you take a look at the attachment you will understand. Under the first lot of columnns you will see that there are week and fortnightly date ranges. Then total amount of hours worked in that period. I then have a column which works out the amount of working days during that period. I then have hours worked per day, hourly rate and a gross amount for that period. I then have a different set of date ranges. I need the formula to calculate the hours worked and gross amount earned in the second lot of date ranges based on the info from the first set of date ranges. For example: fortnight 22/1/12 - 4/2/12 the person worked 53 hours at \$25 per hour.This equals a gross amount earned of \$1325. And for the fortnight 5/2/12 - 18/2/12 the person worked 76 hours at \$25 per hour. This equals a gross amount earned of \$1900. Now, the other fortnight date range shows fortnight 24/1/12 - 6/2/12. Based on the info from the first columns I need to work out what the hours and gross earnings are for the period 24/1/12 - 6/2/12.

How to calculate the average cycle time

Hi -

I was wondering if there is an easy way to calculate the average cycle time when dates and times are present.
I have a spreadsheet with approximately 40k rows and require an average cycle time per truck.

Any assistance will be greatly appreciated otherwise i'll be doing it manually

To How to use Excel formula to count the item

How to use Excel formula to count the item and generate the 'LineNo'
column and the result look like the following:

LineNo Item Location
0 A101 L01
1 A101 L02
2 A101 L03
0 B100 L01
0 C111 L02
1 C111 L03
2 C111 L05
3 C111 L08

--
annsmjarm
------------------------------------------------------------------------
annsmjarm's Profile: http://www.excelforum.com/member.php...o&userid=13691

Excel formula to convert the first three (3) digits of a person’s social security num

I am trying to find an Excel formula to convert the first three (3) digits of a person’s social security number to the state in which a person first applied for a Social Security Card. For example, social security numbers which start from 449 to 467 represents a Texas, 486 to 500 present Missouri and so on. Based on the table on the Social Security Administration’s web site (http://www.ssa.gov/employer/stateweb.htm), I would like to be able to put social security number in Column A and obtain the state in column B. Thank you for your effort and time

Array formula to calculate the highest cumulative value

Hi,

I have a column of numbers that I need to accumulate using the formula:

=(product(1+(a1:a2000)/100)-1)*100

Can someone please help me with an array formula to calculate the maximum accumulated value of the column of values?

Thanks!

Dividing data into bins and calculating the averages

Hi everyone,

I have huge amount of data with me. I have speeds of vehicles generated at random times(for 24 hrs) in a column. Now I need to divide the data into bins of 300 secs (5 mins) and calculate the average value of speeds for every bin.

The problem I am facing is that the bin size varies from time to time due to randomness in data. So this makes it difficult to the application of a single formula to calculate the average of speeds for vehicles in a particular bin. I can do this manually by seeing the times and calculating the average for every 5 min interval, but it would take a lot of time as the dat is huge.

Can anyone please let me know the procedure to do this?

Thanks
sashi

PS: please let me know if the question is not clear

Average days per month

I have a massive list of dates in column A in dd/mm/yyyy format (United Kingdom), from which I'd like to write a formula to calculate the average days per month. Is this possible??

Many thanks!

Calculating the average of cells

I am trying to use a formula to calculate the average of random cells
currently containing data, however also including cells where data will be
added at a later date but remain empty. Is this possible?

To How to use Excel formula to count the item

How to use Excel formula to count the item and generate the 'LineNo' column and the result look like the following:

LineNo Item Location
0 A101 L01
1 A101 L02
2 A101 L03
0 B100 L01
0 C111 L02
1 C111 L03
2 C111 L05
3 C111 L08

Formula to calculate total values for several unique IDs

I have an excel sheet which has two columns- Employee ID and Number of hours worked on project. Employee IDs and Number of hours worked go like this-

ID Hours worked on project

101 .76
101 .33
102 .97
103 .77
103 .54
103 .65
103 .66
104 .87
104 .64

This goes on for 1830 ID values.

I need a formula to calculate the total number of hours worked by each employee, for example Employee 101 has worked a total of 1.09 hours. Employee 102 has worked .97 hours. Employee 103 has worked 2.62 hours and so on for each employee. Each employee ID occurs multiple times (in many cases) because each employee works on several projects at the same time.

Please give me a formula, thank you very much for the help!

Formula to calculate total for each unique value

I have an excel sheet which has two columns- Employee ID and Number of hours worked on project. Employee IDs and Number of hours worked go like this-

ID Hours worked on project

101 .76
101 .33
102 .97
103 .77
103 .54
103 .65
103 .66
104 .87
104 .64

This goes on for 1830 ID values

I need a formula to calculate the total number of hours worked by each employee, for example Employee 101 has worked a total of 1.09 hours. Employee 102 has worked .97 hours. Employee 103 has worked 2.62 hours and so on for each employee. Each employee ID occurs multiple times (in many cases) because each employee works on several projects at the same time.

Please give me a formula, thank you very much for the help!

Need a formula to calculate the number of months to pay off a loan

I need a formula to calculate the number of months it will take to pay off a
loan, given interest rate, balance and the amount of payments.

Excel formula to calculate percentage for hours worked

Hi

I am currenlt working on a speadsheet for clocked hours (in & out)

I have managed to work out the formula to calculate the total hours worked, now what i need to do is calculate the percentage of hours spend on site but for some reason the formula isnt working.

Total hours on site 08.10hrs
Contracted Hours 10
% = ??

the formual i used for this was total hours on site/contracted hours*1 =

This formula should give me an answer of 81% but for soem reason its giving me and answer of 3.4%. can anyone shed any light on this? is my cell format wrong?

In Out In Out In Out Total allocated %
7:50 AM 10:00 AM 10:32 AM 1:30 PM 2:24 PM 5:26 PM 08.10 10 3.4

Thanks

Kate

Calculating the average over the last 5 values.

I am looking for a formula to calculate the average for each row of numbers but to only pick the last 5 values (but to ignor 0).

So the averages of the below rows
A 22 22 26 24 0 0 21 59 74 68
B 100 98 100 0 90 100 94 95 91 49
C 56 0 0 0 0 0 52 95 41 28
D 47 80 0 50 0 47 0 95 0 97

As time goes on there will be more data added these (in the spreadsheet 0's will be blank) so the formula will need to average the last 5 data points from the right of the row.

Formula to calculate average in a column

Hello:

We get checks from Client and i add them up in excel sheet as is shown in column B,C,D,E,etc
Sum of checks is in Row 4.
I need a formula for Row 3, to calculate the average per check for that particular deposits.
Example : Column B , Total of all checks was \$4865.80 consisting of 75 checks so averge would be
\$4865.80/75 = \$64.88 This would need to be in B3

Similarly C3 = 7145.01/136 = \$52.54
D3
E3, etc.....

Please let me know if you have any questions.

RM

How to make a formula to calculate the right answered questions for the Questionnaire

I am analyzing my Questionnaire where i want to calculate the results using Excel.

Supposedly there are 5 Multiple Choice Questions asked to the different set of peoples.

Sno Q1 Q2 Q3 Q4 Q5 TOTAL
1 A B C D E ?
2 A C B D E ?
3 B D D E A ?
4 A C A D A ?
5 A B C D A ?

I want to calculate that how out 5 questions, how many correct answers has been given by person 1?
If the answer to question 1 =a, q2=a, q3=c, q4=b,q5=d then how many answers in total has the person 1 has given correct...??

Make excel able to calculate the data by using highlighted cells.

I'm trying to make an excel to keep track of my time spent on things. I am trying to spend more time being productive so i'm hoping this will help me realize how much time I am spending being unproductive so I can change that habit. Ironically, I've wasted a few hours being "unproductive" making this excel.

Anyway, the attachment is what I made. It's not at all complicated, it was just me messing around. Any suggestions on how to improve it somehow? It has no formulas or inputs that will change anything, which is what I want to do. I have basically no idea how to work excel though. What I want, if it's even possible, is excel to calculate the total hours of productivity, unproductivity, and neutral at the end of each day by calculating each specifically highlighted cell, each cell being 30 minutes, and making it show at the end.

A bit hard to plain, take a look at the excel and you will understand. It probably looks dumb and over simplistic to veteran excel users.

How do I write the formula to calculate someones time worked

I would like to use excel spreadsheet and calculate a simple payroll time
card. A person start time to work is 10:15 pm (cell A 1) and end time is
2am (cell B 1). What would be the formula for cell c1 to calculate the total
number of hours worked which would be 3 3/4 hours total worked. .