| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
analizing a sheet
I have a sheet of my phone usage. i have a cullom of the country I call,the
number i phoned, the time the call took, the price I pay/ I need to fined out : 1. How much time and what % for each country. 2. How much money and what % of the cost for each country. 3. How much time and cost (real and %) for cellular an for regular phone per each country (I can tell by the number)/ How is this done? |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
analizing a sheet
Hi,
can we see a couple of lines of your data table. Mike "Danona" wrote: I have a sheet of my phone usage. i have a cullom of the country I call,the number i phoned, the time the call took, the price I pay/ I need to fined out : 1. How much time and what % for each country. 2. How much money and what % of the cost for each country. 3. How much time and cost (real and %) for cellular an for regular phone per each country (I can tell by the number)/ How is this done? |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
analizing a sheet
france 33625290482 13/02/2008 10:38:43 00:00:26 0.365 0 0.365 france 33143748795 13/02/2008 10:57:36 00:10:37 2.643 1.777 0.866 france 33626121615 13/02/2008 11:20:00 00:00:34 0.477 0 0.477 france 33650198179 13/02/2008 11:21:31 00:00:37 0.519 0 0.519 france 33623581321 13/02/2008 11:24:19 00:00:36 0.505 0 0.505 france 33650364551 13/02/2008 11:27:22 00:02:49 2.369 0 2.369 france 33621207891 13/02/2008 11:33:22 00:00:31 0.435 0 0.435 france 33650454353 13/02/2008 11:34:50 00:00:36 0.505 0 0.505 france 33674254315 13/02/2008 11:43:18 00:02:27 2.061 0 2.061 france 33613381848 13/02/2008 11:49:23 00:00:24 0.336 0 0.336 france 33621302136 13/02/2008 11:50:31 00:10:07 8.51 0 8.51 "Mike H" wrote: Hi, can we see a couple of lines of your data table. Mike "Danona" wrote: I have a sheet of my phone usage. i have a cullom of the country I call,the number i phoned, the time the call took, the price I pay/ I need to fined out : 1. How much time and what % for each country. 2. How much money and what % of the cost for each country. 3. How much time and cost (real and %) for cellular an for regular phone per each country (I can tell by the number)/ How is this done? |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
analizing a sheet
Hi,
All your calculations can be done using the Sumproduct function but first you need total cost and total duration and to get those simply sum the appropriate columns =SUM(H2:H12) for the total cost (Change the range to suit) =SUM(E2:E12) for the duration. Format this cell with [h]:mm:ss Now to get the total cost for france (Column H is call cost) =SUMPRODUCT((A2:A12="France")*(H2:H12)) Duration for france =SUMPRODUCT((A2:A12="France")*(E2:E12)) To get the percentages for each then use thi =(SUMPRODUCT((A2:A12="France")*(F2:F12)))/SUM(F2:F12) I can't help on mobiles because I don't understande which are mobile numbers Mike "Danona" wrote: france 33625290482 13/02/2008 10:38:43 00:00:26 0.365 0 0.365 france 33143748795 13/02/2008 10:57:36 00:10:37 2.643 1.777 0.866 france 33626121615 13/02/2008 11:20:00 00:00:34 0.477 0 0.477 france 33650198179 13/02/2008 11:21:31 00:00:37 0.519 0 0.519 france 33623581321 13/02/2008 11:24:19 00:00:36 0.505 0 0.505 france 33650364551 13/02/2008 11:27:22 00:02:49 2.369 0 2.369 france 33621207891 13/02/2008 11:33:22 00:00:31 0.435 0 0.435 france 33650454353 13/02/2008 11:34:50 00:00:36 0.505 0 0.505 france 33674254315 13/02/2008 11:43:18 00:02:27 2.061 0 2.061 france 33613381848 13/02/2008 11:49:23 00:00:24 0.336 0 0.336 france 33621302136 13/02/2008 11:50:31 00:10:07 8.51 0 8.51 "Mike H" wrote: Hi, can we see a couple of lines of your data table. Mike "Danona" wrote: I have a sheet of my phone usage. i have a cullom of the country I call,the number i phoned, the time the call took, the price I pay/ I need to fined out : 1. How much time and what % for each country. 2. How much money and what % of the cost for each country. 3. How much time and cost (real and %) for cellular an for regular phone per each country (I can tell by the number)/ How is this done? |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
analizing a sheet
1. If you can explain to me what is the difrence between sum and sumproduct.
2. my problem is that my sheet does not have each country separetly but has mixed countries ,example france france spain spain spain Venezuela france etc so how do i work with that I cant sum up collums. "Mike H" wrote: Hi, All your calculations can be done using the Sumproduct function but first you need total cost and total duration and to get those simply sum the appropriate columns =SUM(H2:H12) for the total cost (Change the range to suit) =SUM(E2:E12) for the duration. Format this cell with [h]:mm:ss Now to get the total cost for france (Column H is call cost) =SUMPRODUCT((A2:A12="France")*(H2:H12)) Duration for france =SUMPRODUCT((A2:A12="France")*(E2:E12)) To get the percentages for each then use thi =(SUMPRODUCT((A2:A12="France")*(F2:F12)))/SUM(F2:F12) I can't help on mobiles because I don't understande which are mobile numbers Mike "Danona" wrote: france 33625290482 13/02/2008 10:38:43 00:00:26 0.365 0 0.365 france 33143748795 13/02/2008 10:57:36 00:10:37 2.643 1.777 0.866 france 33626121615 13/02/2008 11:20:00 00:00:34 0.477 0 0.477 france 33650198179 13/02/2008 11:21:31 00:00:37 0.519 0 0.519 france 33623581321 13/02/2008 11:24:19 00:00:36 0.505 0 0.505 france 33650364551 13/02/2008 11:27:22 00:02:49 2.369 0 2.369 france 33621207891 13/02/2008 11:33:22 00:00:31 0.435 0 0.435 france 33650454353 13/02/2008 11:34:50 00:00:36 0.505 0 0.505 france 33674254315 13/02/2008 11:43:18 00:02:27 2.061 0 2.061 france 33613381848 13/02/2008 11:49:23 00:00:24 0.336 0 0.336 france 33621302136 13/02/2008 11:50:31 00:10:07 8.51 0 8.51 "Mike H" wrote: Hi, can we see a couple of lines of your data table. Mike "Danona" wrote: I have a sheet of my phone usage. i have a cullom of the country I call,the number i phoned, the time the call took, the price I pay/ I need to fined out : 1. How much time and what % for each country. 2. How much money and what % of the cost for each country. 3. How much time and cost (real and %) for cellular an for regular phone per each country (I can tell by the number)/ How is this done? |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
analizing a sheet
Hi,
I tested it with mixed countries in column A and it works fine =SUMPRODUCT((A2:A12="France")*(E2:E12)) This formula will sum column E only when it finds France in coulmn A. Likewise you could have =SUMPRODUCT((A2:A12="Germany")*(E2:E12)) and it would then sum only when it finds Germany in Column A If you want percentage cost/duration by country then you need a simple sum of the cost/duration columns for all countries If your stuck then post an extract of you workbook here http://www.savefile.com/ and I'l put the formula in for you Mike "Danona" wrote: 1. If you can explain to me what is the difrence between sum and sumproduct. 2. my problem is that my sheet does not have each country separetly but has mixed countries ,example france france spain spain spain Venezuela france etc so how do i work with that I cant sum up collums. "Mike H" wrote: Hi, All your calculations can be done using the Sumproduct function but first you need total cost and total duration and to get those simply sum the appropriate columns =SUM(H2:H12) for the total cost (Change the range to suit) =SUM(E2:E12) for the duration. Format this cell with [h]:mm:ss Now to get the total cost for france (Column H is call cost) =SUMPRODUCT((A2:A12="France")*(H2:H12)) Duration for france =SUMPRODUCT((A2:A12="France")*(E2:E12)) To get the percentages for each then use thi =(SUMPRODUCT((A2:A12="France")*(F2:F12)))/SUM(F2:F12) I can't help on mobiles because I don't understande which are mobile numbers Mike "Danona" wrote: france 33625290482 13/02/2008 10:38:43 00:00:26 0.365 0 0.365 france 33143748795 13/02/2008 10:57:36 00:10:37 2.643 1.777 0.866 france 33626121615 13/02/2008 11:20:00 00:00:34 0.477 0 0.477 france 33650198179 13/02/2008 11:21:31 00:00:37 0.519 0 0.519 france 33623581321 13/02/2008 11:24:19 00:00:36 0.505 0 0.505 france 33650364551 13/02/2008 11:27:22 00:02:49 2.369 0 2.369 france 33621207891 13/02/2008 11:33:22 00:00:31 0.435 0 0.435 france 33650454353 13/02/2008 11:34:50 00:00:36 0.505 0 0.505 france 33674254315 13/02/2008 11:43:18 00:02:27 2.061 0 2.061 france 33613381848 13/02/2008 11:49:23 00:00:24 0.336 0 0.336 france 33621302136 13/02/2008 11:50:31 00:10:07 8.51 0 8.51 "Mike H" wrote: Hi, can we see a couple of lines of your data table. Mike "Danona" wrote: I have a sheet of my phone usage. i have a cullom of the country I call,the number i phoned, the time the call took, the price I pay/ I need to fined out : 1. How much time and what % for each country. 2. How much money and what % of the cost for each country. 3. How much time and cost (real and %) for cellular an for regular phone per each country (I can tell by the number)/ How is this done? |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
analizing a sheet
Danona,
I've uploaded a small workbook here http://www.savefile.com/files/1487414 that should demonstrate the techniques Mike "Danona" wrote: 1. If you can explain to me what is the difrence between sum and sumproduct. 2. my problem is that my sheet does not have each country separetly but has mixed countries ,example france france spain spain spain Venezuela france etc so how do i work with that I cant sum up collums. "Mike H" wrote: Hi, All your calculations can be done using the Sumproduct function but first you need total cost and total duration and to get those simply sum the appropriate columns =SUM(H2:H12) for the total cost (Change the range to suit) =SUM(E2:E12) for the duration. Format this cell with [h]:mm:ss Now to get the total cost for france (Column H is call cost) =SUMPRODUCT((A2:A12="France")*(H2:H12)) Duration for france =SUMPRODUCT((A2:A12="France")*(E2:E12)) To get the percentages for each then use thi =(SUMPRODUCT((A2:A12="France")*(F2:F12)))/SUM(F2:F12) I can't help on mobiles because I don't understande which are mobile numbers Mike "Danona" wrote: france 33625290482 13/02/2008 10:38:43 00:00:26 0.365 0 0.365 france 33143748795 13/02/2008 10:57:36 00:10:37 2.643 1.777 0.866 france 33626121615 13/02/2008 11:20:00 00:00:34 0.477 0 0.477 france 33650198179 13/02/2008 11:21:31 00:00:37 0.519 0 0.519 france 33623581321 13/02/2008 11:24:19 00:00:36 0.505 0 0.505 france 33650364551 13/02/2008 11:27:22 00:02:49 2.369 0 2.369 france 33621207891 13/02/2008 11:33:22 00:00:31 0.435 0 0.435 france 33650454353 13/02/2008 11:34:50 00:00:36 0.505 0 0.505 france 33674254315 13/02/2008 11:43:18 00:02:27 2.061 0 2.061 france 33613381848 13/02/2008 11:49:23 00:00:24 0.336 0 0.336 france 33621302136 13/02/2008 11:50:31 00:10:07 8.51 0 8.51 "Mike H" wrote: Hi, can we see a couple of lines of your data table. Mike "Danona" wrote: I have a sheet of my phone usage. i have a cullom of the country I call,the number i phoned, the time the call took, the price I pay/ I need to fined out : 1. How much time and what % for each country. 2. How much money and what % of the cost for each country. 3. How much time and cost (real and %) for cellular an for regular phone per each country (I can tell by the number)/ How is this done? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "='sheet 1'!D4" auto fill sheet to sheet ='sheet 2'!D4 | Excel Worksheet Functions | |||
| Formula for Analizing Grades | Excel Worksheet Functions | |||
| How do I select price from sheet.b where sheet.a part no = sheet.b | Excel Worksheet Functions | |||
| where are Analizing Data in Excel 2002 | New Users to Excel | |||
| relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
Hybrid Mode
