Free Microsoft Excel 2013 Quick Reference

Crystal Ball Users

Hi All,

I recently bought an academic license for Crystall Ball, which is like a probabilistic monte carlo "add-in" for excel. However, the guys at crystal ball do not support academic licence users.

All I would like to know, is how do I create a PERT distribution in crystal ball, so i can use in my simulation.

Any asistance would be appreciated.

Thank you

Post your answer or comment

comments powered by Disqus

Suppose that I am using goal seek function to find out the volume of oil that i have to sell to achieve a 10% return on the investment.

How can i define it in crystal ball simulation to seek the range of volume that could possibly generate a 10% return on investment?


how do I load crystal ball add-in?

I recently had my laptop stolen. Whilst setting up my new laptop I am trying to reinstall Crystal Ball 4.0 but Excel 2003 (with OS XP) wont recognise it. My previous laptop had the same software and OS and when I installed it previously the Crystal Ball web site had a help page that gave instructions. Now though it recommends that you upgrade and says XP wont support CB 4.0. I don't want to upgrade as CB 4.0 suits my needs. This seems a bit ridiculous given it had worked previously.

Does anyone know how to get XP and Excel 2003 to recognise the CB 4.0 add-in?

Hello, 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 run (1000 runs in the simulation). Can anyone help me with this?

Hi all

I am looking into the crystal ball in an attempt to plan how my group handles data into the future and would appreciate any thoughts from the collective wisdom of this forum.

The problem:
I have a series of people (observers) on fishing vessels collecting masses of data in relation to catch effort enviromental elements and rare sightings and interactions. There is the opertunity for these observers to enter data electronically into XL spreadsheets or Access data bases or alternatively we might be able to develop some sort of "Form" front end possibly with Visual Basic of some flavour with the product being in XML.

For me the attraction in the last alternative is that I am not stuck with the high cost of licence fees for Excel and Access for some thirty odd people that may be at sea or in the field.

My concern is how robust is XML. ie how dramatic and or frequent are file corruption issues with XML. How readilly does it interface with Excel, Access and SQL databases? Is it going to be the transmittion file type of the medium future?

In short what are peoples experiences with XML and the excel Access enviroment.

Regards bob

Hello to everyone

Using excel and Crystal Ball I generated a workbook that calculates via VBA Value at Risk and diverse financial charts and ratios.

I have a second workbook, the template, where this values are pasted generating a report.

Up to this point the macro works correctly, the problem is that some of the Crystal Ball charts are pasted as images and I dont know how to "handle" this images.

I reached the point where I can paste the image in the template.

I want the macro to:

1) Detect the images in the template worksheet
2) Determine a position and size for each image

Any ideas on how to do this?

Thanks a lot

Hi all,

I am using =MATCH(TRUE,INDEX(I105:AS105>100000,0),0) to locate the first cell in a row that is above 100000. This works just fine in a regular spreadsheet. However, my spreadsheet uses a Monte Carlo simulation (Crystal Ball) and when letting it run I always receive an error for the cell with this function.

"A calculation error has occurred in cell [my cell]. You may wish to check your formulas and assumptions for division by 0, etc."

I am not sure what the reason could be - I even hard-coded the last cell in the row to always be above 100000. Still the error appears, so it's not likely that it would be triggered by a scenario w/o any number above 100000...

Any ideas?

Hello All -

I have a problem that has been plaquing me and need to consult some experts.

I need to create a price reversal off of an equity index - say S&P 500. In theory, a price reversal is simple; however, I can not figure out a way to make this work in Excel.

A price reseversal has two points (buy / sell) and works like this: If an index falls from -2% from its high, it would be a sell. The sell would hold until the index begins to rise 4% from its low, that would be a buy. At each buy/sell - you have to start the percent changes over again and that is where I am running into the issue.

Here is an example and say S&P 500 just made a new high at 1,000

Starting at A1:

A1 B1 C1 D1
Day S&P 500 Value % change Trade
1 1,000
2 990 -1.0% from high
3 985 -1.5% from high
4 975 -2.5% from high Sell
5 950 Sell
6 945 Sell
7 965 +2.1% from low
8 980 +3.7% from low
9 995 +5.3% from low Buy
10 999 Buy

Bear in mind that in reality I do not know the optimal buy and sell % change reversals. I will have to test for those via Solver / Crystal Ball OptQuest, etc.

I would try writing VBA code for this, but have no idea how to write code for such a function

I have 4 years worth of historical sales (qty & val) by product category. I want to 'predict' what the sales value & quantity will be next year. Can someone tell me the best function to use for this purpose please.

Anyone know of a add in for simulations, like @Risk or Crystal Ball, that work on the Mac?

Years ago, when I was last on a Mac platform, Crystal Ball had a Mac version, but they don't seem to now. I'm back with the Mac and would like to get an add in (doing it without one is possible, just cumbersome enough that I would be willing to pay for an add in).

Anyone know?

I just downloaded Crystal Ball to use with Excel, so that may be part of my

When I copy formulas, Excel won't calculate them correctly. Even if the
formula is correct, the value is the same value as the original cell I copied
the formula from. So, my initial cell will be "=A1*100". The value of A1 is
0.1. The answer is 10, which is correct.

BUT, If I copy the formula down the column, the cells will correctly read
"=A2*100", "=A3*100" etc., but all of the values will read "10", regardless
of what the values in A2 & A3 are.

Is it a settings problem or something? If you have any ideas, I would
greatly appreciate it.



Please help.

I have a spreadsheet in excel 2003 with crystal ball addin. I have written macros one of which defines the forecast cell. When I try to run this macro it is giving the error above. Any ideas on how I can resolve this.


Hey all.

Im really having big problems with my excel simulation and Im hoping one of you might be able to help me.

Simply said, In my simulation i have a decreasing inventory which, at 30 units, increases by one months average inventory. I use Crystal ball to simulate this, and what I want is for CB to take the ending inventory and use it as beginning inventory for the second simulation run, and so on...

How do I do this without creating a circle reference? Is there any way of excel "remembering" the ending inventory and using it as beginning inventory?

Thanks in advance,
Jan Ivar Cz.

My problem is that I have installed crystal ball on my comp. When for example I am trying to do the simulation I want one of the cells to be decreasing gradually by the values given to me by the crystal ball. That is not the case, lets say:

I have given number 3 by the simualtion

Lets say my inventory is 40

I want 40 to be decreasing every time a simulation gives me a number but it is always subtructing from 40

and not 40-3-5-6-7-4-5

it is always 40-3, 40-5, 40-6

what should I change, is there a lock on the cell for that or what?



When working with large numbers the "thousand separator" doesnt work for the whole number, of example:

Original Number: 2800000000

Expected Result: 2.800.000.000

Result Obtained: 2800000,000.00

I hope the separator could be a dot but a comma works just fine.

I am using Office 2007 and the Crystal Ball add-on

Any ideas on how to make this work?

Thanks a lot for your help

Hello Friends!
I need to test various parameter combinations of a model I built in Excel. I have been thinking of using a macro to run through all combinations of say tree cells (e.g. A1 (1 to 20), B2 (1 to 5) and C4(1 to 3)) – all in all 300 combinations in this example. I think I might need to add more parameters to test so I think a macro would be limiting (which would need to be recorded to add the ability to loop through more inputs). I have been thinking of using Monte Carlo simulation software for EXCEL. I have studied Palisade Risk, Frontline Risk Solver Pro and Oracle Crystal Ball and strangely enough NONE of those products allows to test ALL parameter combinations of the input variables in a model. Yes they allow all kinds of the random sampling of the parameter set universe but that is not applicable in my situation where I need exhaustive testing of ALL parameter combinations (at each run a whole set of stats needs to be copied to another sheet). I wonder if there is software that does this exhaustive test of inputs?
I would be happy to hear any suggestion!
Thanks a lot for your time!

I just downloaded Crystal Ball to use with Excel, so that may be part of my

When I copy formulas, Excel won't calculate them correctly. Even if the
formula is correct, the value is the same value as the original cell I copied
the formula from. So, my initial cell will be "=A1*100". The value of A1 is
0.1. The answer is 10, which is correct.

BUT, If I copy the formula down the column, the cells will correctly read
"=A2*100", "=A3*100" etc., but all of the values will read "10", regardless
of what the values in A2 & A3 are.

Is it a settings problem or something? If you have any ideas, I would
greatly appreciate it.



I've been using excel for a while now and a lot of my work deals with trying to use excel to calculate as much as it can but allow me to overwrite the answer if we want to.

My problem is knowing if the cell is a formula or a user input, and if its a user input if I want to go back to the formula how do I do this.

My solution is as follows

A1 = Formula
A2 = User Input
A3 = Hidden cell which says =If(A2="",A1,A2)

All dependant cells refer to cell A3.

Ok I can simply it by not having the cell A1 and put the formula into the A3 if conditional, the problem is then that the user cannot see the calculated value to see what 'ball park' he is in.

What I'm keen to find out is does anyone else do this kind of thing and what other ways do you accomplish the same task.

WillR + Others,

Has anyone ever used the CRViewer9 control to generate reports from Excel?

I've seen Crystal Reports out put from another application and was fairly impressed. Wondered if it could be done in Excel?

Have placed a control on a user form but have no idea what to do next?

[img]C:DevelopmentImagesCrystal Reports.jpg [/img]

[img]C:DevelopmentImagesCrystal Reports.jpg[/img]

Any Ideas / Help would be great.



I created a user form for my job to make logging equipment checkout faster and less typing. I have a text input box, radio buttons, and tons of checkboxes. I have everything exporting to an excel spreadsheet except the checkboxes.
The problem I am having with the checkboxes is that people dont always only check out one thing, but I need everything they check to export to only one cell, either seperated by commas or spaces. Ex: B4 G1 V2 might be the balls theychecked out. How can I do that?

I'm exporting data from Crystal Report to Excel. When I export I'm not
able to see the borders, hence I'm using Excel namespace in C# program for
showing border lines, the following is the code I'm using in the Web
Application (C# language):

using Excel;
string workbookPath = "c:/SomeWorkBook.xls";
ExportOptions export = new ExportOptions();
DiskFileDestinationOptions ex = new DiskFileDestinationOptions();
export.DestinationOptions = ex;
Excel.Application excelApp = new Excel.ApplicationClass();
excelApp.Visible = true;
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0,
true, false, false);
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Sheet1";
Excel.Worksheet excelWorksheet =
Excel.Range cell = excelWorksheet.UsedRange;
cell.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop ).LineStyle
cell.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom ).LineStyle
cell.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight ).LineStyle
cell.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft ).LineStyle
export.DestinationOptions = ex;
excelWorkbook = null;
excelSheets = null;
excelApp = null;

When I use the above code, the Excel application is running in the
background (I'm able to see the process running for that in the Task Manager)
under IUSR_<servername> and as well as under current user who as logged into
the terminal. So whenever I export data, that many times the Excel
Application are running in the process.

How to solve this problem? Pls help me out...

How Excel can be used for generating End user customisable reports. Data needs to be fetched from oracle / SQL server database based on some query and reports needs to be generated in Excel.

I want to design a solution for end user (who has knowledge of Excel & database query writing) for Report generation as per his changing requirement, so that he has no dependancy on development team for day today modification in report requirements.

I designed this type of application using C# & Crystal report earlier, which requires three things :
1. Crystal report template having database query
2. filter criteria (on which data will be read from database).
3. Dynamic User control (e.g. text box, drop down etc) generation component as per filter criteria Control required (defined in database table by end user).

If End user is acquainted with Crystal report designing then he can design his own crystal report template along with database query embedded in crystal report only. My software generates dynamic user controls as per defined in database for that report template. These filter criteria are passed to query defined in Crystal report so that desired report can be generated as per his filter criteria. This helped him in catering day today reporting related changing requirements.

Now I want to design similar solution using Excel i.e. replacing Crystal report template with Excel template.
My initial proposal is like this that I will replace crystal report with excel template having few fixed parameter & few variable report parameters (which will come from database as per query), rest I will use from my earlier solution.

I don't know
# how to define report parameters in Excel ?
# how to generate multi page report (when database returns dataset of more than one record) ?
# how to paginate report like crystal report does when results are to be displayed on multiple page ?
# how to define Report header & footer area in Excel to be displayed on multiple page report ?

I know that these things can be done using macro programing(defined in that Excel report template) but that will not be easy for end user, I want to have a simple generalised solution which I can implement once in my c# program so that end user (having little excel knowledge) can design / modify report as per his need without having much trouble of programming, like I did in my previous solution (c# & Crystal report).

Thanks in advance for sparing yours valuable time, support and guidence.

Gopal Parikh.

I have recieved a great deal of outstanding advise and help from this forum! And I trust the people here to voice an honest (and informed) opinion. So I hope someone will have time to help me out with this.

Do any of you have an opinion of the software program"Crystal Xcelsius"?
Is it worth the 5 hundred bucks or can you pretty-much do it all with VBA?
If I create a workbook with Crystal Xcelsius features can a user who does not have the software still use the workbook?

Thanks for your time and your advise!!

I think a macro will do the trick, but I am not experienced enough to write one up. Here's what I'm looking for.

Variables: Need a pop-up box to enter any number of text strings (DATA1, DATA2, DATA3, etc.) and another pop-up box to enter a percentage in numeral form (PERCENTAGE1, PERCENTAGE2, PERCENTAGE3, etc.) There has to be a PERCENTAGE variable for every DATA variable and the sum of all the PERCENTAGE variables must equal 100 exactly.

Execution: The macro will randomly fill cells E3:E1002 with each DATA string. The number of cells filled will be equal to the PERCENTAGE multiplied by 10 entered for each DATA string. (NOTE: The PERCENTAGE number is multiplied by 10 because there are exactly 1,000 cells to be filled. This range of cells will never change.)

For example, we have the following entered by the user at the opening of the workbook.

DATA1 = Apple - PERCENTAGE1 = 65
DATA2 = Ball - PERCENTAGE2 = 26

Based on this user-entered information, the macro would determine the sum of the PERCENTAGE values does equal 100 then would randomly fill "Apple" into 650 (PERCENTAGE1 X 10) empty cells between E3:E1002. Then, it would fill "Ball" into 260 of the remaining empty cells in the range E3:E1002 and finally fill "Cat" into the remaining 90 empty cells within that range.

Ideally, the macro would sum the PERCENTAGE values and return an error message if the sum does not exactly equal 100. Also, since the number of DATA strings is not constant, there would have to be a way to tell the macro the user if finished entering DATA strings. Perhaps the user entering an empty DATA string, or something similar.

This seem feasible? Doesn't sound too complicated for an advanced VBA writer, but I'm just not that talented.


No luck finding an answer? You could always try Google.