Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2004
    Location
    Australia
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    New Lounger
    Join Date
    May 2004
    Location
    Australia
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    New Lounger
    Join Date
    May 2004
    Location
    Australia
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •