Results 1 to 15 of 21

20090101, 05:12 #1
 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

20090101, 06:08 #2
 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.

20090101, 06:16 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20090101, 06:17 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: calculating increasing rates (03)
Oops, sorry. I've corrected that  the attachment is there now.

20090101, 06:27 #5
 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!

20090101, 18:38 #6
 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, francisHope 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

20090101, 20:46 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20090102, 03:43 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20090102, 03:44 #9
 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, francisHope 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

20090102, 04:07 #10
 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, francisHope 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

20090102, 04:15 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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!

20090103, 05:01 #12
 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.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

20090103, 07:49 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20090103, 18:55 #14
 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, francisHope 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

20090103, 19:06 #15
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.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.
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>
Moreover, your formulas are needlessly complicated. I've shown that it can be done in a much easier way.