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)

Thanks in advance

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)

Thanks in advance

- How do I create a formula to calculate the average percentage rat
- Formula to calculate the average of a range basing on condition
- Excel formula to calculate vaction time
- Formula to calculate the number of books (same size) can be put into a box
- I need a formula to calculate the ratio of #'s in a row and column
- Excel formula to calculate % gain when adding cash equity to accou
- Formula to calculate the hours worked and gross amount earned
- How to calculate the average cycle time
- To How to use Excel formula to count the item
- Array formula to calculate the highest cumulative value
- Dividing data into bins and calculating the averages
- Average days per month
- Calculating the average of cells
- To How to use Excel formula to count the item
- Formula to calculate total values for several unique IDs
- Formula to calculate total for each unique value
- Need a formula to calculate the number of months to pay off a loan
- Excel formula to calculate percentage for hours worked
- Calculating the average over the last 5 values.
- Formula to calculate average in a column
- How to make a formula to calculate the right answered questions for the Questionnaire
- Make excel able to calculate the data by using highlighted cells.
- How do I write the formula to calculate someones time worked

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

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

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.

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.

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)

Thanks for your help!!!! Kelly

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.

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.

Thankyou for all your help

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.

Attached is an example spreadsheet.

Dummy Spreadsheet.xlsx

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

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

View this thread: http://www.excelforum.com/showthread...hreadid=467435

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!

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

Many thanks!

currently containing data, however also including cells where data will be

added at a later date but remain empty. Is this possible?

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

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!

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!

loan, given interest rate, balance and the amount of payments.

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.

Please see example below:

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

This is a copy of the information i am trying to work with...please help!!!

Thanks

Kate

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.

Please refer to attached sheet.

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.

Thank you in advanve for your help.

RM

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...??

Please help me in this. I am trying to make a formula for this.

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.

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. .

No luck finding an answer? You could always try Google.