An Odd Couple of Trash Pickup Cost & Cell Phone Plan

Hi Guys! My name is Rachel and I'm soon to be a Californian. I've been trying to figure out the best way to create an excel document for two separate topics. I've looked over the different threads and you guys are incredible. I'm so thankful a site like this exists. Much blessings to all!

Okay here are my questions:

Question 1:
Since I'm moving to California, one of the costs I have to budget is my local trash service. The trash service offered in the city I'm moving to is a little complex as compared to where I live now. The new price breakdown is based on how many carts (or cans) that you rent from the city each month. Here are the prices:

Baseline fee (regardless of the amount of cans) = $15.59
Recycle Charge (regardless of the amount of cans) = $.78
Monthly Fee (regardless of the amount of cans) = $.30

Green Waste fees:
1 Can = $2.36
2 Cans = $3.39
3 Cans = $4.57
4 Cans = $5.66
5 Cans = $6.94
Every can after 5 Cans = $1.15

So the differences are:
2nd can = $1.03
3rd can = $1.18
4th can = $1.09
5th can = $1.28

additional can above 5 = $1.15

The workbook I've attached uses two columns, C & D. The "C" Column is for the first cart/can and the "D" column is for additional carts/cans. I want to combine them into 1 single column where I can change the amount of carts over say 5 years in case I need to rent more cans or less cans. Obviously the total cost has to be in Column "N" (monthly column). I just don't know how to do figure it out. Every time I add an additional cart I want the monthly total to reflect the change in price. It would be easier if each additional cart cost the same, but they don't. Does anyone have an idea on how to do this?

Here's a snapshot:
trash service.jpg

Question 2:
I included this Question because I think it's also very similar to the Trash Service question. This time it deals with a Family Cell Phone Plan. Since some of my sisters might come stay or hopefully move with me, I wanted to look into how much it would cost to have X amount of lines on a cell phone plan.

On this workbook I have a column for Taxes, Amount of Lines, 1st Line Cost, 2nd Line Cost, and a 3rd line cost which also represents any lines greater than 3, and an International Cost per line Column.

Very similar to the Trash service question I'm looking for the same result. I want to calculate the cost to add a 2nd line, third line, 4th+ lines, but the prices per line are not all the same.

1st line = $80.00
2nd line = $40.00
3rd line+ = $20.00
International plan per line = $15.00
Taxes = 16%

I would assume the basic formula for trash service would be similar, but I may be wrong. Any advice on this would be helpful too.

cell phone.jpg

The combined workbook is attached. Sheet 1 = Trash Service & Sheet 2 = Cell Phone Plan

Thank you guys for helping me, You're the best and I'm very grateful!!! If I forgot something please let me know!

Here's a possibility. It assumes you won't have more than 10 trash cans. If you do then......
Good Luck!!!

Originally Posted by skywriter

Here's a possibility. It assumes you won't have more than 10 trash cans. If you do then......
Good Luck!!!

that is seriously impressive work! is there anyway i could keep it just in the monthly column on each respective sheet without being referenced to sheet 3? And how do i expand the cell phone lines past 3? I'm looking at taking it more close to 10 or 12.

Originally Posted by RachelMads02

that is seriously impressive work! is there anyway i could keep it just in the monthly column on each respective sheet?

I don't understand your request.

The third sheet is so that you can have a clean sheet and just change the number of cans and see the results, if you want to reference all of your columns in a formula on the same sheet, it's going to be quite a messy formula to get it to work since you would have to make adjustments for which numbers to multiply etc. based on how many carts you have, whether you want international calling or not on the phones etc. The table on sheet three is much cleaner and if you there's changes in taxes or base fees etc. you change one place and everything adjusts. I thought you wanted a clean sheet where you could just change the number of trash cans or the number of phone lines and see the results. By the way you can right click on sheet three and choose hide if you don't want to see it. I left your stuff in just as a courtesy. I personally would put the phone stuff on the same sheet as the trash, get rid of all the number columns you have and do a formula to combine what I gave you to get yearly and monthly totals. Change the number of cans or phone lines etc. and you can see it all on one sheet.

Here's the shortest formula I could come up with for the trash cans.
It would go into N2 the only cell you need is C2.
The formula goes under the assumption that you have to have at least one can so it has a flat fee for that and then adds in the extra cans.
The phone formula would be more complicated because you have the options for additional lines and then the international thing.

Formula:

Please Login or Register  to view this content.

This formula can go in that cell also and will get you up to 4 additional cans.

Formula:

Please Login or Register  to view this content.

Originally Posted by skywriter

Here's the shortest formula I could come up with for the trash cans.
It would go into N2 the only cell you need is C2.
The formula goes under the assumption that you have to have at least one can so it has a flat fee for that and then adds in the extra cans.
The phone formula would be more complicated because you have the options for additional lines and then the international thing.

Formula:

Please Login or Register  to view this content.

This formula can go in that cell also and will get you up to 4 additional cans.

Formula:

Please Login or Register  to view this content.

Thank You!!

Don't forget the tax. In you original post you said it was 16%, but when I actually click into cell B2 it shows it's keyed in as 16.04%.