Calculate All A Rates Seperate From B Rates And C Rates
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
ADVERTISEMENT
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
Aug 23, 2012
I want to calculate labour rates as follows:
06:00 to 18:00 std rate $10
18:00 to 06:00 night shift rate $15
I want to be able to type in the hours worked e.g. 11:00 to 19:00 and would expect 7hrs at $10 and 1 hr at $15.
View 1 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
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
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
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
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
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
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
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
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
View Related
Jun 24, 2014
I have to create VBA according to these criterias. I have a list of 22 rates that i need to distribute by order for a month period (30 days) noting that I need to skip weekends (Friday and Saturday)
Day 123.06.2014Monday20%
Day 224.06.2014Tuesday10%
Day 325.06.2014Wednesday7%
[Code]....
create this formula so that each time I change the date the rates are distributes accordingly
View 4 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
Mar 23, 2014
We have agreed maximum rates with suppliers for certain services and I'd like to check that the rates they have invoiced fall below the agreed maximum.
I've attached an example of the data I'm working with. What I'm after is a formula that will cross check the details and rate charged on the 'Invoiced' tab against the three 'rate card' tabs and generate an output that flags any discrepancies. I've added a column called 'Validate' on the 'Invoiced' tab where I'd like this formula to go.
All the data should match with the exception of the shift which is listed as a description on the rate card but is a concatenation of the job role and an abbreviation of the shift (D = Days, N = Nights/ Saturday and O = Sunday/ Bank Hols).
View 8 Replies
View Related
Sep 25, 2013
I am trying to create a a file that will serve as time log and dashboard report for consultants who work a maximum of two hours a day, with a strict(fixed) calendar schedule with multiple sessions. Some consultants may work one hour of a particular schedule on category A(Lead) and the second hour as category 2. How to create a sheet that sums the rates for all the categories worked on a given day without using a macro. Here is my sample data
Pay Categories:
Lead
Assistant
Admin
Pay Rates
Lead-$75/hr
Assistant - $40/hr
Admin - $20/hr
Sessions and Session Dates sample:
Session 1 - 10/8/2013 to 11/8/2013
Session 2 - 11/15/2013 to 12/15/2013
Session 3 - 01/06/2014 to 03/15/2014
*Note the dates will or should be listed in separate columns in order for them to be used for daily logging.
Employees (Sample)
Jane Doe
Jack Doe
John Doe
Desire goals of the report:
1. Daily log as employees work e.g if Jane Doe works on 10/8/2013 as a Lead and Assistant, ideally the data entry person should be able to enter these two values on one cell or pick the relevant value from a set list
2. The report would then match the value of the data in the cell for Jane Doe on 10/8/2013 with a table array with categories in one column and the pay rate in another, and return the appropriate pay value which can either be a sum of the two rates or the total hard coded into the cell.
3.Be able to create a dashboard report that would sum the totals by employee, month,sessions etc in a separate sheet vs other criteria such a budget etc.
View 1 Replies
View Related
Nov 28, 2013
I have two different columns of currency time series data with dates that do not align. One trades Monday-Friday, the other seven days a week.
I want to either a) add dates to the shorter series that only trades Monday - Friday (and just use the closing price on Friday for the Saturday and Sunday dates)
b) (not as good an option, as I will lose data) is delete the Saturday and Sunday data from the longer series that trades on the weekend.
View 1 Replies
View Related
Jan 25, 2007
My task is to somehow access [url] and copy the exchange rates on the table to my Excel workbook. Obviously a macro will not record outside of the Excel program, so I am just wondering if this is possible at all (without hacking of course). My plan B is to have the user manually enter the exchange rates.
View 3 Replies
View Related
Dec 29, 2013
I have a problem calculating a total interest for an input period that has different interest rates. You can see the attached file: INTEREST.xls
I have used a function but the result comes out wrong. Can I fine tune it or do I have to change my method of calculating.
View 10 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
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
Jul 23, 2009
Attached file where i m not able to split data in seperate seperate coloumn
View 3 Replies
View Related
Dec 9, 2009
The first one, I do i remove #DIV/0! from cell O7 in the worksheet "My overview"?
The second one, I need to get averages of the QA score for the 3 months from January to March in the worksheet "My Overview" Cell E14 for the corresponding person on each sheet. I understand that there need to be values in place to get an average but is there a way to overcome this?
View 2 Replies
View Related
Oct 8, 2008
I want to do a simple Sum if. My range is simple and so is my criteria, but the actual range of cells to sum is not in one continus row or column. Can I do the sumrange as seperate cells?
View 3 Replies
View Related
Oct 21, 2006
I have 4 worksheets where the structure is exactly the same except the figures differ
The worksheet have text and values in columns A to S. I need to write VBA code that will combine the data and show the descritions in a consolidated woorksheet.
View 9 Replies
View Related
Apr 27, 2007
I have the following list of names, phone and radio numbers that somebody put together all in one cell. Formated as General with Word Wrap.is.
View 9 Replies
View Related
Aug 14, 2008
Is there a way to put vba code to a standard alone exe file? This way, I don't have to keep update the code on different excel files.
View 9 Replies
View Related
