Excel 2010 :: Random Numbers In One Function
Jul 22, 2012
I'm currently working on my masters dissertation and am using excel 2010.
Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.
I'm sure there has to be a faster way than creating 1000 tables for each random number generated.
Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.
View 2 Replies
ADVERTISEMENT
Jul 22, 2012
I'm currently working on my masters dissertation and am using excel 2010.
Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.
I'm sure there has to be a faster way than creating 1000 tables for each random number generated.
Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.
View 1 Replies
View Related
May 19, 2014
I am trying to create challenge/response cards. Each card will have a 5-digit number in the upper-left and lower-right corner along with a series of rows containing a line number and 3 pairs of challenge/response words.
Rather than have have copies of these cards, I want the central office to have a macro-enabled workbook wherein they can enter the two numbers from the card and have the challenge/response matrix regenerate on a worksheet.
So, essentially, I want the challenges random number generator seeded from one of the 5-digit numbers and the responses random number generator seeded from the other 5-digit number.
I am trying to use
Code:
Randomize(Seed)
where Seed is one of the 5-digit numbers.
The problem appears to be that, no matter what I use for Seed, the RND(-1) function is not resulting in different numbers.
I am using Excel 2007 and Excel 2010.
View 1 Replies
View Related
Mar 23, 2009
I'm using the prob() function, and it works for no more than 61 random numbers. Does anyone know of a limit on the number of random numbers used in the function, and whether it's possible to use more numbers in the function? My formula is:
=PROB($C$11:$C$71,$L$11:$L$71,G11,H11)
View 11 Replies
View Related
Aug 11, 2014
Im looking to create random data for an athlete over about 4 months with daily tests, however when im using the =randbetween(*,*) function the data is too 'Random'. so im looking to create a trend in the data, like real athletes will have. is there a function in Excel 2010 that can do this?
The boundaries of the data are -336,416 (i know its quite a big range)
View 8 Replies
View Related
Jul 18, 2012
How do I create a 6 digit unique random number for use as an ID in column A. Once created the rows with preexisting 6 digit unique random ID numbers must not change every time new rows are added.
View 9 Replies
View Related
Feb 20, 2013
I am extracting data from a website to excel 2010. my problem is the data contains both text, numbers, and sometimes a combination of both.
e.g. hi im ron for more information you can reach me at 6 five 6 four 5 seven 7 three 2 two..
I need it to look like this 6564577322 or 7 * 0 * 2 * 4 * 5 * 6 * 8 * 6 * 2 * 1...i need to look look the same
How do i first format the numbers written in text into numbers and then show only the numbers in a cell minus the text?
View 3 Replies
View Related
Apr 8, 2014
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
View 14 Replies
View Related
Sep 18, 2009
I am working on a Random Cycle Count Generator that provides random SKU#s based on 3 separate columns of SKU listings. The user clicks a button to generate the SKU#s to cycle count for that day. What I would like to see is a date stamp in the columns next(B,D,F) to the referenced SKU listing(A,C,E) based on which SKU#s are generated. This will let me see the last date that the SKU was generated. I would also like it to automatically save after generating.
View 2 Replies
View Related
Jan 24, 2014
The situation I have on my hands is over 200 users, were the majority can't use Excel's built in function to create PDF's.
Yes, I've tried to teach them, but I'm probably not a good teacher.
So, I've resorted to create a button at the top of the worksheet, that says "Create PDF". I thought this was gonna be very easy to program, but alas.
Is there a way to start the built-in Excel dialog that happens when you click "Create PDF/XPS" in "Save & Send"?
View 4 Replies
View Related
Dec 21, 2011
Having problems with the code below on a 2010 Excel spreadsheet. The function flags me at first line and highlights Mid. I get a "compile error, cannot find project or library". I'm trying to set GetSheetName = m_sSheet but the GetSheetName function is not declared as returning any type.
Code:
Function GetSheetName(ByVal m_sFormula As String) As String
Dim m_sSheet As String
m_sSheet = Mid(m_sFormula, 2, InStr(m_sFormula, "!") - 2)
If InStr(m_sSheet, "'") Then
m_sSheet = Mid(m_sSheet, 2, Len(m_sSheet) - 2)
End If
GetSheetName = m_sSheet
End Function
View 1 Replies
View Related
Apr 10, 2014
Basic Excel-2010 functions.
I have given a table with some decimal values like e.g 0.3658 0.358 0.485 0.7863 0.558 0.858 0.985, I want to find the sum and average of these number.
1) I have tried the autosum or auto Function =AVERAGE(A2:A7) but it yields #DIV/0! error
2) when I use =AVERAGEA(A2:A7), it yield ans=0
3) puttin =ROUND(AVERAGEA(H2:H23);3) also yields 0;
View 4 Replies
View Related
Jan 13, 2013
I have this text at J22 and i would like to break out 13 and 95 000 out of this:
<td><span>13</span></td><td class=""right_align""><span>0-0-2</span></td><td class=""right_align""><span>95 000
The numbers and lenght of the numbers where 13 and 95 000 are may vary in lenght. I need them to parse the values at those spots to separate cells.
Im using excel 2010
View 12 Replies
View Related
Jan 16, 2014
Open a brand new spreadsheet whether the format is set to "General" or "Text" it will not let me enter numbers. For example, when I enter 2007 in the cell it displays 20 07 not really that big of space between the two zeros - but still a space. I can't format this no matter what I do.
View 1 Replies
View Related
May 29, 2012
In Excel 2010, I have a list of numbers with about 7000 rows. I am trying to track the number of times the number "22" appears within 30-day periods, with the end-result being a "count column".
While I could enter a formula manually for the date ranges, there are over 200 time-spans I'd have to enter (list goes back to year 1994). Is there a way to automate this process...
Excel 2010ABCDE1DateNumbers List# of Days AgoCount for number
22205/31/12304305/30/12602405/29/12909505/28/12301202605/27/12391505705/26/12371807805/25/1222210
11905/24/123824051005/23/122227091105/22/123230011205/21/123533001305/20/12383608
[Code] .........
View 1 Replies
View Related
Aug 5, 2013
I have a column of Dates in Col B with corresponding X values in Col C. I specify a start date in Cell F14 and an end date in Cell F16. I then use the function shown in cell H6 to identify the starting row number (corresponding to start date) in Cell F6. If I used the same function to identify the end row (in this case 4/2/13), it would choose row 8 which is the first row in which it encounters that date. Unfortunately, I need the end row to identify the last row that date appears in. In this case, that would be row 11.
Excel 2010ABCDEFGHIJKLM1 Date and TimeX2
3/30/201334/1/2013 1:366.9744/1/2013 4:563.95 54/1/2013 4:573.27
Start Row64/1/2013 5:165.553=MATCH(DATE(YEAR(F14),MONTH(F14),DAY(F14)),$B:$B)+174/1/2013 5:172.35
End Row84/2/2013 17:191.59894/2/2013 17:192.81 104/2/2013 17:252.14 114/2/2013 17:262.05 124/3/2013 5:152.84 134/3/2013 5:154.11
Start Row144/3/2013 5:173.2404/01/13154/3/2013 5:173.45
End Row164/3/2013 17:124.0304/02/13174/3/2013 17:133.621819Sheet1
View 7 Replies
View Related
Sep 18, 2013
I would like to combine 2 columns, containing numbers on it, is it possible?
I have found the other option how to do it
=A2&B2 but then this doesn't work
I have
COL A COL B
12345 0000
The result I want would be= 000012345
View 2 Replies
View Related
Jun 15, 2014
Using Office 2010.
I am trying to find duplicate numbers in sets but so far I can only highlight the ones that are in exact order. I need to find each set that has the same numbers, in any order. Example..
I will provide an example of sets of 3. But I get 3, 4 usually but sometimes 5 or 6.
I get them from different people.
Person A- 234, 569, 498, 849, 848,343,567,347 etc...
Person B- 432, 596, 677, 566, 565,433, 455 etc..
Now I need to find each set that has the same numbers, any order. Like 234 from A and 432 from B would be the same, so I would need to highlight them 2 sets. But I can not figure out how to do this. For Excel to highlight it they have to be 234 and 234. Does not recognize same numbers, different order.
View 7 Replies
View Related
Dec 28, 2011
Im trying to write a code in vba in Excel 2010 that would incorporate the vlookup function in excel.
My Data Looks like this:
List 1Weight 1List 2Weight 2List 3Weight 3List 4Weight 4a2.00%j20.00%a14.00%p2.00%b4.00%k32.00%d2.00%y3.00%
c7.50%e10.00%g14.00%u6.00%d8.00%d15.00%h20.00%h7.00%e9.00%q13.00%y45.00%f8.00%f20.00%r5.00%q5.00%
d9.00%g35.00%n5.00%t10.00%h10.00%r14.00%i4.50%w15.00%b0.18Z0.04S0.04Total100.00%Total100.00%Total100.00%100.00%
Where I have X number of Lists, each composed of two columns. The 1st column has the name of each item in each list, and the 2nd column has the value for that item.
What I'd like to do is create a function that would let me choose two lists, and tell me the amount of items in List X and what their values are in List Y, and then total them. Also, I would like it to work the opposite way, and tell me the amount of items in List Y and what their values are in List X, and then total them.
So for example, If I wanted to look at lists 1 and 2, the function would calculate that for List 1, Items D & E are found in List 2 and have a total value of 25%. For List 2, Items D&E are found in List 1 and have a total value of 17%. For all items not in both lists, it would return values of 0.
The code I came up with so far looks like this:
Function AK_Overlap_Go(x, y)
Dim Temp(1 To 2, 2 To 1)
Dim x As Integer
[Code]...
One of the problems I'm having is that the lists contain a different # of items, and so I think I need to loop the vlookup for each row. However, I'm not sure how to do that and get the cumulative values for each list.
View 3 Replies
View Related
Apr 18, 2012
The problem is that whenever I have any browser open, IE, Chrome, Firefox, etc... the Cut & Paste, Copy & Paste function does not work correctly in Excel 2010.
When I Cut or Copy the blinking marquee around my selection briefly appears and then disappears. When I try to paste, I only have two options under the paste special function: Unicode Text and Text, same with cut and paste, however, the text doesn't actually cut, it only copies.
As soon as I close down any of the mentioned browsers, the full functionality of the cut/copy & paste functions are restored, no need to restart excel.
I need to have open a browser most of the time for work as our system is web based, so closing and re-opening is more than just an annoyance.
View 2 Replies
View Related
Sep 16, 2012
Using Excel 2010 Pro.
I have a formula to create a string to create a lable for a Trial Balance report
=LEFT(I5&" "&REPT(".",95),101)
But the decimal portion of the string is distracting. I would like to make it a grey instaed of a black so that the text portion of the label "pops".
I don't believe I can do it with conditional formatting and I do not want to do it manually.I was thinking some kind of function or UDF like:
=LEFT(I5&" "&RGB(REPT(".",95),(191,191,191)),101)
View 3 Replies
View Related
Jan 4, 2014
I am working with Excel 2010 and I have a problem that I can't seem to figure out. I am trying to find the first nonzero cell and begin an average function from that cell and down through 29 rows (a total of 30 rows).
Sub average()
FinalRow = Cells(Rows.Count, 3).End(xlUp).Row
For I = 3 To FinalRow
If Cells(I,3).Value 0 Then
'I use the above to identify the first nonzero cell
ActiveCell.FormulaR1C1 = "average(........)
This is where I am stuck, I don't know how to code the first nonzero (which can change with different data) cell and average rows below it.
View 7 Replies
View Related
Jul 17, 2014
I think I need some version of a lookup for match function. Here is essentially what I need: I have a column of data points. I need to output every position at which the data point changes to a different value (going from top to bottom). See below for an example.
Here is some sample data (with column headers)
Position
Data Pt
1
-7.5
2
-8.0
[Code] .......
Here is the result I need (I want to put a consistent formula into the second column below that returns the results shown in the second column below):
split 1 position
2
split 2 position
4
[Code] ........
Is what I am describing possible? Because I am inserting this into a template, I ideally need to do this outside of any sort of VBA work.
View 2 Replies
View Related
Dec 10, 2013
I am working in Excel 2010 (Windows 8) and am having real problems trying to find a function/formula that will work with both numbers and text. I am trying to identify if the contents of Col A cells and Col C cells on each row are duplicated within a worksheet, and if they are, to add a 'J'.
(Column A is a list of numerical company codes and Column C is a list of adviser codes using the letter A-G. If a company ID code has more than one of the same adviser codes referenced to it, it is termed 'Joint', hence the 'J').
The formula I've used is: =IF(A2:C2, FREQUENCY>1, "J")
But it tells me 'numbers only'.
View 4 Replies
View Related
Apr 21, 2014
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 Related
Apr 29, 2014
I am looking for a function or a formula that performs the same process in Excel 2010 as:
Data Tab -> Sort & Filter -> Advanced -> *radio button* Copy to another location -> List range: A1:A100 -> Copy to: B:B -> *radio button* Unique Records only.
View 2 Replies
View Related
Apr 4, 2014
I get syntax error on return statement. I am using Excel 2010.
View 2 Replies
View Related
Feb 14, 2012
What I wanted was to a function, say, "=LastModifiedDateOf(CELL)", where CELL, is a parameter that indicates the cell I want to monitor. If the value of such cell ever gets changed, the cell containing the function has its value updated to the current date.
E.g.
A1 = "AA"
A2 = "=LastModifiedDateOf(A1)" -> "10/03/2011 10:30:32"
-- Make an update:
A1 = "BB"
A2 = "=LastModifiedDateOf(A1)" -> "14/02/2012 12:15:11"
I'm not quite a super user in Excel, but this function would be very very useful for me.
ps. I'm using Office 2010
View 4 Replies
View Related
Jan 10, 2013
I try to call a function/procedur in an Excel2010-AddIn (.xlam file) from another Excel2010-AddIn (.xlam file):
Code:
Application.Run (filename.xlam!functionname)
Both AddIn-files are in the same directory (the default AddIn directory).
This call causes the error message "runtime error 91: object variable or with block variable not set".
This problems came up with providing an existing Excel2003-AddIn for Excel 2010.
The same code runs in following cases:
1. call a function in an Excel2003-AddIn (.xla) from another Excel2003-AddIn (.xla)
2. call a function in an Excel2003-AddIn (.xla) from a Excel2010-AddIn (.xlam)
View 1 Replies
View Related
Feb 20, 2013
I'm using Excel 2010. I have three columns. Column A contains the date, Column B contains the time, and Column C contains the date and time. Column C is the result of a formula, which adds Columns A and B (ie. =A2+B2). So I have the following...
Date
Time
Date and Time
1/15/13
9:00:00 AM
1/15/13 9:00:00 AM
1/15/13
9:00:05 AM
1/15/13 9:00:05 AM
[Code] ....
I'm using a defined name to store a date and time, which I'm going to use as a lookup value in a Match function. I've defined MyVal as follows...
1/15/13 9:00:20 AM
Excel automatically converts this to the following decimal number...
=41289.3752314815
When I first run either of the following two macros, they correctly return 5...
Code:
Sub test1()
Dim x As Double
x = [MyVal]
Debug.Print Application.Match(x, Range("C2:C10"), 0)
End Sub
Sub test2()
Dim x As Date
x = [MyVal]
Debug.Print Application.Match(CDbl(x), Range("C2:C10"), 0)
End Sub
However, when I save and close the workbook, and then re-open it, these macros both return an error. Is this some sort of bug in Excel?
View 9 Replies
View Related
