analizing a sheet

[Danona]

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?

 

[Mike H]

Hi,

can we see a couple of lines of your data table.

Mike

 

[Danona]

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]

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]

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.

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

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]

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

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.

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

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

:

Hi,

can we see a couple of lines of your data table.

Mike

:

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?

 

[Mike H]

Danona,

I've uploaded a small workbook here
http://www.savefile.com/files/1487414
that should demonstrate the techniques

Mike

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.

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

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

:

Hi,

can we see a couple of lines of your data table.

Mike

:

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?