Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    calculating increasing rates (03)

    My daughter has a pet sitting business and she wants me to help her automate her pricing. I thought it sounded easy, and maybe it is for someone else, but I'm getting a headache.

    She charges a base rate of $10 for 2 visits a day or $15 for 3 visits a day + gas (this is a variable based on actual mileage and average gas prices) .. whew - I haven't tried to tackle the mileage part yet.

    on top of that, she charges per pet
    1 dog = base rate
    2 dogs = increase base rate by $2 for every additional dog over 1 dog
    If they also have cats, she charges $2 per cat to the base rate

    If it's just cats, then it's just the base rate
    I thought I had it figured out, but my calculation for additional pets isn't working right.

    I'm attaching my worksheet so far. This is not pretty. I want to get the calculations to work and then I'd like to figure out how to make these work in an access database. But, I'm not rushing that part yet. If we have to calculate separately in Excel and then hard code the numbers in an Access field, that will be ok. My main goal is so to make it so she can enter a number of dogs, number of cats, number of trips, and mileage in one place and have the total fee calculate automatically. This spreadsheet won't hold historical data - it's just a calculator. ... unless you have some cool idea that is beyond what I'm thinking of. =)

    THANK YOU
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating increasing rates (03)

    Thank you, but I don't see an attachment, Hans.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating increasing rates (03)

    See if the attached workbook does what you want.
    The cells with a green background are the input cells, those with a blue background are the output cells. I've provided both a calculation with intermediate steps and a single formula.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating increasing rates (03)

    Oops, sorry. I've corrected that - the attachment is there now.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating increasing rates (03)

    So much more elegant than the mess I made. Thank you. I see where my IF statement was messing up too. Thank you, Hans!

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating increasing rates (03)

    Hi Melanie

    I am a little late on this, although Hans have provided a solution
    I have did a different version using If statements on some columns and a SUM with IF in the Total Charges column
    See Sheet3 of the attached if it do what you want, it take into consideration to sum other charges beside the dog and cat's fees
    as well as the potential increase in the rate for dog but not the cat.

    I was trying to use Sumproduct on the Total Charges column but is not successful.

    HTH
    cheers, francis
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating increasing rates (03)

    Franciz,

    You haven't taken the base rate into account. Melanie stated that one dog is included in the base rate, and that if there are no dogs, all cats are included in the base rate.
    There is no need for the formulas in column I to be array formulas, a simple IF would suffice.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating increasing rates (03)

    I still think your formulas don't meet Melanie's requirements:
    - There should be a base fee that includes one dog; more dogs are extra (but not at the same rate as the base fee).
    - If there are no dogs, only cats, the cats are included in the base fee.

  9. #9
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating increasing rates (03)

    Hi Hans

    Col E shows the cat's rate, if there is no dog, it will take the base rate of 10.
    I relook at the sheet and found that in actual fact, a simple sum on the cells C4,F4:H4 will do the trick in col I
    you are correct that the array formula is not require. When I first started to work on this post, the first thing
    that come to my mind for the result are SUM with IF and SUMPRODUCT. I have forgotten after working thru
    other columns and stick to the array formula.

    Thanks for the guide, I still consider myself as a novice in this.

    cheers, francis
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  10. #10
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating increasing rates (03)

    Hi Hans

    You are correct!. I didn't take into account for the additional charges of 2 if there already exist 1 dog.
    I have thought the charge for a dog is always 10 and should increase by 10 if there additional except for cat which
    I thought is a complementary service.

    Hi Melanie, apology.

    I have reworked the formulas. See if this work for you, I have include a cell in B2 for additional charge of 2 for an existing 1 dog.
    The formulas now are not hard code and take into consideration for price changes.

    cheers, francis
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating increasing rates (03)

    I'm very sorry, but you still haven't got it quite right.
    You now charge $10 for 1 dog, and $12 for any larger number of dogs. You should charge $2 extra for EACH dog.
    And if there are no dogs, the rate should be $10 regardless of the number of cats, not $10 per cat!

  12. #12
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating increasing rates (03)

    Hans,
    There's no need to apologise on this. A good lesson learned here and
    it show that you are a good teacher / mentor.

    It was not mentioned that if the charge of 10 is for any number of cats or the charge will be the same as dog,
    ie incrementing 2 per additional cat with the 1st being at the based rate of 10 if no dog exist.

    See attached, hopefully I have taken all requirements into account. Let hear from Melaine if this does what she want.
    But I found that my formula getting unwieldy, I believe that it can be improve further.
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating increasing rates (03)

    Your formulas are still not correct. Please read Melanie's post again, then look at the relatively simple solution I posted.

  14. #14
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating increasing rates (03)

    Then I have no idea where did its goes wrong, they are working according to the requirements requested for.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating increasing rates (03)

    Here are Melanie's requirements from the first post in this thread:
    <hr> She charges a base rate of $10 for 2 visits a day or $15 for 3 visits a day + gas (this is a variable based on actual mileage and average gas prices) .. whew - I haven't tried to tackle the mileage part yet.

    on top of that, she charges per pet
    1 dog = base rate
    2 dogs = increase base rate by $2 for every additional dog over 1 dog
    If they also have cats, she charges $2 per cat to the base rate

    If it's just cats, then it's just the base rate<hr>
    The last line states that if there are only cats, i.e. no dogs, the customer only pays the base rate. Your worksheet does NOT satisfy this requirement: if the number of dogs is 0, you still compute a fee that depends on the number of cats.
    Moreover, your formulas are needlessly complicated. I've shown that it can be done in a much easier way.

Page 1 of 2 12 LastLast

Posting Permissions

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