Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate per minute cost of cell phone usage
I have a spreadsheet that I’m trying to use to calculate the cost per
minute of cellular calls. I have 3 plans (in rows 3, 4, and 5) that each provide a monthly cost (column a), and the number of minutes included in that cost (column b). There is also a cost per minute if you go over that allowance (cell E2). I have a field (cell C7) that I use to enter the average number of minutes used. I have a drop down list (cell C8) that allows you to select the plan you have. I have a formula that is currently written as: =SUM(((C7-C8)*E2)+A5)/C7 whe C7 = average number of minutes used C8 = drop down list to select the number of minutes included in the plan (list points to B3, B4, or B5) A5 = cost of the plan This is written so that if someone uses more minutes than they’re allowed, it multiplies the number of minutes over what was allowed by the cost if you go over (cell E2), adds that to the monthly cost, then divides the total cost by the total number of minutes used. My problem is that this is not bulletproof in two ways: 1) The cost of the plan is a variable based on the selection in C8. So if someone selects the plan defined in B5, then the cost in cell A5 should be called. If the plan defined in B4 is selected, then the cost in cell A4 should be called. If the plan defined in B3 is selected, then the cost in cell A3 should be called. 2) If the average number of minutes used is less than the included minutes, then the formula should simply divide the cost by the number of minutes. Can someone help me re-write my formula to bulletproof this? I’m sure “IF” and “ELSE” is going to be involved, but I don’t have that level of expertise. TIA, Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Cost Per Minute | Excel Worksheet Functions | |||
how to - phone number - usage and percentage | Excel Discussion (Misc queries) | |||
tracking cell phone usage | Excel Discussion (Misc queries) | |||
how to calculate an rate per minute | Excel Discussion (Misc queries) | |||
how to calculate an rate per minute | Excel Discussion (Misc queries) |