| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Automatic calculation
I want to create a spreadsheet that will automatically calculate a sum... I
have no idea how to do this... My Telephone section took 1563 calls. All these calls added together took 80:39:43 (HH:MM:SS) Is there any way I can get staff to enter these numbers in to Excel to work out the AVERAGE call length in MINUTES? Any help is much appriciated, though I am virtually clueless when it comes to Excel. Thank you, -- Rob, Teesside, UK |
|
#2
|
|||
|
|||
|
Assume the times are in column B
C1: = SUM(b:B) and format as [hh]:mm C2: =AVERAGE(B:B) -- HTH Bob Phillips "abfabrob" wrote in message ... I want to create a spreadsheet that will automatically calculate a sum... I have no idea how to do this... My Telephone section took 1563 calls. All these calls added together took 80:39:43 (HH:MM:SS) Is there any way I can get staff to enter these numbers in to Excel to work out the AVERAGE call length in MINUTES? Any help is much appriciated, though I am virtually clueless when it comes to Excel. Thank you, -- Rob, Teesside, UK |
|
#3
|
|||
|
|||
|
Hey there,
Thanks you for the help, but can't get it to work... E.G. B1: 10:10:00 (HH:MM:SS) B2: 10 Your formula states answer as 05:05, when it should be 01:01:00 ??? -- Rob, Teesside, UK "Bob Phillips" wrote: Assume the times are in column B C1: = SUM(b:B) and format as [hh]:mm C2: =AVERAGE(B:B) -- HTH Bob Phillips "abfabrob" wrote in message ... I want to create a spreadsheet that will automatically calculate a sum... I have no idea how to do this... My Telephone section took 1563 calls. All these calls added together took 80:39:43 (HH:MM:SS) Is there any way I can get staff to enter these numbers in to Excel to work out the AVERAGE call length in MINUTES? Any help is much appriciated, though I am virtually clueless when it comes to Excel. Thank you, -- Rob, Teesside, UK |
|
#4
|
|||
|
|||
|
Rob,
Are you saying that B 1 is a call time, and B2 is an other call time? If so, why is B2 not in time, and why is the answer 1:01:00? -- HTH Bob Phillips "abfabrob" wrote in message ... Hey there, Thanks you for the help, but can't get it to work... E.G. B1: 10:10:00 (HH:MM:SS) B2: 10 Your formula states answer as 05:05, when it should be 01:01:00 ??? -- Rob, Teesside, UK "Bob Phillips" wrote: Assume the times are in column B C1: = SUM(b:B) and format as [hh]:mm C2: =AVERAGE(B:B) -- HTH Bob Phillips "abfabrob" wrote in message ... I want to create a spreadsheet that will automatically calculate a sum... I have no idea how to do this... My Telephone section took 1563 calls. All these calls added together took 80:39:43 (HH:MM:SS) Is there any way I can get staff to enter these numbers in to Excel to work out the AVERAGE call length in MINUTES? Any help is much appriciated, though I am virtually clueless when it comes to Excel. Thank you, -- Rob, Teesside, UK |
|
#5
|
|||
|
|||
|
Bob,
Using your formula, I put my numbers in column B (and what you told me to put in column C) 10:10:00 in cell B1 - this is HH:MM:SS of ALL calls (total time of all calls) 10 in cell B2 - this is how many calls were taken, So 10:10:00 (10 hours and 10 mins), divided by ten calls gives you an average of 01:01:00 (1 hour and 1 min per call). Obviously my figures will be more complicated than this in the real thing. It will be me - not very gopod with Excel. Thank you for your help. -- Rob, Teesside, UK "Bob Phillips" wrote: Rob, Are you saying that B 1 is a call time, and B2 is an other call time? If so, why is B2 not in time, and why is the answer 1:01:00? -- HTH Bob Phillips "abfabrob" wrote in message ... Hey there, Thanks you for the help, but can't get it to work... E.G. B1: 10:10:00 (HH:MM:SS) B2: 10 Your formula states answer as 05:05, when it should be 01:01:00 ??? -- Rob, Teesside, UK "Bob Phillips" wrote: Assume the times are in column B C1: = SUM(b:B) and format as [hh]:mm C2: =AVERAGE(B:B) -- HTH Bob Phillips "abfabrob" wrote in message ... I want to create a spreadsheet that will automatically calculate a sum... I have no idea how to do this... My Telephone section took 1563 calls. All these calls added together took 80:39:43 (HH:MM:SS) Is there any way I can get staff to enter these numbers in to Excel to work out the AVERAGE call length in MINUTES? Any help is much appriciated, though I am virtually clueless when it comes to Excel. Thank you, -- Rob, Teesside, UK |
|
#6
|
|||
|
|||
|
I don't see the problem. When I put 10:10:00 in A1 (formatted hh:mm:ss) and
10 in B1, the formula A1/B1 gives me 1:01:00... "abfabrob" schreef in bericht ... Hey there, Thanks you for the help, but can't get it to work... E.G. B1: 10:10:00 (HH:MM:SS) B2: 10 Your formula states answer as 05:05, when it should be 01:01:00 ??? -- Rob, Teesside, UK "Bob Phillips" wrote: Assume the times are in column B C1: = SUM(b:B) and format as [hh]:mm C2: =AVERAGE(B:B) -- HTH Bob Phillips "abfabrob" wrote in message ... I want to create a spreadsheet that will automatically calculate a sum... I have no idea how to do this... My Telephone section took 1563 calls. All these calls added together took 80:39:43 (HH:MM:SS) Is there any way I can get staff to enter these numbers in to Excel to work out the AVERAGE call length in MINUTES? Any help is much appriciated, though I am virtually clueless when it comes to Excel. Thank you, -- Rob, Teesside, UK |
|
#7
|
|||
|
|||
|
When I leave the cells blank - A1: 00:00:00 and A2: 0, A3 displays: "#DIV/0!"
(but without the qutation marks. Why is this? I want it to be all zeros so that users can enter their own numbers... Thanks for your help. -- Rob, Teesside, UK "Bart Snel" wrote: I don't see the problem. When I put 10:10:00 in A1 (formatted hh:mm:ss) and 10 in B1, the formula A1/B1 gives me 1:01:00... "abfabrob" schreef in bericht ... Hey there, Thanks you for the help, but can't get it to work... E.G. B1: 10:10:00 (HH:MM:SS) B2: 10 Your formula states answer as 05:05, when it should be 01:01:00 ??? -- Rob, Teesside, UK "Bob Phillips" wrote: Assume the times are in column B C1: = SUM(b:B) and format as [hh]:mm C2: =AVERAGE(B:B) -- HTH Bob Phillips "abfabrob" wrote in message ... I want to create a spreadsheet that will automatically calculate a sum... I have no idea how to do this... My Telephone section took 1563 calls. All these calls added together took 80:39:43 (HH:MM:SS) Is there any way I can get staff to enter these numbers in to Excel to work out the AVERAGE call length in MINUTES? Any help is much appriciated, though I am virtually clueless when it comes to Excel. Thank you, -- Rob, Teesside, UK |
|
#8
|
|||
|
|||
|
I have tried this, but get a message saying that the formula contains an error.
-- Rob, Teesside, UK "Bart Snel" wrote: Then it would be: =IF(A20;A1/A2;0) Bart Snel "abfabrob" schreef in bericht ... When I leave the cells blank - A1: 00:00:00 and A2: 0, A3 displays: "#DIV/0!" (but without the qutation marks. Why is this? I want it to be all zeros so that users can enter their own numbers... Thanks for your help. -- Rob, Teesside, UK "Bart Snel" wrote: I don't see the problem. When I put 10:10:00 in A1 (formatted hh:mm:ss) and 10 in B1, the formula A1/B1 gives me 1:01:00... "abfabrob" schreef in bericht ... Hey there, Thanks you for the help, but can't get it to work... E.G. B1: 10:10:00 (HH:MM:SS) B2: 10 Your formula states answer as 05:05, when it should be 01:01:00 ??? -- Rob, Teesside, UK "Bob Phillips" wrote: Assume the times are in column B C1: = SUM(b:B) and format as [hh]:mm C2: =AVERAGE(B:B) -- HTH Bob Phillips "abfabrob" wrote in message ... I want to create a spreadsheet that will automatically calculate a sum... I have no idea how to do this... My Telephone section took 1563 calls. All these calls added together took 80:39:43 (HH:MM:SS) Is there any way I can get staff to enter these numbers in to Excel to work out the AVERAGE call length in MINUTES? Any help is much appriciated, though I am virtually clueless when it comes to Excel. Thank you, -- Rob, Teesside, UK |
|
#9
|
|||
|
|||
|
I have tried this, but get a message saying that the formula contains an error.
-- Rob, Teesside, UK "Bart Snel" wrote: Then it would be: =IF(A20;A1/A2;0) Bart Snel "abfabrob" schreef in bericht ... When I leave the cells blank - A1: 00:00:00 and A2: 0, A3 displays: "#DIV/0!" (but without the qutation marks. Why is this? I want it to be all zeros so that users can enter their own numbers... Thanks for your help. -- Rob, Teesside, UK "Bart Snel" wrote: I don't see the problem. When I put 10:10:00 in A1 (formatted hh:mm:ss) and 10 in B1, the formula A1/B1 gives me 1:01:00... "abfabrob" schreef in bericht ... Hey there, Thanks you for the help, but can't get it to work... E.G. B1: 10:10:00 (HH:MM:SS) B2: 10 Your formula states answer as 05:05, when it should be 01:01:00 ??? -- Rob, Teesside, UK "Bob Phillips" wrote: Assume the times are in column B C1: = SUM(b:B) and format as [hh]:mm C2: =AVERAGE(B:B) -- HTH Bob Phillips "abfabrob" wrote in message ... I want to create a spreadsheet that will automatically calculate a sum... I have no idea how to do this... My Telephone section took 1563 calls. All these calls added together took 80:39:43 (HH:MM:SS) Is there any way I can get staff to enter these numbers in to Excel to work out the AVERAGE call length in MINUTES? Any help is much appriciated, though I am virtually clueless when it comes to Excel. Thank you, -- Rob, Teesside, UK |
|
#10
|
|||
|
|||
|
Rob,
I see now. The problem is that you are mixing the call times and the calls together, so any formula such that I gave will get confused by them. Put your call times in column B, call numbers in column C, and then use =SUM(A:A)/SUM(B:B) to get the average -- HTH Bob Phillips "abfabrob" wrote in message ... Bob, Using your formula, I put my numbers in column B (and what you told me to put in column C) 10:10:00 in cell B1 - this is HH:MM:SS of ALL calls (total time of all calls) 10 in cell B2 - this is how many calls were taken, So 10:10:00 (10 hours and 10 mins), divided by ten calls gives you an average of 01:01:00 (1 hour and 1 min per call). Obviously my figures will be more complicated than this in the real thing. It will be me - not very gopod with Excel. Thank you for your help. -- Rob, Teesside, UK "Bob Phillips" wrote: Rob, Are you saying that B 1 is a call time, and B2 is an other call time? If so, why is B2 not in time, and why is the answer 1:01:00? -- HTH Bob Phillips "abfabrob" wrote in message ... Hey there, Thanks you for the help, but can't get it to work... E.G. B1: 10:10:00 (HH:MM:SS) B2: 10 Your formula states answer as 05:05, when it should be 01:01:00 ??? -- Rob, Teesside, UK "Bob Phillips" wrote: Assume the times are in column B C1: = SUM(b:B) and format as [hh]:mm C2: =AVERAGE(B:B) -- HTH Bob Phillips "abfabrob" wrote in message ... I want to create a spreadsheet that will automatically calculate a sum... I have no idea how to do this... My Telephone section took 1563 calls. All these calls added together took 80:39:43 (HH:MM:SS) Is there any way I can get staff to enter these numbers in to Excel to work out the AVERAGE call length in MINUTES? Any help is much appriciated, though I am virtually clueless when it comes to Excel. Thank you, -- Rob, Teesside, UK |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find automatic page breaks | Excel Worksheet Functions | |||
| How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
| How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
| automatic calculation freezes | Excel Worksheet Functions | |||
| time-clock calculation | Excel Worksheet Functions | |||
Linear Mode
