Calculating Charges Based On Stepped Rates?
Jun 20, 2014
Never tried complicated formulas in Access and at a bit of a loss... What I am trying to do is calculate a utility bill based on stepped rated.
For example:
Usage up to the first 500KHW is billed at .067 per KWH
Usage after the first 500KWH from 501 to 999 is billed at .044 per KWH
Usage from 1000 up is billed at .0318
So if my usage was 1200 KWH...
((500 x .067)+(500 x .044)+(200 x .0318)) = 61.86
I was assuming it would require an complex "if" function to split the 1200 into steps and then calculate charges per step?
View 9 Replies
ADVERTISEMENT
Jul 20, 2006
I have three columns. The first column contains a certain category ( i.e. Cat 1, Cat 2, ect.), and the second and third columns contain numbers referring to that category:
Ex:
A1 B1 C1
Cat 1 6 5
I want to be able to find the success rate (C1/B1), but I want Excel to recognize what category it belongs too so I can split the success rates into categories in a different location. What function, if there is one, do I use, and what information do I need to plug in.
View 8 Replies
View Related
Oct 14, 2013
I am trying to create a booking calculation sheet for facilities. I want it to create something like a quote for customers. So when booking facilities, the hourly rate changes after 6pm. What I want to do is to put in a start time and a finish time and it calculate how many hours are before 6pm and charge them at $12 per hour, as well as how many hours after 6pm and charge them $18 per hour. I have tried a few things but they don't seem to work. I'm struggling with the logic of it really. This is mainly because the start time may or may not be before 6pm, as too the finish time.
View 6 Replies
View Related
Feb 28, 2008
I have US money supply data, arranged monthly from 1975-2008. I need to calculate the monthly growth rates. I would really appreciate some help as I have no clue how to do this.
here is a link to a text version of the data I am using: [url]
View 9 Replies
View Related
Aug 15, 2007
formula is needed to get excel to calculate all A rates seperate from B rates and C rates example 10 A rates @ 50.00 9 B rates @ 40.00 and 6 C rates @ 30.00 so although the rates could be mixed up (not following in any particular order the result which I would like to appear on a separate spreadsheet would be A = 500.00 B = 405.00 C = 180.00 then to get them to total up = £1085.00
A 50.00
B 40.00 then separate sheet with answer a 100.00 b 40.00 c 60.00
A 50.00
C 30.00
C 30.00
View 9 Replies
View Related
Dec 13, 2013
I want to create an Excel sheet that calculates the charges of a freight based on predefined parameters.
I got 3 service types as shown below, two freight types and 12 different destinations both NT and SA have 6 different destinations:
I want Excel to do the following:
If Road and Carton and to NT-Country and weight is 7 then 7*1.000089 is the result
1 to 15
>15
Service
Type
NT
METRO
0.337284
0.337284
[Code] .......
View 7 Replies
View Related
Apr 4, 2014
how to match a series of rates for a destination city depending upon the origin city. I have figured out how to match rates from origin city to multiple destination cities but have not figured out how to change the series of rates when the origin city changes. These rates will be calculated on the "calculator" in the excel document depending on the cities chosen.
I have attached a document that shows a simplified version of what I am trying to do.
View 2 Replies
View Related
May 29, 2014
What I am trying to do is to create a formula for the attached spreadsheet - that calculates the daily compounding interest based on the higher rate of the two rates for the first five years then after 5 years the calculation should only be based solely on the blocked rate.
View 5 Replies
View Related
Jun 10, 2014
Looking for a formula for total cost based on a list of hours for each employee and a table of rates for each employee.
Please see attached file : Formula.xlsx‎
View 1 Replies
View Related
Mar 2, 2007
I have coins in the following denominations: 10 cents and 20 cents
IF my parking charge is 30 cents, I can pay the charge using:
a: 10 + 10 + 10 cents
b: 10 + 20 cents
c: 20 + 10 cents
= 3 combinations (a, b, c)
= 2 unique (a, b) or (a, c)
If my parking charge is 80 cents
What is the formula to use to find:
(i) The combinations (answer should be 34(?) posible combinations)
(ii) The unique combinations
whilst if the parking charge was 50 cents
Than there would be 8 possible combinations (as the answer to the formula!)
View 9 Replies
View Related
Feb 25, 2009
what i trying to learn is that im trying to get the delivery price calculate base on the delivery area(F10) and the total qty of the items(G10).
but i've tried with Vlookup, IF, lookup, Hlookup function and i still cant manage to get the right one to put the data in the H10
View 4 Replies
View Related
Mar 3, 2009
If I ask anyone in my office they can easily tell me what the next line should be in this list. I can't figure out how to do it in Excel
=A5
=A10
=A15
?
So, obviously the cells are referencing column A and increasing by 5. That's it, nothing fancy. I have 5,000 cells to fill and it's increasing by 13 instead of 5 so I can't just do it by hand. If I highlight the first 3 and then drag down the next cell would say A8. Not even sure how they get that.
View 9 Replies
View Related
Jan 22, 2014
scenario testing and coming up with a simple "multiply by" that I can drag across a table.
View 5 Replies
View Related
Apr 11, 2008
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
examples:
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
View 8 Replies
View Related
Apr 23, 2008
The phone call is charged at 0.57 for the first 60 seconds, thereafter 0.0095 per second. Determine cost if duration (in seconds) is available?
AND
The phone caal is charged at 1.654 for the first 60 seconds, thereafter 0.827 for every 30 seconds.Determine cost if duration (in seconds) is available?
View 7 Replies
View Related
Feb 4, 2014
I have a base rate in A1, the the units in B1 and need a total in C1.
In A3 i have discount rate (%) for units between 0 and 9
In B3 the have the discount rate (%) for units from 9 to 15
In C3 the have the discount rate (%) for units from 9 to 15
In D3 the have the discount rate (%) for units above 16.
How would the formula looklike?
View 3 Replies
View Related
Jun 24, 2008
i have a bunch ofdaily rates back from 2005. sometimes ill have one or even 2 or 3 missing rates in a row. when there is a blank rate, i just want excel to calculate the average of the date below and the date after. right now, ive just been going manually to each missing date's rate and calculating the average.
View 9 Replies
View Related
Mar 27, 2007
I am trying to create a conditional formula. I have multiple workers that work for different $$ per hour. I am keeping the hourly rates on one page for security purposes (the person that is going to be updating the hours worked is not very computer savy and they less they have to type the better) and the hours on another... I want copy a formula down the page that will calculate the hours to the hourly rate. The issue is that i want the formula to look at all of the hourly rates (assume they get raises) for Joe and multiply the most recent hourly rate times the hours... in addition I don't want the formula to re-calculate when a new hourly rate is put into the hours sheet.
Example
Jan. 1 joe works 8hours at $10 per hour formula in A1 (or wherever) calculates $80
Jan. 2 joe gets $1 raise
Jan. 2 Joe works 8 hours at $11 per hour formual in A2 calculates $88 but formula in A1 maintains the $80 and does not calculate the $1 raise.
View 12 Replies
View Related
Oct 25, 2011
There is a database of rates, stored in an excel table. There are dates (in the format MMYY) and corresponding currencies (100 in total)
I would like to have a custom function where I could type
=RATELOOKUP("EUR","0911")
And the corresponding rate will appear
The excel file with the rates is stored read only in a locked folder on the network so the format and layout will not be affected
How could I macro this? - then turn the result into an addin so that everybody can take advantage of this.
View 9 Replies
View Related
Dec 7, 2006
I know that Excel has a function to import currnecy rates from the MSN Money web site. But MSN Money doesn't support all currencies. I would like to build an excel sheet where a user can maintain the currency codes and the currency rate is being fetched from the internet. Does anybody have a suggestion from where I could get currency rates into Excel?
View 9 Replies
View Related
Apr 27, 2004
I am trying to write what I believe is a fairly simple set of calculations to determine non-qualified stock option values. Before this digresses into a discussion of the merits of Black-Sholes methodology, I must tell you that this is a "101" type sheet. I'm not an economist.
The deal is that my sister (for whom I'm constructing this worksheet), gets employer stock options which vest at different intervals.
In the past year, she's gotten some options that vest in two, four, and five year increments. I'm using Excel 2002, with the analysis toolpak, and stock pricing add-in from MS.
Have today's date, date of option grant, and can easily compute time (in years) until options are fully vested.
The trick is that options with a 2-year vesting period are 50% excerciseable in one year, 100% in two.
Four-year options are excerciseable 25% per year for 4 years; 5-year are 20% for 5 years, etc.
I'm trying to write a single date-driven formula to take each grant (individually), and divide it by the appropriate divisor (based on the current date), to figure out the current value.
I can't seem to quite figure it out how to write a formula which can handle:
Shares Strike Price Date of Grant Years Until Vested Value
100 26.57 1/4/2003 5
245 45.67 2/6/2001 4
Value should equal =
(if today's price is grater than Strike Price)
(Current Price * (Shares * (% vested which is based on "date of grant" + "years until vested")) )
-
(Strike Price * Shares * (% vested which is based on "date of grant" + "years until vested") )
how the math to make the "% vested multiplier" work...
View 9 Replies
View Related
May 16, 2014
Formula to calculate a daily compound interest based on the higher rate of the two rates for the first 5 years, then after 5 years the calculation would only be based solely on the blocked rate.
View 4 Replies
View Related
Oct 8, 2007
I am trying to figure out a formula to figure out how much to charge for hrs of a rental. There is an automatic $1000 charge regardless of time used. the rate chart is as follows:
$1000 + hourly charge = total
=<50 hrs= $15/hr
>50hrs but <100hrs= $6.50/hr
>100hrs=$4.10/hr
ex: so is A1= 200hrs then i want B1 to equal $1820.($1000 + (200 x 4.1))=1820.
I have an idea on how to write the formula, but i am having a little bit of trouble with it.
View 14 Replies
View Related
Sep 22, 2011
I am trying to work out a formula to calculate compounded interest rates.
I have a table that is 24 columns wide (months).
1 row that will have amounts input in to the columns (these will be different amounts)
I want to have a row along the bottom that calulates the compounded interest at a fixed interest rate on the total amount that is in the first row.
I have managed to do this using a table but surely there is a formula for this as the table can become very troublesome if the input amounts change.
Ive added an image (attached) : excel.jpg‎
View 6 Replies
View Related
Jul 23, 2008
I have a grid to determine interest rates. The are add ons to the rate that are dependent on two factors - the credit score and the Loan to Value percent or LTV, both of which are shown in terms of ranges (720-739, etc). See the screen shot below.
I have already written formulas for converting the score and LTV to the ranges as they appear on the sheet (ie, if you type in a score of 722, the formula converts it to the range of 720-739.
What I need to do is this - when the score and LTV are input and determine which set of add ons come into play, I need the sheet to take those add-ons, identified by an x next to it at the top of the page, and add them to the base interest rate, giving me the final rate. Again, see below to make this more clear.
I've thought of using VLookup, but I don' think that would be applicable here.
What's the best method, and can you give me an example of what a formula might look like?
Loan Level Pricing Adjustments
Base Rate 6.25 6.25
Credit Score 741 >740
LTV 60 740 Cash Out 0 0 0 0.25 0.375 0.375 n/a >740 Investment 1.5 1.5 1.5 2 2.5 2.5 n/a >740 2-unit 0.5 0.5 0.5 0.5 0.5 0.5 0.5 >740 3-4 unit 1 1 1 1 n/a n/a n/a
720-739 all -0.25 0 0 0 0 0 0 720-739 Cash Out 0 0.125 0.125 0.375 0.5 0.5 n/a 720-739 Investment 1.5 1.5 1.5 2 2.25 2.5 n/a 720-739 2-unit 0.5 0.5 0.5 0.5 0.5 0.5 0.5 720-739 3-4 unit 1 1 1 1 n/a n/a n/a
View 9 Replies
View Related
Jan 7, 2009
i. I currently have a spreadsheet which is used to forecast resource cost for a project. The forecasted cost is calculated on a few factors - rate, allocation, contract start and end date, and expected days worked per month. One of the mods actually helped me out with this a few weeks ago.
I now have been told that there is a possibility that certain resource costs may change in the new year and that will need to be reflected in the sheet whilst keeping the historic information.
For example - XXX has a rate of £200 p/d, allocation is 1, working 18.83 days p/m and is working from 01/01/09 to 01/06/09. The current formula will work out his cost per month until contract end. Now say his rate will be changed to £150 p/d from the 01/03 and all other info remains the same, I need the sheet to calculate his revised cost from 01/03 onwards and not change the calculation previous to that month.
Now Ive actually managed to figure that part out myself by adding in two columns (over-ride rate and over-ride date) using a nested IF statement. The only problem is that if the new rate starts mid month then it will still calcuate the original amount for the full month and the revised amount from the next month.
Edit - Also, could someone advise as to how do I remove my old attachments as I have almost used up my allocation.
View 10 Replies
View Related
Feb 11, 2009
I'm looking for assistance with working out the following formula. I live on an apartment complex in Marbella Spain and we pay Monthly Community Fees of say $100.
We want to calculate a penalty amount for late payers of say 30% of the monthly fee due for non payers? So for instance if the monthly amount due in Cell D8 = 100 and the actual amount received in cell E8 shows 0 or then increase any figure due for the following month in Cell F8 should increase by 30% ($ 130) in the following month. and so on
I use Excel ver 2003. Trust my query is reasonably clear?
View 7 Replies
View Related
Mar 16, 2009
I have a spreadsheet which calculates the score for a particular item.
In cell M5, the answer should be 30 since the rating is 3 and weightage is 10 ( 3 X 10).
How do I automate this task via a formula ?
View 6 Replies
View Related
Jul 15, 2009
Is there a function in Excel that would calculate the 85th & 90th percentiles based on the 50th & 75th percentiles.
View 2 Replies
View Related
Feb 27, 2012
I have a series of numbers in the column AB56 going downwards and in AC56 going downwards their respective counts in a data-set.
What I would like to do is find the average of the min and max, the min and max for the average calculation are both determined by looking at the count column, isolating the max, isolating the min, provided they both have a count greater than zero then averaging both results.
eg.
0.1562818960
0.1558039690
0.1553260431
0.1548481172
0.15437019 2
0.1538922643
0.1534143384
0.1529364124
[code].....
In this calculation the MAX is 0.155326043 because it has a count greater than zero, the MIN would be 0.138120697 as it has a count greater than zero, the average of both would simply be 0.1467. i.e. Average(0.155326043,0.138120697)
View 2 Replies
View Related
