Velocity Reviews > Manipulate Spreadsheet data (cell phone bill)

# Manipulate Spreadsheet data (cell phone bill)

ramcneilly@gmail.com
Guest
Posts: n/a

 04-02-2008
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
Guest
Posts: n/a

 04-02-2008
=(VALUE(MID(C4,7,2))/60+VALUE(MID(C4,4,2))+VALUE(MID(C4,1,2))*60)*(VALU E(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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.