i have data on number of employees in culomn A range from 1 to 10000. i need to generat four dummy variables in column B,C,D and E as the following
s=1 if number of employees 1-50 and zero otherwise
m=1 if number of employees 51-100 and zero otherwise
l=1 if number of employees 101-1000 and zero otherwise
xl=1 if number of employees 1001-10000 and zero otherwise
I need to generate a series of variables that model for events that occurred in previous days. If you look at the spreadsheet, you will see that on row 10, U01 = 1. This occurred on 1/13/2009. I need to generate the values for 1 Day Lag U01 - 15 Day Lag U01.
This means "1 Day Lag U01" = 1 for each row if in the row below it, U01 = 1. "2 Day Lag U01" = 1 for each row if 2 rows below it, U01 = 1. I manually entered in what this should look like in column AJ to AQ (it is only for 8 days though). how create a formula to do this. I need to do this lag model for all the binary var between C to AI on a 15 day period for 445 companies, so an explanation of how the formula works would be great.
If possible, can the formula end and restart for each company so row 1996 doesn't take on the value of row 1997, which is a different company. If this is too difficult, don't worry about it as I can just manually modify the last few rows of each company.
I'm trying to get the syntax correct, and not getting it right.
I need to calculate the Standard Deviation of values, but calc it based on more than one dummy variable.
EG: I need to Standard Deviation using data in column A, if the value in Column B (same row) is 1, and the value in Column C (again, same row) is 0. Right now, I have to reorder the data and calc it, then copy n paste values. I'd MUCH rather have a longer function so I don't have to reorder the data over and over again, and add and delete rows, etc.
I know it's a nested IF AND function, or I think it is, I just can't seem to get the syntax correct.
I want a formula to do the following. Count all instances when column A contains S or A and column B = 1. The example below would equal 2 (ONES MEETING CRITERIA ARE IN BOLD).
Example
N 2
S 1
A 1
D 3
X 2
I have an old DOS program I once used extensively to manually enter numbers from a market report for a series of calculations. I already know the calculations and have re-created in excel anyway, but it has a graph which helps with viewing the end calculation/fluctuations, plus it has it's own database. It then saves that specific report to DAT file. ( it's own "database", the key lookup is date and alphanumeric code) It's this very DAT file I want to re-create from my imports I do now with Excel/VBA then re-open that same DOS program.
if this is possible to re-plicate a "dummy DAT" file, but instead the info. within that DAT file is filled from excel after a Web Query Imports rather than typing in the info.?
I am making a weekly plan for machine inspection at the end of every column i need to sum how many machine will be inspected.
L= area
L1=10 ,) L1 has 10 machines ... this number can be change in the future that is why t is variable. for example:
L1=10, L2= 25, L3=20
week1
L1
L3
sum ?
I have a datasheet with 6000+ entries. One column has lists of companies, of which there are around 300 and their are entered into this column multiple times. I organized and identified the largest ones but a work file of 6000 is making it a long process to find every single company listed, paste their name onto a separate table to analyses. Is there a formula to take all the different company names and arrange them in a list OR list them just once in a column next to it? I will have to do this again and the analysis of the companies is the most important.but I find I need to spend all evening steadily combing the haystack for needles!
View 4 Replies View RelatedA B C D
COL 0
COL 0 1
COL 0
COL 0 1
COL 0
EDM 0
I need to add the number of 0's in column B if Column A is COL and C OR D is 1. correct function yields 3 for this example. I've played with =sumproduct(if)) and =sumproduct(if(and))) etc... can't figure it out.
I have data in three columns. The first column contains the machine name (Robot 3, Robot 4, and Robot 5), the second column contains the paint color (1-20), and the third column contains the data that I need to sum. For example the data could be paint purges, the number of purges from each robot. Each row of data will contain a number of purges for each robot, for each color. The data is provided by day, so I will be summing the data weekly.
View 8 Replies View RelatedSplit From Copy Non Null Cells In Column To Another. I'm trying to copy some of the results. How do you combine two variables with text into one cell. For example i want range("a1") = var1"."var2 so it would display var1.var2 if var1 = var1 and var2=var2
View 5 Replies View RelatedI recorded a macro to perform a graphing operation which will be used in a For-Next Loop which will run eight times. I need to modify the “ range” portion of one of the recorded lines to allow the graphing data to be dependent on variables I have set up as integers. See the applicable part of the recorded code below.
Dim firstrowno As Integer
Dim ltcol As Integer
Dim lastrowno As Integer
Dim rtcol As Integer
‘code which sets the values of the above variables Is Not shown
ActiveChart.SetSourceData Source:= Sheets("PLOTS").Range("AN27:AQ33"), PlotBy _
:=xlColumns
How do I replace/modify the ("AN27:AQ33") in the code above using the variable integers; firstrowno, ltcol, lastrowno and rtcol ?
I have a table which looks like this:
Name 1 IDNumber Name 2 Name 3 Column 5
Tom20148 John Malmo
Tom20148 Will Malmo
Bob20206 Will Malmo
Tom20206 Will Paris
Bob20206 Rob Rotterdam
Bob20207 John Rotterdam
Ray20207 John Paris
Tom20208 John Malmo
Ray20208 Rob London
Ray20209 Rob Paris
Bob20209 Will Malmo
Is it possible to have excel go through this list and assign each row a number in column 5 based on the names and the IDNumber? Basically, I would want each entry that is identical in name 1, 2 and 3 to be assigned numbers 1, 2, 3, 4, 5 etc based on their IDNumber. So Tom/John/Malmo with the IDNumber 20148 would get the number 1 in column 5, while the next match (Tom/John/Malmo/20208) would get the number 2 in column 5. For each different match of Name 1,2 and 3, I would want the count in column 5 to start at 1. So Bob/Will/Malmo/ 20206 would get number 1, Bob/Will/Malmo/20209 number 2 etc.
Please see attached example book.
In it I want to change name "G" in column A to "X" in column C.
And I want to do it for month of May in column B.
So a match won't do the trick because Name G is in 3 times so it should match on G and May from columns A and B.
How to catch this in a simple VBA line?
I need to create a macro, where the contents of a particular cell are dependent upon summing values based on a word desription from another column. For instance, I would like cell E10 to include a numerical value from cell D5, but ONLY if cell C5 is populated with the word "trust" as opposed to "equity". Conversely, I would also have a macro in, say, cell E11 that would do the same for the word "equity" in column C. I would like the macro to hold for the entirety of different words in column C.
View 6 Replies View RelatedI'm working on a workbook that will combine a cash flow report by category and also by date. The categories can change (which I think is the easiest part) but so also can the frequency of data. For instance, you choose how you want to see the data, monthly, quarterly, annually and this will change the Row that the data should be summarized based off of.
Examples:
1) Annual: Sheet 1: Row = Rental Revenue and Column = 2001. I want to pull from sheet 2 all of the Rows that are mapped to Rental Revenue and all of the Columns that are mapped to 2001.
2) Quarterly: same idea but by quarter.
3) Monthly: No issue, just a SUMIF.
In Sheet 2 I have added a mapping at the top of each column for the Quarter, Year or Month that should be included in the Sheet 1 heading Row.
I tried using a sumproduct on both row and column using the whole data set underlying, but this did not work.
"=SUMPRODUCT(1*('Sheet 2'!$A$9:$A$140='Sheet 1'!$C28)*('Sheet 2'!$E$9:$GA$9='Sheet 1'!E$27)*('Sheet 2'!$E$10:$GA$140))",
It gives me a #N/A result.
I have tried to attach a table of what I am trying to do.
Sheet 1
Choose Frequency
1
Annual
[Code]....
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
I am trying to select a range based on two variables which store the column numbers. what I have is:
View 4 Replies View RelatedI want to ask you in this kind of formula: =IF($B11=$B$51;$E11+$C$51;IF($B11=$B$52;$E11+$C$52;IF($B11=$B$53;$E11+$C$53;IF($B11=$B$54;$E11+$C$54 ;IF($B11=$B$55;$E11+$C$55;IF($B11=$B$56;$E11+$C$56;IF($B11=$B$57;$E11+$C$57;IF($B11=$B$58;$E11+$C$58 ;0))))))))
is 8 IFs the most I can use? And if yes, is there a similar formula to use so that I can use 12 variables and all the results to be in a single column?
Can a Function give two or more output variables. e.g.
Sub a()
x = 5
result = Y(x)
End Sub
Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.
I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...
Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset)
Dim fd As FileDialog
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet
Dim iFilePicked As Integer
Dim strFilePath As String
fd.Filters.clear
fd.Filters.Add "Excel files", "*.xls"
fd.ButtonName = "Select"
iFilePicked = fd.Show
If iFilePicked = -1 Then
strFilePath = fd.SelectedItems(1)
Else ..................
i have a "problem" to empty / reset my variables. I defined them as vHour1_KW2 where the "1" is from 1 to 21 and the "2" starts from 1 to 53. Now I want to erase all of this variables or to set the value of them to "0".
At moment I use following
vHour1_KW1 = 0
vHour1_KW2 = 0
...
vHour1_KW53 = 0
vHour2_KW1 = 0
vHour2_KW2 = 0
...
vHour2_KW53 = 0
until...............................
I'm having a hard time understanding how to accomplish what seems to be a simple result.
I need to display one of two words, based on whether or not a pair of values are above or below the criteria.
FIRST:
IF H6 is greater than 5000
AND
IF AB6 is greater than 25000
Display: Double
SECOND:
IF H6 is less than 5000
AND
IF AB6 is greater than 25000
Display: Single
There is no 3rd scenario, even though logically there should be.
I am trying to put variables in this URL which is related to yahoo finance :
.Name="hp?s=NVDA&a=00&b=31&c=2001&d=11&e=29&f=2006&g=m&y=0"
I defined at the beginning
Dim start_date As Date
Dim end_date As Date
Dim datestring As Variant
start_date = #1/31/2001#
end_date = #11/26/2006#
and put them in datestring
I passed the datestring to a new sub which has the URL:
.Name="hp?s=NVDA&a=00&b=31&c=2001&d=11&e=29&f=2006&g=m&y=0"
So, my question is, i tried to put the (1/31/2001) and (26/11/2007) which is in the above URL which is separated in variables and the URL remain the same
I am trying to use COUNTIF with two critera. If this isn't possible is there any other way possible of doing this in a range of cells.
What I am trying to do is show the amount of students in a year group who spend x amount of hours on the internet and have a target grade (for example) of Lvl 4
I have been trying use a formula along the lines of =COUNTIF (Q5yr7, "0- 1Hour", Q12yr7, "4")
vlookup with 3 different variables, for example cells k4 k5 and k6 can be changed to give different variables. Is it possible to have a vlookup function in cell k9 which returns the correct % when the 3 variables are chosen. example, blue boat 48 would return %value of 21%
View 2 Replies View RelatedI am trying to count the status and type of some work so:
Column A would contain the status of the work e.g. open, in progress, closed etc.
Column B would contain the department: ict, development, operations, etc.
I want to do a summary that shows: How many are in ICT are open, closed etc.
I can do a countif to get the total open, in progress etc or total number of ICT jobs but not ICT In progress.
I am trying to add an additional criteria to the following sumproduct formula. The formula below works fine to add up values that are within a date range. However, I want to add values within a specified date range as well as one additional variable. The additional variable is in column G.
SUMPRODUCT(--($A$3:$A$1015>=$A1026),--($A$3:$A$1015<=$B1026),D$3:D$1015)
Trying to work out the formulas for placing plus minus variables above and below a cell as per worksheet attached. right hand side of the page
View 2 Replies View RelatedI want to make an excel funktion than can distinguish between 3 variables. The three variables are the outcomes of my first function which are 0, 1 and 2 these IF functions can be seen below:
=IF(B11>52;"1";"0")
=IF(B12>5;"1";"0")
Then when I have these results I have tried the following function:
=IF(C11+C12=2;"6-12";"4-6")
By using this function I get an output for 2 (true) and 0 (false), but I would also like an output for 1 (which would be 6 in my work)
It looks like this:
Var 1 58 1
Var 2 4 0
Suggestion 4-6
So in the above case where the sum of var1 and var2 is 1 it is only counted as false for not being 2 and therefore = 4-6 instead of what I would like it to be = 6 (for 1)
I'm trying to count cells in one column that match a variable only if it also matches a variable in another column. For example, I want to count all of the cells in column A that match "Franklin" only if column D shows "True".
View 5 Replies View Related