Manipulate Spreadsheet data (cell phone bill)

Discussion in 'Computer Support' started by ramcneilly@gmail.com, Apr 2, 2008.

  1. Guest

    Hi guys,

    I have been trying to solve a problem here without much success. I am
    reviewing my cell phone bill, which is in excel format, and trying to
    do some analysis. I am able to open the bill in Calc and setup some
    filters but I would also like to do some calculations on the filtered
    data. The problem is that the columns that I am interested in summing
    up are text columns. The duration columns is of the format hh:mm:ss
    eg of actual data
    Code:
    '00:10:34
    and the Charge column $0.50
    eg of actual data
    Code:
    '$0.50
    .

    Notice the apostrophe before the data.

    Can someone please give me some tips as to how I can sum the data in
    these columns after I have filtered the rows I want.

    Richard

    I am currently using OpenOffice, if anyone has a solution with Excel
    that also be welcome.
    , Apr 2, 2008
    #1
    1. Advertising

  2. Andy Guest

    =(VALUE(MID(C4,7,2))/60+VALUE(MID(C4,4,2))+VALUE(MID(C4,1,2))*60)*(VALUE(MID(D4,2,6)))

    where c4 is cell for time and d4 is cell for rate, (this includes an
    allowance for d4 to go to more decimal places)

    --
    Andy Wilson


    <> wrote in message
    news:...
    > Hi guys,
    >
    > I have been trying to solve a problem here without much success. I am
    > reviewing my cell phone bill, which is in excel format, and trying to
    > do some analysis. I am able to open the bill in Calc and setup some
    > filters but I would also like to do some calculations on the filtered
    > data. The problem is that the columns that I am interested in summing
    > up are text columns. The duration columns is of the format hh:mm:ss
    > eg of actual data
    Code:
    '00:10:34
    and the Charge column $0.50
    > eg of actual data
    Code:
    '$0.50
    .
    >
    > Notice the apostrophe before the data.
    >
    > Can someone please give me some tips as to how I can sum the data in
    > these columns after I have filtered the rows I want.
    >
    > Richard
    >
    > I am currently using OpenOffice, if anyone has a solution with Excel
    > that also be welcome.
    Andy, Apr 2, 2008
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Dave

    Manipulate audio from VCR in computer?

    Dave, Aug 11, 2005, in forum: Computer Support
    Replies:
    5
    Views:
    498
    SgtMinor
    Aug 11, 2005
  2. Replies:
    3
    Views:
    290
  3. pavan prabhat
    Replies:
    0
    Views:
    511
    pavan prabhat
    May 24, 2007
  4. billo
    Replies:
    13
    Views:
    541
    billo
    Nov 22, 2008
  5. John Doe
    Replies:
    5
    Views:
    3,288
    John Doe
    Feb 8, 2009
Loading...

Share This Page