I am trying to automate a simulation with the crystal ball add-in. Specifically the simulation will go through a set of workbooks in a directory - run the simulation (monte carlo x1000) and extra the "trial values" data in another spreadsheet.
I have tried to find crystal ball documentation but I'm not having any luck.
I am asking for help with the program Crystal Ball. Basically what I need is non repeating assumptions. I have a roughly 10 assumptions and they are all uniform discrete distributions between 1 and 10 but I need to make sure that if assumption 1 chooses say number 3 that no other assumptions chooses that number and thus all the numbers are used for each simulation.
View 9 Replies View RelatedI use excel pivot tables a lot for most of my information gathering for reports that I produce. Problem I keep running into is the 64k row limit. I work with up to 200K rows of excel data at a time and just need to pick out a summary of certain columns. I have read (mostly adverts) good things about crystal reports and have been considering giving it a try. It will be a steep learning curve for me, but I was wondering have this group any views on the program before I splash out on books, DVD courses etc.
View 6 Replies View Relatedorganising a golf tour for 32 players.playing in teams of 4 (4 Balls) for 4 games of golf and the teams are to be randomly selected such that no player plays in a group with a person he has played with in a previous game. finding it difficult to create a spreadsheet which meets this criteria.
need to build into the spreadsheet the fact that the variable mix of people is constrained by the fact that at least 4 persons have to use a buggy (which will have 2 people in it) and as the tour progresses and courses become physically demanding a number of people (N) for the last 2 rounds may also ask for the use of buggies as well.develop a spreadsheet to make the 4 Ball team selections.
I would like to embedd a symbol into a cell. I dont want it to be done by using Fonts. By symbol I mean Harvey Balls (Plan, Do, Check, Act) symbol embedded the same as the "and" (&) symbol. I have seen it done once on a spreadsheet but did not get a copy and I would like to recreate it as a way of visually showing the status of an action item.
View 14 Replies View RelatedI have been able to export data from a Crystal Report into an Excel file. After exporting to the Excel file I copy a Macro that I wrote into the workbook that deletes blank lines. My question is: Is it possible to export into a workbook that contains macros without overwritting the entire workbook which wipes out the macros?
View 4 Replies View RelatedI am having a very difficult time determining how to get started with this problem in VBA. I am not sure how to generate the failures, or start the array. I have been racking my brain for quite some time. Company B makes a product that has two component parts. The results of reliability testing for each component shows that the components fail according to an exponential distribution with the following mean time between failure (mtbf).
ComponentMTBF(months)
115
224
Below are Company B's sales records for the past 36 months.
MonthSalesMonthSalesMonthSales...............................
Has ever created a spreadsheet that used Monte Carlo Simulation?
I've been able to create one that works using the random number generator in conjunction with discrete probability distributions - that's fairly easy, just assign a probability to each discrete value in the distribution and then decide whether or not to select it using a randomly generated number.
However, I want to perform a Monte Carlo simulation that uses a continuous (not discrete) probability distribution, but how I would use the random number generator to then select a value from the distribution based on its probability of occurance.
I am newbie in Excel so i got limited knowledge of programming in excel. I need to perform a dice simulation. I will toss a die repeatedly until I got all six outcomes, then record the numbers of tosses.
I tried to use the function RANDBETWEEN() in to simulate the toss of the die but I don't know how to stop my simulation.
For example, I can generate a sequence like this:
1 3 5 3 4 3 2 1 2 3 6 5 6
but I need to stop at the 11th generation since I achieve all possible outcomes (1,2,3,4,5,6) there.
I have a PDF sheet with info laid out like this:
Code:
Peter Paul Mary
Text Corporation
One two three street
Mars, New York 90000 USA
phone: (111) 555-2222
email: tellmewhy@yahoo.com
There are 10 names and addresses like this on each PDF page and the entire PDF file has 50 pages so to manually copy and paste each bit of info into separate cells on the spreadsheet's appropriate cells "Name, Company, Address, State, Phone, Email" e.t.c is too difficult.
Im setting up a fixed hedging simulation using excel and want to use solver to obtain a maximum profit. I have taken historical stock prices and then predicted future prices using the random walk process. To create the hedge I am using european calls and puts in both long and short positions. The simulation has been set up so that the options are being brought/sold when the historical data ends and then sold/payed when the predicted data finishes.
I am letting solver change the values of the strike of each of the options and also the amount purchased/sold for each of the options too! My constraints are that all the strike prices have to be >= 0, all the amounts of each particular option has to be >= 0 and that profit has to be >= 0. It is worth noting that as well as the values already mentioned, d1 and d2 values are also being calculated on the sheet which are then used to calculate the value of the options being used (using Black-Scholes model), this value is then given a realistic spread value and then 3% is added on as the brokers fee. All of the values mentioned are being taken into consideration when caluclating profit.
how to automate data from excel into word. This vba code takes every sheet from excel and puts it into a word document:
Code:
Sub CopyWorksheetsToWord()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library
[Code]....
My problem is that it doesn't style the data into a table, is there any way to do this with the code I have posted? perhaps with a .Style code or something of that sort?
I am running Excel 2003.I have a spreadsheet with two worksheets, each worksheet has a Refresh button that runs a Data Extract Macro.The Data Extract Macro imports Data from another spreadsheet.I want to have the spreadsheet automatically run my Data Extract Macros for both worksheets at a specific time each week. (Scheduled task) Regardless of whether or not the excel file is open.
View 4 Replies View RelatedA typical Design Matrix is shown in the attached Workbook. There are two domains of Merged Cells that make up the Headings of the Matrix; FRs (Functional Requirements) and DPs (Design Parameters). Given a Hierarchical List of FRs specified by the User, the User would like Excel to bulild the Matrix Hierarchy of FRs automatically (going down the Worksheet). The DP Hierarchy is the same hierarchy, except transposed and reflected across the Worksheet. The attached Workbook has up to seven (7) levels, but the ability to go create up to 10 levels is desired.
View 8 Replies View Related how to automate my excel sheet i have got.
It has code which when you click the "Save" button it saves the document and then converts a copy as CSV with the same name.
I have been trying to automate this to run every 2 minutes but everything i have tried does not work.
I have tried using OnTime events but again does not work.
I have two spreadsheets. one contains a master list of computer names. the other contains a list of computer names to be removed from the master list (exception list).
I need to go through the master list and remove any computer names that are on the exception list- for example, if 'computer1' is in the exception list, i have to find and remove 'computer1' from the master list.
The exception list is quite long, and I want to automate this process if possible. not sure how to achieve this.
I'm using Excel 2007 Standard.
Code + Button to automate certain row heights.
1. Starting in row 5 the row height is to "30"...every 17 rows after row5 to have a height "30"...next row 22 is "30" , then row 39 is "30" and so on...
2. All rows in between row height "30" will be with a row height of "11"
3. Can this be associated with a button....
4. where would i place this code.....
Version:Excel 2010
I have the office 2007 Suite.I have a small business, where i sell online mostly. I'm creating a simple spreadsheet using Excel 2007, a workbook that has simple financial statements.
I'm creating a list of customers along with what they order, what they paid, how they paid, (paypal, cheque) etc, The first column is the invoice number, and each is unique to each customer. I add as i get orders. so my list of invoices has the following fields as column headers
Quote:
Invoice Number Customer Name, Paid by Paypal, Paid by Cheque, Amount, Cusotmer email, Customer address, There are more and i won't list them all. Now each time i send an order, i have to fill in the excel worksheet. I then create the invoice separately in Word 2007 which i print to send to the customer with the order in the mail. The third step is sending an email to the customer upon despatch, using Outlook 2007.
I don't want to use accounting software as that is too complicated for my simple little business. What i do want to do however is combine all three tasks instead of having to open different programs and copying and pasting between them. I'd like to be able to add the data to Excel either using a form (ideally) or direct, then i'd like to be able to click on the "customer email" field to send a confirmation email to the customer, and also to be able to have an automated invoice, either in excel or Word, which draws the information automatically for each invoice, so that i only have to print it.
At the moment i'm doing all 3 tasks separately, when i know they can be automated.
1. I have about 20 columns of data for each invoice, ideally i'd like to be able to enter them from a form as it gets cumbersome going through them all in Excel. The form could be either in Word or Excel, which would be easier?
2. Using the invoice number as the unique field, how can i create an invoice that would draw out the required fields from the Excel data automatically, just by clicking a button? I dont' mind using either Excel or Word, whichever is easier, and i do have Access too, but i don't know if i should use that, or not. I don't need to draw any other data. I only want to print invoices and send confirmation emails.
3. After printing the invoice and posting the order to the customer i sent them an email confirming. Again i have to manually enter or copy and paste data in the email, which i know could be automated, if i create a template, and have some fields that are automaitcally populated using the fields in the date already entered.
These fields ususally include the total paid, currency paid, address, name and how many ordered. All of this could be automated, but i'm not sure how to do it.
I am trying to figure out the calculation steps that Goal Seek is doing behind the scenes so I can create the formula rather than clicking on goal seek whenever I need to make a change. I need to maintain certain weeks of supply every week depending on the sales. Let's say i want to maintain 5 weeks of supply every week, I would like to insert a column for "Suggested To Order' column that would change if weeks of supply goes below 5. In this scenario I would do a Goal Seek, but I want to automate the calculation so I don't have to do this step every week.
View 2 Replies View RelatedI create a schedule based on abbreviations which is given to staff. Then this is retyped up with actual times and put in a schedule book that is the final product. I am trying to automate this change from one sheet to another
n = night shift = 2330-0800
D = day off
etc, etc
I have alot more abbreviations, but I hope I just need some advice and can figure it out from there....................
I have a workbook that I use for my purchase orders which is pretty simple. Each row is a separate PO, with 10 columns (po#, description, cost, date, etc.).
Is there a way to automatically generate new workbooks that contain all of the info from each individual row? For example: if I have 100 rows on my spreadsheet, which means I've issued 100 POs, then I want to have 100 individual xls workbooks, with the filename for each workbook representing the PO #s (206904.xls, 206905.xls, etc.).
Dont know if this is possible. I have a spreadsheet where cell A1 contains a value which is not fixed and can change daily, the value can increase and decrease. At present at each month-end I manually record the position of cell A1 at month end in cells C1 through C12 repsenting the 12 months of the year.
Example:
A1 @ 31/01/07 = 50 therefore cell C1 = 50
A1 @ 28/02/07 = 45 therefore cell C2 = 45
and so on for 12 months
what I would like to do is automate this process to automatically capture the value at month end into cells "c" and once captured, the figure to remain absolute for that cell/month and to ignore changes in Cell A1 that may subsequently occur.
Could this be done by date formula?
I have a VBA Macro that starts with a user form that gives me two options. I always need the same option selected, so is there a way to automate this? I want it to select Option B and click "OK" automatically. I don't want to get rid of the user form completely in the background because it has some other functions that need to remain intact.
View 3 Replies View RelatedIs it possible to set up 2 tables whereby if you change a field in 1 table it automatically cuts and pastes a row of info in to another table. Or possibly by clicking a macro hot button? E.g. An entry in 1 table is no longer 'live' so moves to the 'archived' table when a field is changed.
View 3 Replies View RelatedI have the table below and I am trying to automate the YTD calculation.
Jan
1
Feb
1
Mar
1
Qtr 1
3
[Code] ........
YTD
Need to automate.
I find that the picture toolbar opening automatically when I click on a picture useful (sometimes).
Is there anyway of automating other toolbars to open (such as 'chart') when I click on an object?
I've been looking at various add-ins in google - but can't find one to do that.
When I save an Excel workbook, I would like to automate it to also save as a web page in 3 different locations (and automatically close after save as a web page). I would like to be able to automate the save as a web page in the 3 different locations and close these web pages. But my Excel workbook (which I have saved as an .xls) I will close manually.
One location to save the web page would be on my desktop and the other 2 locations would be on different shared drives. Can this be done with ******* event or something?
My goal is to automate an excel userfrom. So from outside of excel, i would look to be able to interact with the controls (simply fill in a textbox for example). I'm thinking this may be possible using windows API (user32) and system hooks, but not entirely sure where to start.
I saw this previous thread, that suggests it may not be possible :
[url]
i have 110 different columns. i want to do the same thing to each of these formulas (ie i want to calculate things like ln (A2 / A1)
is there some way i can automate this process so i only nhave to type in my formulas once and it will automatically enter in the same formulas for the other columns?
I work in the accounting department. We have an excel workbook for each account with sheets for every month. These sheets contain a template with pre-formulated cells.
For example, the 2010 workbook for account XYZ will have sheets named - Jan 10, Feb 10, Mar 10 and so on.
What we do, is move and copy the previous month sheet, and rename it to the current month.
But each person is responsible for over 100 accounts, so it becomes rather tedious.
So I wrote a code which would automate this process.......
