In the enclosed SS there are several columns, "A" being Style and "G" being Available. I'm trying to piece together some code that will remove all of a style(all sizes) if the number of stock is less than say 3; however, if any one style has 3 or more available the entire style needs to remain in the spreadsheet.
I've been trying to piece together some code but I don't think I've quite come up with what I'm looking for.
I have a spreadsheet with early retirement factors;
Years early
1 93.5%
2 87.6%
3 82.3%
4 77.4%
What I'd like to know is if there is an easy way to work out the factors for 1 yrs 1 mth early, 1 yrs 2 mths early, 1 yrs 3 mths early, etc.
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.
I need a way to calculate building rates based selecting the building and the value selected;
I have a list of building construction (fire resistive - protected, non combustible - protected etc)... in cell N8 and the limits in cell O8.
Now what I need to do is calculate the rates in the file attached (cells A5:J21 in my worksheet) based on the building construction and the value/limit I select.
I am working with data and I am trying to identify people that have moved departments using the following criteria, a person are classed as a mover if their:-
Manager AND Dept ID have changed
OR Job Code has changed
OR Status has changed
I have been trying to come up with a formula but just can't get one to work?
I have a sheet with 5000+ entries, column A is an ID number, column B has one of 4 outcomes C,D and E are just times and quantities and are not important.
In column A there could be multiple entries of the same ID number.
In column B it will show Pcid243A, Pcid243B, Ddar400A or Ddar400B.
I need to export (to another sheet) any entries with the following conditions:
A = ID number with any instance in B showing both a 243 and 400 condition.
E.G
A B C D E
AA565677878D - Pcid243B - C - D - E
AA837632823G - Pcid243B - C - D - E
AA837632823G - Pcid243A - C - D - E
YA74846CC - Ddar400A - C - D - E
WW768765364F - Ddar400A - C - D - E
WW768765364F - Ddar400B - C - D - E
WW768765364F - Pcid243B - C - D - E
AA73262362G - Ddar400B - C - D - E
In the example above, I'd need the red cells exporting to a new sheet as they meet the conditions of having the same ID and both a 243 & 400 outcome:
A B C D E
AA565677878D - Pcid243B - C - D - E
AA837632823G - Pcid243B - C - D - E
AA837632823G - Pcid243A - C - D - E
YA74846CC - Ddar400A - C - D - E
WW768765364F - Ddar400A - C - D - E
WW768765364F - Ddar400B - C - D - E
WW768765364F - Pcid243B - C - D - E
AA73262362G - Ddar400B - C - D - E
Now I've spent ages trying different things and can't get anything to work...
I'm hoping to achieve the result in the image below.
Column A contains data, column B contains values to be added to column A and column C contains values to be removed from column A. Using formulas or macros, how can I present the result in column D?
editing a datafile. I have a spreadsheet (Sheet2) that looks like this (Start Date is entered in J2 in Sheet 1 and End Date in J3 in Sheet1):
Date & TimePatient IDResultLevel
13/02/2014 14:1818.5>=4 AND <=15
13/02/2014 14:1027.8>=4 AND <=15
13/02/2014 02:231122.5>20
13/02/2014 06:191116>15 AND <=20
13/02/2014 10:081110.2>=4 AND <=15
13/02/2014 14:111113.4>=4 AND <=15
13/02/2014 17:541112.9>=4 AND <=15
[code]....
I need to find the maximum and minimum value for each subject. If the min>4 and max<10 and the subject does not have a value on either the last date (J3 in Sheet 1) or the date before that (J2 in Sheet 1 - 1), I need all of their data removed from the spreadsheet.
I have the data below. I need to total the amount in the USD Equivalent column but there is an exception. Where the "Curr Sold" and the "Curr Sold" are the same in reverse (as highlighted) in yellow, I need to take the net figure. For example below, I need to add the USD Equivalent column but then minus the 6,013,072.66 because the CHF - USD is the same in reverse with the same maturity date.
Maturity DateCurr SoldAmount SoldUSD EquivalentCurr Bought
09/03/2012USD4,000,000.004,000,000.00EUR09/03/2012USD3,500,000.003,500,000.00CAD
09/03/2012USD7,535,000.007,535,000.00CHF09/03/2012CHF5,578,989.356,013,072.66USD
09/03/2012USD2,500,000.002,500,000.00EUR09/03/2012USD2,500,000.002,500,000.00CAD
09/03/2012USD5,000,000.005,000,000.00AUD
I have to make a hierarchy of my employees, taking in consideration the achievemnt of their target. I'll attach a sample to make it simple to understand. The are 2 classes of factors: Class I and Class II.
- In Class I (witch counts 75% in total results) there are:
- New deposit - count 75% from class I
- Insurance - count 10% from class I
- Investments fund - count 15% from class I
- In Class II (witch counts 25% in total results) trere are:
- Loan A - count 15% from class II, but the number have a weight of 40% in results, and volume 60%
- Loan B - count 75% from class II, but the number have a weight of 75%, while volume have 25%
- New clients - count 10% from class II
Now, I have to rank the employees taking in consideration the Deviation for all of the factors above (columns E, H, K, P, Q, V, W, Z).
I need to count, not list, all of the factors of an integer. For instance the number 12 has the following, 1 2 3 4 6 12, so the solution to the NoF(12)=6.
View 9 Replies View RelatedI have a big list with names, money spent and date. I need somehow with a formula to do:
Take all the money spent on some specific day from a specific person an put it into a field.. and this for every date of the week and for everyname... Im gonna upload a photo.
Name
Money
Date
Daniel
5
01.03.2014
[Code] ........
I need the list of the left to produce me list of the right somehow, at least just the sum of everything spent everyday.
I am making an Excel calculation system where I have two factors (weight and opening length), which is essential for which lift there is possible to use.
There are 8 models each of which may have some limitations in terms of weight, and the opening length:
ModelPushing/pulling Force (KG)Stroke Length (mm)
HCV 130 350
HCV 230 600
HCV 330 800
HCV 430 1000
HCV 550 350
HCV 650 600
HCV 750 800
HCV 850 1000
What I am looking for is a formula, that when you enter numbers in two cells "Weight" and "Opening distance" of what their platform is to meet, then all the models that have the potential to accomplish this task pops up.
Example: A client writing a weight of = 40 kg and opening length of = 450 mm. The formula should then show which model that meets the requirements.
In this case: Models that meet the requirements for the lift.
ModelPushing/pulling Force (KG)Stroke Length (mm)
HCV 650 600
HCV 750 800
HCV 850 1000
I've been looking on the internet for a spreadsheet that lists the various units of measure with conversion factors to other units. I'm not looking for the formulas, just a chart.
View 3 Replies View RelatedSearching through the forums, I've found a post of daddylonglegs, witch shows how to find the smallest factor of a number:
View 7 Replies View RelatedThis is kind of an extension to a previously solved post. For a similar problem I used the attatched file (which someone from here so kindly came up with - but to be honest i dont fully understand),
but now im getting onto numbers from 1 to 100 so could get very confusing in the table!
RANDOM TABLE2.xlsx
Basically I want to produced a random number, (which I have managed) then another number, but it cant have any common factors as the first.
So 1st = 10, 2nd = 13 is acceptable
but 1st = 22, 2nd = 12 is not.
I have managed to do it with smaller values by just typing then out, but this is a very long winded technique, but is there a formula that can do this?
I have a spreadsheet where I want to record the VO2max value (a number indicating the maximum volume of oxygen a person can utilise) of a person.
The VO2max is just a number, normally between 30-60.
I then need excel to look up the VO2max value (i.e. “35”) and give that number a predetermined inputted value name in a different cell (i.e. “low” or “fair”, “good”, “very good” etc etc).
However, as VO2max is also affected by age and *** I also have a column for the participants age and *** and I want excel to use that to make the value name.
E.g. in a 24 year old male a VO2max of 32 is “low”
But, in a 42 year old female a VO2max of 32 would need to be classed “”moderate”
Any ideas how this can be done? can it be done?
I could post the table containing the age range, values and names if I knew how to post an excel sheet on here.
I was hoping to use the index function, but I'm having difficulty because the grid or table I'm pulling from, one of the row criteria requires two factors. It would be something like:
[A] / [B] / [C] / [D]
Numerical value / Dogs / Birds / Whales
1: 500-599 / Doberman / Parakeet / Grey
2: 400-499 / Rottweiler / Lovebird / Killer
3: 300-399 / Cocker Spaniel / Cockatoo / Baluga
So essentially, whenever a number falls within that range, it would pull the corresponding animal
450/Birds = Lovebird
320/Whales = Baluga
I wish I can show an excel spreadsheet on this, but not sure how.
=INDEX(ResultRange,MATCH(RowField,RowFieldRange,0),MATCH(ColumnField,ColumnFieldRange,0))
I'm trying to make a table of the total amount of a liquid used throughout the day. Here is what I am trying to do: In cell D4, I want to be able to enter something similar to the following: 3cup+2bottle+1liter
and by doing so, Excel can automatically recognize that 1cup is 8oz, 1bottle is 17oz, and 1liter is 34 oz because of the reference chart provided on the side. Also, it would be able to notice the 3, 2, and 1 amounts so it would multiply accordingly so it would know to do this: (3*8)+(2*17)+(1*34)
and then put the calculated amount in the cell. The correct answer should be 92oz. Is there a way for Excel to recognize the conversions (i.e. whenever it sees 'cup' it will multiply by 8) and multiplication factors (i.e. 3, 2, 1)?Is there a formula I can enter that I can just "drag" down to the upcoming days in column D?
I know I can just do something like this: (3*G4)+(2*G5)+(1*G6).
I am using Excel 2010 and I am trying to calculate 4 different factors' (columns) values which are numbers. Based on my analysis I will assign the value from 1-5 for each factor and at the end I have to decide the priority (a new column) adding all 4 columns' numbers. My Priority column has three criteria (High, Medium, and Low) but I don't know what formula/function to use and how. I have tried IF function but did not work.
View 9 Replies View RelatedI have an Excel 2003 situation I cannot resolve. In one workbook, I have a table of multiplication factors based on a numerical general ledger code. On another sheet, I have a data extract including that code and a cost. In the latter sheet, I need to multiply cost by the appropriate factor based on the GL code - IF Code=X, then cost*factor.
Should be simple, but I have too many codes for a nested IF statement. I also tried a VLOOKUP, but since the codes are numerical, Excel interprets them to be in the thousands, while my factors are 1.xxxxxxx, and since the data being looked up thereby appears smaller than the keys, VLOOKUP will only return #N/A or ?NAME, depending on whether I try to nest it in an IF or not. I also have several thousand records per month to which this needs to be applied, so going manual is not an option.
The calculating is done in column E, the original cost is in column AB, the key (GL code) is in column R, and the GL code range with factors is on the !Factors sheet (column A=Code, B=Description, C=factor).
*I'm running Excel 2003 on WinXP Pro
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range
[Code].....
I have a workbook with 7 different Sheets to pull data from. On Sheet 3 named "PMP Mileage Linear". I have 4 drop down boxes. In one drop down labeled "Drop Down 4" there are 21 different choices to choose from. Based on the choice I would like to hide or unhide Rows. Example: if a user chooses "3yr/36,000 Miles". I would like to hide rows 16:51. If a user chooses "3yr/60,000 Miles". I would like to hide rows 29:51 (hence un-hiding 16:28). If a user chooses "5yr/100,000 Miles" I would like only rows 41:51 hidden and so on until a user hits the max of 125,000 Miles (5,6 or 7Yr) whereas no rows are hidden. I would have attached the spreadsheet but I don't have permission from the people who own the data.
View 7 Replies View RelatedNot sure where to start with code to do the following.
I have a Large workbook 150 worksheets in which i need to remove rows based on date criteria.
I need to remove all rows which contain 2011 in cloumn D within the range of B10:T40 without filtering the work sheet first. on every worksheet in the workbook. also I would need the code to skip/ignore sheets that do not have a 2011 date incolumn D.
Time is not a issue for this procedure.
I have code that pulls data from multiple sheets in a folder and throws a time stamp in the row/col. The data can have duplicates when adding to the master sheet. What I would like is to delete the duplicate row that is furthest from the NOW date.
Criteria for duplicates are based off of Col A. Time stamp is in Col G.
Highlighted rows would be deleted:
Sheet1
*
A
B
C
D
E
F
G
1
Item
*
*
*
*
*
Imported
2
112
a
b
c
d
*
Apr-5-2013 08:53:19
[Code] ..........
I am using three columns - Col A contains serial number, col B contains name and Col C contains Codes.
The problem is in col C as sometimes the codes are copied from other files also. So I want that if there is any duplicate codes in column C, on running the macro it will delete the duplicate rows and keep original record ie the duplicte records added later has to be deleted and that the original record will be in place.
This is my input:
Col A
asde,dsfts-qwsa/001,asdasd-qwsa,asdas
etert,ttyutt-tww/013,dgdd-tww,dsfsaa
.
.
.
.
.
Col B
qwsa
tww
In Col C.. I want the output to be as
asde,asdas
etert,dsfsaa
Requirement as a text:
I need a macro that removes the value in column A based on the value in Col B.
The data that the macro removes is always between the "," (comma).
I have a large spreadsheet that has some duplicates in Row Q. However, in column L there is a date. I want to be able to find the duplicates in column Q then delete the duplicate with the earliest date.
[Code] .....
I have an excel sheet with data where I have column A index number, B company name, c empty, d data , e with item.
[Code] ......
What I trying to do is I made a dropdown for company list and i select comp1 then i want to populate items list with out duplicates .
I have about 25 workbooks with 180 formulas in them that I would like changed. The formulas are all in the range I10:Q29 and all have the same basic structure:
[Code]....
I would like to remove "filepath1!cell1*filepath1!cell2-" and "filepath1!cell2-" so that only "(filepath2!cell3*filepath2!cell4)/(filepath2!cell4)" remains.
My VBA is pretty weak, but I imagine it should be possible to use something like the VBA equivalent of len() and mid() to isolate the components of the equation to the left of the - in the numerator and denominator, and remove those components (as well as the -) from the formula.
Attached File : trim formulas.xlsx