calculating increasing rates (03)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

Oops, sorry. I’ve corrected that – the attachment is there now.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

cheers, francis

Reply | Quote

Here are Melanie’s requirements from the first post in this thread:[indent]

---

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

---

[/indent]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.

Reply | Quote

Have you look down in col F and the comments that I have inserted?

You did a simple formula but I am not sure if that’s is correct, as it didn’t take into account
of summing the trip rate of 10 or 15. It did only give a total of 15 for 3 trip a day but 0 for charges on 3 cats
without a existing dog.

cheers, francis

Reply | Quote

Neither of your assumptions in column F correspond to Melanie’s requirements.

I have attached your workbook with my version of the formula and an explanation of its results.

Reply | Quote

Hi Hans

Thanks for doing this, but I am still not sure who get it correctly,
based on the extracts, I take it that trips are charged separately

” 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)….
on top of that, she charges per pet……….”

You have calculated the trips’ rate in as charges for dog / cats but I have calculated them differently,
I may have interpreted it incorrectly.

cheers, francis

Reply | Quote

Francis,
[indent]

---

” 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)….
on top of that, she charges per pet……….”

---

[/indent]
No. Some of the pet’s are included in the base rate, this being either (a) the first dog or ( if there are no dogs, all of the cats.

[indent]

---

I may have interpreted it incorrectly.

---

[/indent]
From my understanding, i think that is correct.

HTH.

Reply | Quote

Hi Hans and Nathan

A matter of interpreting the requirements.
We are just volunteering our time and what we know on the subject to provide assistance to others
Since there are 2 option here, if the result is not what the op want, she have the option of choosing the other.

Like Hans have said, let pass this and move forward.

cheers, francis

Reply | Quote

Let us agree to disagree and leave it at that.

Reply | Quote