Column A = persons name
Column B = number of hours
Column C = trade
Column D = shift (as in day, night or weekend)
Column E = I would like the formula that looks at the trade and time and then puts the correct rate.
I have a little table on the site with trades/times and rates. IF and IF THEN do something
I took a screenshot to show: [URL] ..........
I have a spreadsheet, but I need the following formula to make our lives easier here.
I have the following columns:
Rate, I/T/P/S, FT/PT
I need a formula that when I enter "I" for Infant it will give me the FT (full time) or PT (part time) rate. All the columns have drop down menus, so if I choose "I" under the ITPS column and then choose FT, I want the formula to be able to give me the full time rate which is 157.94
How can I correct this Time formula which says in the Tag that it produces a
negative Result and hence displays in cell as ####
=(INDEX($A18:$A33,MATCH(MIN(C18:C33),C18:C33,0)))-(60/1440)
What I am doing is selecting the field in A which equates to the lowest
value in C (C= hourly sales), then I am subtracting 60 mins from the value
selected in A. This in effect will give me my closing Times i.e. find the
cell with Zero Sales then subtract 1 hour to find what must be the closing
time (assuming of course that there is at least ?1 of sales per hour while
open).
My cells in A are formatted as h:mm AM/PM, as is the format in the formula
cell
How would the formula look that pulls/puts the right number in E19 and F19 respectively, taking input from the list above?
View 2 Replies View RelatedI'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
View 4 Replies View RelatedDates in Column A
Currency in Column B (expressed as EUR, CHF or GBP)
Amount in Column C
And I'm trying to get the USD equivalent in column D based on the exchange rate at the time.
In Sheet 2 I have a table of the historical exchange rates like this:
DateEURGBPCHF
08/05/20060.786270.538151.2277
09/05/20060.784470.536751.2224
10/05/20060.781360.536271.2185
11/05/20060.778050.530761.2108
12/05/20060.775880.528811.2019
15/05/20060.779780.530911.2089
I am trying to calculate earnings based upon hours worked based on 24 hours format (time out - time in = time worked) multiplied by hourly rate. I cannot get the proper calculation. How can I attach a copy of the Excel worksheet?
View 9 Replies View RelatedI have a row of cells, and some of which are displaying time, but in an incorrect format.
for example 15:20 is showing in a cell as 3.2
How can I create a new cells that basically converts 3.2 into 24 hr hh:mm ....e.g. 15:20?
If I got to use the format cells option, and hh:mm, it changes the cell to the completely wrong time (i.e 04:48) ?
p.s. I should state that "some" of the cells are kind of showing the correct time, albeit with a decimal instead of a colon, for example, cell A12 is showing 4.25
one other thing I should mention.....the times will always be PM...... so 3.2 should be 15:20, and not 03:20
The formula I am looking for would tell me what annual growth rate % I would need to achieve to make any investment reach a set target, for instance, what % of fixed annual growth would I need to make 200K grow to 750k in say 10 yrs or any time scale. I was given the formula below but Excel tells me it's wrong, I have tried putting 10 before ^ and the 10 after but to no avail, could some kind soul please put me straight.
r = 100((Y/X)^(1/n))-1)
So for X = 200, Y = 750, n = 10, we have
r = 100((750/200)^(0.1))-1) = 14.1309%
I did post a problem where I have a number like 123456 and I need to have Excel change it so that it puts a comma before the last two digits .. like so: 1234,56
I got a reply where I got the solution to use 0","00 and this works in Excel (using the custom format)
The only problem is that although the number changes in the cell to 1234,56 it doesn´t do so in the FX window and thus when I use the number to multiply it is actually 123456 instead of 1234,56 like it want it to be.
I have a file which ask for UserId and password. If password is correct, the time stamp should be recorded on the worksheet. However I have some slight probelms on the code I am working on. can someone help me check my coding?
View 14 Replies View RelatedI have a spreadsheet where I often have to exchange pairs of cells. I currently do this by dragging Cell A to a blank portion of the sheet, dragging Cell B to Cell A's former location, then dragging Cell A to Cell B's former location. I was just thinking that I might be able to use VBA to allow me to drag Cell A to Cell B and have them "trade places".
I don't even know what events are involved in the dragging process so I don't know where to start.
I would like to know the formula used in excel for rate function. Anybody knows and can share with me ? as i need to use that in other programmining language where these financial functions are not available.
View 9 Replies View RelatedI have done a VBA course but am struggling to apply it to my situation. What I want to do is on one spreadsheet, I have a column with "shift, date, grade 1, grade 3, quarantine" in it, and in the cells next to it I want an operator to enter either "day" or "night" next to shift, and the date. Next to the others I want the total weight of the appropriate product made in that time. The spreadsheet I will be feeding off is quite big with a lot of information in it. Down the left hand side is the roll number (we make rollstock). Further along on that same row will be the date, the weight, and a grading (putting it into grade 1, 3, quarantine etc). Our shifts run from 7am to 6:59pm, and 7pm to 6:59am. Which shift the roll comes under is determined by it's end time (also in that row). So basically, I need VBA to say sum up all of column [weights] if column [date] = [date cell in working spreadsheet] and if working spreadsheet says day then column [roll end time] =7:00-18:59 and if working spreadsheet says night then column [roll end time] =19:00-6:59 and also if column [grade] = [grade cell in working spreadsheet] ....
Oh, also on the feed spreadsheet i have a column that i put in a little code date, "AP04" for 4th of april kind of thing, rolls being made before 6:59am on the 5th of april being put into the 5th april as they need to be counted under 4th april as they are 4th april night shift (even though the date will stay 5th april)...
The attached spreadsheet should explain my issue. In a nutshell, the value on line 6 is from the previous day's data (i.e Tuesdays line 6 is based on Monday's data). If the previous day has no data ( i.e. market closed), line 6 should reflect the last line 6 calculation.
Also, i would like line 6 to be zero past 2 calendar days from today. i.e. Friday June 6 should read zero until tomorrow (june5) data is entered.
Pivot.xlsx
FromToGr Rate
90100A14.0
8689A23.6
8085B13.2
7279B23.0
6471B32.8
5663C12.6
This is part of a marks to grade and rating table. I have a list of students and the marks they obtained in an exam. I need a formula to put the grade and rate beside each name e.g. John; 92 marks; A1; 4.
Looking for a formula that would calculate flow rate (GPM) based of ID of pipe & PSI.
Example - 3/4 inch ID pipe at 70 psi = GPM
B. start time C. end time D. Total time (military time)
Example: 2300 - 0300 = 4 hrs worked.
=IF(C19-B19>0,C19-B19,1+C19-B19)
I have a row for every date the hors is worked.
I cant seem to get the total hours to calculate correctly at the bottom of column D for a total hours worked in a particular pay period. It calculates a total up to 24hrs then reverts to zero. I have tried [h]:mm =SUM(XX:XX) and just about everything else I can think of and cant get it to calculate a correct total over 24 hrs.
I have a number of equity trades (both purchase and sales). I need to know the book cost of those sale trades to figure out the realized gain/loss according to First-in-first-out method.
Since the unit sold may included units bought at different time and different price, so I have to first exhaust the first lot that I purchase before moving into second lot, and so on and so for.
Column G - J are for illustration. I highlighted K10, K11 and K12 are book cost that I want to calculate. Instead of manually separating out units in each lot previously bought. Is there an automatic way? I would not mind adding new columns to ease calculation.
I am trying to calculate a new base pay rate, but I need it make sure it is at least brought up the new minimum and capped at the new max of the range where applicable. The increase is based on 10%
So here are the columns used:
L = Current Base Pay
Q = New Min of the range
S = New Max of the range
U = where I want to calculate a 10% increase of L, but ensuring it is brought up to at least the min (Q) or not over Max (S). In other words if my min is $12 and Max $18 and my new base pay is $16 -- then I am good. However it if is $11.50 I need the formula to return at least $12. And visa versa -- if the new rate would be $18.50, I need it to return no more than $18.
I am trying to calculate the effective annual interest rate earned on an investment and find the results are close but not really accurate. I suspect because I have not included the frequency of interest in my existing formula
r = n * nt root (A/P-1)
where;
r = the effective interest rate
n = the number of times interest is added per year
t = the total number of years
A = the current value
P = the original value
The 2 problems I face are;
1. Confirming this formula would provide the correct answer (need maths expert here) &
2. How would "nt root" (as in sqr root, but using the product of the years and frequency) be used in Excel
I am trying (and failing miserably) to create a fomula to 'score' my pickers in a warehouse environment.
The basic variables are;
1) Units (Total quantity of units on picklist)
2) Lines (Number of different products on picklist)
3) Time (Total time to complete pick)
To give an example, if Units = 8, Lines = 1 and Time = 00:01, I want 'Score' to = 100.
(Based on benchmark, rounded down to lowest common denominators)
The added complication I face is the relationship between Lines and Units; it would take someone longer to pick 1 unit from 10 lines than 10 units from 1 line.
I would like to a formula that will provide a weighted average rate (yellow cells) for a provided volume level, given the tiers on the left. I have already performed this the "dumb" way with lots of IF statements to figure the volume by tier and then taking a weighted average of the products with the rates by tier, but there's got to be a more efficient way. My file is huge! ....
View 9 Replies View RelatedFrom a chart in Excel I need to automatically calculate what the annual percentage growth rate is of a trend line. How to automate this in Excel? I've attached a sample so you can see what I'm trying to accomplish.
View 6 Replies View RelatedI have a macro that takes the column of data from D on Sheet 1, pastes it into A on Sheet 3, gets rid of duplicates and then, in B1, combines all the numbers from A into a comma-separated string. The only problem is that it puts a comma at the beginning of the string. Is there something I can do to make that first comma not be there?
View 5 Replies View RelatedI need to know if its possible, and if it is, what the formula would be to get the correct "Rate" to pull based on the criteria given:
User would input the following information:
Zip Code: 56559
Pallets: 3
Weight: 1200
The formula needs to use the following table to use the criteria listed above, to fine the correct "Rate". The "Zip Code" and "Weight" both need to fall between the correct ranges and then match the "Pallet" to find the correct "Rate".
From Zip Code
To Zip Code
Pallets
Weight From
Weight To
[Code]......
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).
I have been trying to work on this excel worksheet for a while. I have give the charge column (tab:data) and the rate chart (matrix type with weight and zone). I want to find the formula that would tell me if the charge is in the rate chart (I know it would use countif), and tell me what the zone and the weight associate with it. Using
=CELL("address",Index()), but it won't work.
Basically I have two sets of data. One will be new each week. I'd like to use the non-changing data as a base to compare new data to. The formula would need to match multiple values, including a 'time between', and then return whether a minimum rate has been met.
SampleRateExamine.xlsx‎
I am trying to create a formula that will allow me to enter a zip code and have excel return the specific tax rate for that zip code. I have zip codes in excel for all of California along with the corresponding tax rates. I am creating a form that I can just input the zip code and have the tax rate automatically pop in.
View 1 Replies View Related