Distributing Numbers In A Pattern
Dec 21, 2008
I am trying to come up with a formula that will distribute the numbers as shown in this diagram:
To get the value of each number divide 42,000 by the last number in the pattern. For example the fifth column from the left would be 42,000/18. That means that each number would have a value of 2,333.33. By the time you reach the half way mark, nine in that example, you should have around 21,000, and the other 21,000 should be distributed between the last half (block 10 thru 18). The formulas would have to work on all of the patterns. Can anyone think of a way to match the pattern shown? It doesn't have to be exactly the same pattern just as long as the number of blocks remains the same for each half, and they total 21,000 for each half without going over 42,000 all together. There has to be 11 rows from top to bottom.
View 9 Replies
ADVERTISEMENT
Jul 23, 2013
I'm working with a dataset from a survey that has recently taken place. The original structure of certain questions in the survey were multiple choice. The ideal structure for these questions, in terms of variables, is that each possible option for the question (A,B,C,D... etc) have a separate value, with a 1 for yes (reported) and 0 for no (not reported). In other words, if Question1 has multiple possible answers (A,B,C...), then there should not be one variable created from this question, but rather, there should be a variable for each possible response: Question1_A, Question1_B, Question1_C, and so on. And within each of these variables, should be a 1 or 0 depending on if that option was reported or not.
The format I received the data in, though, was a single variable with a list of letters, (e.g. B,D,F) for each option reported.
Variable: Question1
Row 1 A, B, C
Row 2 B, E, F
Row 3 B, D, E
The data I have right now is housed in Excel prior to exporting to SPSS and/or STATA. I need to find a way to distribute these values into separate columns (variables). The tricky part, is that if I simply use "text to columns", it distributes the letters to new columns, but doesn't take into account that the first value reported might not be A. Is there a way to take the list of values in the single columns, and distribute them to assigned newly created columns?
View 6 Replies
View Related
Jul 30, 2014
how can I modify the formula below so that after every row (i+60) the letter D changes to E then F, then G..... and so on. I want the following with the formula below:
=MAX($D$2:$D$61)
=MAX($E$62:$E$121)
=MAX($F$122:$F$181)
and so on...
[Code] .....
View 8 Replies
View Related
Dec 19, 2012
I would like to search for a certain pattern that appears in a cell. For example, I have certain cells that begin with a 7 digit project code (Ex: "1234567 - Project Red"). I was planning to use an IF inside a FOR loop and then do an action when I find the cell with the pattern. I thought I remember it being something like Format(########*) so that I have the wildcard on the end since there is more than just the numbers. how to make other patterns work such as Number-Letter-Number or Capital-Lowercase-Capital
View 3 Replies
View Related
Mar 13, 2009
I need a function or VB code to parse through a column of numbers and find a repeating pattern. The column has some initial numbers that do not fit the pattern and I need those returned along with the repeating pattern.
View 11 Replies
View Related
Jan 31, 2007
Pre-requisite: I would consider myself to be very poor with excel, based on what I've read on this forum and found on my web-searches. I have a worksheet that has a list of data on the left going vertically, then a summary of this data going horizontally across the top. It is not arranged in such a way that transposing the data will do what I want. I am pulling the 5th word out of the title of each block of the vertical data and need to show this word on the horizontal section.
When I use this formula to pull the 5th word: =MID(MID(MID(SUBSTITUTE(A2," ","^",4),1,256), FIND("^",SUBSTITUTE(A2," ","^",4)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A2," ","^",4),1,256),FIND("^",SUBSTITUTE(A2," ","^",4)),256))-2)
I need to increase A2 to A30, then A58 (up by 28 every time) in every instance in that formula. The fill handle increases the values by 1, instead of 28 (even if I do 3 or 4 instances manually) How do I do this? I've run into this problem in other scenarios, and there HAS to be a way to get around it.
View 5 Replies
View Related
Apr 10, 2007
I've written an add-in that I need to give to about 30 people in my office. What's the best way to get the add-in saved in everyone's Windows Add-Ins folder?
View 4 Replies
View Related
Apr 26, 2006
I have a number of identical workbooks used for data capture within the organisation, and need to continue development as time goes on.
Whenever I make a change to the core Excel functions of the workbooks I simply use a VBA procedure to conduct this change within each of the production workbooks, however I am stumped as to how I can achieve this for changes to the VBA code.
I have made some changes to the code held within one of the modules, and have also modified the form slightly. What I want to be able to do is run a VBA procedure that either updates the production workbooks from a central source, or by importing previously exported code / forms.
View 5 Replies
View Related
Jun 9, 2007
I have a number of sheets that have sheet macros that are identical.... i am changing the sheet macro in 1 and want to see if there is an automatic way to distribute it to the other sheets automatically.
View 9 Replies
View Related
May 10, 2009
I am using conditional formating to show dates as a bar graph (nice little template I got from Vertex 42). I have fee dollars in another column that I would like to distribute according to those dates. I have posted a snapshot of the image here:
[URL]
how I can get the dollars to populate the cells according to the dates.
View 7 Replies
View Related
Oct 31, 2013
I have 2 dates and I'm looking to distribute the days between in the correct 'year' column (See below). my problem is with the units that have days that span +2 years.
I've tried nested 'If(and...)' formula......
View 2 Replies
View Related
Feb 25, 2008
I have a master spreadsheet with names and addresses and action dates all in one sheet tab.
I want to be able to get all the data that pertains to action dates in March 08, for example, onto a sheet tab in the same spreadsheet called march 08, and then all the data from action dates in april 08 to go to a sheet tab called april 08 etc etc
What would be the best way to do this?
View 9 Replies
View Related
May 15, 2006
I have a sheet with 5 columns in use at the moment.
Col: A - E . Col C has a Score and Col D has Game #
I'll like some code to look at each cell in col D and based on the Game# copy the value from the corresponding Col C to the Game column that is set up for that Game. ( I can set these up manually )
Col D can contain both numbers or text
Something like
IF D2 = 23, C2 = H2
IF D2 = 29, C2 = M2
IF D2 = "HOME", C2 = W2
etc etc
Col D can contain up to 20 possible numeric values & 8 possible text values
Also: Since I know all the 28 possible values that Col D can contain, how can I also get those columns set up automatically using VBA and SUM those columns that are set up with the numeric values at the last row.
View 4 Replies
View Related
Apr 22, 2009
I imported a CSV file into Excel, and the data from the CSV file all ligned up into one column. For example: I have (date, price) both in one column. I need to separate these values into two respective columns.
View 2 Replies
View Related
Mar 15, 2012
I am trying to enter this formula in cell B2 and i just wanted to know if there was a better and quicker way to do this. I need to do this all the way to BP however my list can extend however the pattern will stay the same.
Ws.range("B2").value=
F & LROW * D1 +
K & LROW * D6 +
P & LROW * D11 +
U & LROW * D16 +
Z & LROW * D21 +
AE & LROW * D26 +
[code]....
View 1 Replies
View Related
May 19, 2008
I need to have a string comparison done in a macro,
I have a files with names similar to "TEVT_GURUPRASAD_WEEK08" and a array of strings having names "Guruprasad,AnilKumar,....etc." I Need to match the name in the array and the name in the filename.
View 9 Replies
View Related
Apr 7, 2009
The spreadsheet I'm working on is kinda hard to describe, but I'll try.
It's a finance calculator of sorts that I need done, and it shows me the total cost of various products. Each of these products require different components, and numbers of components, and also these components all cost different prices. So what I have is the following spreadsheet setup:
View 9 Replies
View Related
Sep 9, 2013
I'm trying to extract specific character from a set of text strings which have irregular patterns. For example:
CJWHCon.D005 - JN Holding Inc Corporate
CUSViWind.D404 - Windows Ringtown Vinyl
My goal is to get only the 'DXXX' which sometimes might be only 'DXX'. I've tried using the following formula but I seem to be going wrong somewhere as I only get 'D'.
=MID(F10,FIND(".",F10)+1,FIND(" ",F10,FIND(" ",F10)+1)-1-FIND(" ",F10))
View 2 Replies
View Related
Jul 15, 2014
I'm trying to repeat a pattern which outputs 4 equal numbers and then increments that number by 1 for another four row as follows
1
1
1
1
2
2
2
2
and so on
how to do this?
View 9 Replies
View Related
Feb 14, 2008
Need a macro that will autofill in an email pattern from a customer list i recieve. So far i have been having to manually do it.. but when have documents over 3k entries.. ouch.
This is what i got.. hope the way i type it in makes sense
A ..........................B ..........................C
First name........Last Name ............Email Pattern
John ..................Smith ...............First name initial, last name@abc.com
John ..................Smith ...............First name initial, dot, last name@abc.com
John ..................Smith ...............First name, dot, last name@abc.com
John ..................Smith ...............First name, initial of last name@abc.com
Is there a macro i can make that will automatically fill in the email based on the criterea found in column C before the @ symbol? then take the required info from Column a and b?
View 9 Replies
View Related
Oct 2, 2008
I am a beginner to VBA and Macros, and I have a fairly complicated macro that I am pressed to make.
I am working with alphanumeric data that is unorganized. Here is an example of what it looks like: ...
View 7 Replies
View Related
Jul 28, 2006
Is it possible to change the borders/pattern of a single cell so that it looks like the image in the attached document? This may seem a strange thing to want to do, but there is a good reason! Merged cells will not suit my purpose. The pattern palette allows a cell to be filled with horizontal lines, but I need there to be just two lines (i.e. dividing a single cell into three boxes).
View 2 Replies
View Related
Apr 12, 2007
I made a code that automatically checks the date and then compares it to the date of the last save. If it is a new date then the colors of the tabs will randomize. It can also be done manually via a button. The problem is that I have now realized that the colors are always have the same pattern. Every day the first color is an orange color, no matter how many times I randomized yesterday...and so on. Is there a way to make the randomness not have a repeating pattern?
Sub MMVII_4_9_b()
Dim NewDate
If Sheets("Scrappaper"). Range("F2") = Date Then
NewDate = 0
Else
NewDate = 1
End If
'Determines whether it is a new day or not
Sheets("Scrappaper").Range("F2") = Date
'Updates the date
If NewDate = 0 Then
'Does nothing if the date is the same
Else
View 7 Replies
View Related
Dec 18, 2007
I'm trying to write 'user-defined' function in Excel, that doing following:
Getting two arguments: cell and 'like' pattern (_ _ , *, e.t.c).
As a result returns data that match this pattern.
For example:
1. Cell = "golf car is really comfort", pattern = "_ _ _ _" will return "golf"
2. Cell = "toyota is number one", pattern = " num*" will return "number one"
View 6 Replies
View Related
Aug 1, 2014
I am comparing two years' data, one on top of the other (Rows 1 and 2). For each cell in each row, I am referencing a cell from a different tab. This needs to be done with about 290 data items, so 580 rows.
When I attempt to copy the two rows down, it doesn't go in the correct order (skips a cell reference number). It looks like this:
Excel Forum Q.xlsx
It would save me a lot of time considering each of the 580 rows has about 30 different column with each cell referencing various things.
View 4 Replies
View Related
Jun 9, 2009
Create a workpattern using something similiar to the Networkdays function. I want to be able to calculate how many working days are between 2 dates if the employee works a non standard Monday to Friday Week. For Example if someone works ever Mon, Tues, Wed how many working days are there for them in June.
View 11 Replies
View Related
Apr 5, 2013
I need to perform data mining from a CSV file,and use correlation function to analyse and generate a pattern from the provide data (CSV) for a particular set of error for a given time sample.
Find the CSV file which contains the data which needs to be analysed and generate a pattern.
eventLog_20121205_140322_00000000002119445219.csv
View 4 Replies
View Related
Jul 14, 2014
I have a formula such as below..
=SUMIF('MBX TO DBS'!$G$1:$G$818,"*livedb01*")
This will sum the values in G1-G818 that contain livedb01 on the "MBX TO DBS" sheet, however this is not exactly what I want.. I want the SUM function to look for the livedb01 string in G1-G818 and SUM the values in D1-D818.
So instead it would look for livedb01 text in G column and take the value from D and SUM those once the text qualifier was found in G column..
Is this possible with Excel?
View 2 Replies
View Related
Jun 10, 2014
I want the first 60 rows of column C to be constant meaning C1, C2, C3, C4..C59 and after 60 rows it should start again with C1, C2, C3.....C59 rather than C60, C61, C62. In other words i+1 but after 60 rows i should be reset to 1 and then again increase by 1. how can i implement these changes
[Code] ......
View 2 Replies
View Related
Apr 10, 2013
I have the following series of formulas consecutively in a single column:
=IF(A3=INDIRECT(ADDRESS(2+MATCH(MIN(H3:H50),H3:H50,0),1)),"YES","NO")
=IF(A4=INDIRECT(ADDRESS(2+MATCH(MIN(H3:H50),H3:H50,0),1)),"YES","NO")
.
.
.
=IF(A50=INDIRECT(ADDRESS(2+MATCH(MIN(H3:H50),H3:H50,0),1)),"YES","NO")
=IF(A51=INDIRECT(ADDRESS(2+MATCH(MIN(H51:H98),H51:H98,0),1)),"YES","NO")
=IF(A52=INDIRECT(ADDRESS(2+MATCH(MIN(H51:H98),H51:H98,0),1)),"YES","NO")
.
.
.
Effectively, I am comparing the values in each set of 47 to determine which one is the smallest. The smallest entry will get a YES label next to it and all others will get a NO. The problem I am having is the the autofill feature is not correctly detecting the pattern, even if I manually fill in the first 49 entries (shown above). If I hilight all of the above entries and autofill one down, I get the following:
=IF(A53=INDIRECT(ADDRESS(2+MATCH(MIN(H53:H100),H53:H100,0),1)),"YES","NO")
It should be:
=IF(A53=INDIRECT(ADDRESS(2+MATCH(MIN(H51:H98),H51:H98,0),1)),"YES","NO")
Basically I need the H:H: reference to stay constant, but only for 47 cells at a time. After that, both the lower and upper bound should increase by 47. Is there any way to indicate this to Excel? Manually typing in the new formula every 47 entries isn't practical because the spreadsheet has thousands of entries. How I can accomplish this using a single formula and autofill?
View 2 Replies
View Related