# Thread: Phone Bill comparison (Excel 2000)

1. ## Phone Bill comparison (Excel 2000)

Hello,
I'm attempting to create a formula that will allow me to compare two phone plans. For example, plan A charges a 25 cent flagfall and 15 cents per 30 seconds, while plan B charges a 20 cent flagfall and charges 1 cent a second. I intend to enter all the call times for the bill and check out the totals. I can do this with a calculator easily enough, but thought it might be an idea to broaden my excel horizons a little. Anybody care to help?

Cheers - Marco

2. ## Re: Phone Bill comparison (Excel 2000)

See if the attached workbook does what you want. I have assumed that Plan A rounds call times up to the next multiple of 30 seconds.

3. ## Re: Phone Bill comparison (Excel 2000)

Thanks HansV,

that points me in the right direction. Just a couple of questions. In the colum B+C formula you have (\$A2/Unit_B,0). What do the '\$' & ',0' do, and how could the formula be changed to accomodate minutes and seconds (ie 10:30) instead of seconds for the call period?

4. ## Re: Phone Bill comparison (Excel 2000)

The \$A means that the reference to column A is absolute, it will not be changed if you fill to the right.
In column B, I used the ROUNDUP function to calculate the number of units of 30 seconds will be charged. The 0 in the formula means that I round up to 0 decimal places, i.e. to the next whole number.

Excel stores times as fractions of 1 day. So if you have a time in the form 10:30 and want to convert it to seconds, you must multiply by the number of seconds in one day, 24 * 60 * 60 = 86400. See modified version.

5. ## Re: Phone Bill comparison (Excel 2000)

Thanks Hans!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•