# 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

2. ### AndyGuest

=(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