# Computing Erlang C Formula Within Excel With No Addin?

Jan 5, 2012

I am trying to set up a spreadsheet that will have a calculator where variables can be modified in order to properly see staffing requirements using the Erlang C formula. I have all of the known variables, but I can't seem to figure out how to calculate the formula to get the numbers I'm expecting.

[URL] is the website I'm using currently, and I'm stuck at the actual Erlang C formula. I'm not very good at math, but here is how I am reading the formula in English (I'm using the given numbers on the site for the variables so I can check my work).

Erlang C = calls per second to the power of agents staffed divided by the factorial of agents staffed. The result is then divided by calls per second to the power of agents staffed divided by the factorial of agents staffed again, that result is added to 1 minus occupancy, that result is multiplied by the summation of 0 to 54 (54 being the result of 55 agents staffed minus 1), which is then multiplied by calls per second to the power of "k" (i haven't yet figured out what k should equal, i assumed zero from the summation) divided by the factorial "k".

The result I get is not even close to what the website shows as the answer for Erlang, which should be .239. Am I making faulty assumptions anywhere? It's entirely possible I'm not computing the formula properly, it's been about 12 years since I've taken a math class, but since I know all the values for the variables (except for "k"), I should be able to write out a simple Excel formula to compute this.

## Computing Commission Formula

May 11, 2007

determining a formula to compute a sales commission.

Here is a sales scenario.

A \$25 commission will be paid on sales between \$50 to \$150.
A \$50 commission will be paid on sales between \$151 to \$300
A \$75 commission will be paid on sales between \$301 to \$600

The sales person will enter the sale amount into column B. Column C should compute the total commission for multilple sales.

Example:

Column A Column B
Sales Commission
\$50 \$150 (which is the comm. for the combined sales)
\$175
\$360

Dec 30, 2008

I have an addin in a shared folder which was created by someone else. (it is a user form). I need to create a macro which will go to its existing folder and then save it in the users personal addin folder also picking up their user id. There will be about 100 users so easier this way than going round doing it for them.

So I suppose the query is three fold.

1, pick up the addin and save it in a personal addin folder.
2, Find the username as part of the path file. For instance my personal would be:

The "O033116" is where I will need a search completed to obtain the user id.

The reason I want it this way is so the user is unable to access any of the code and we are in control.

May 23, 2012

I have an Excel Addin that I deploy as a read-only addin on a server share. I instruct my co-workers to add the Add-In as they would any other Add-In, but I tell the to answer NO to the Windows question "Copy 'AddinName' to the Addins folder for John Doe?".

That way, everyone will be working with the server version of the addin, and any time I want to roll out an updated version I just place a new addin file on the server. The next time the users restart Excel, the new version is loaded.

How do I disable the "Copy Addin" question completely? I don't want to depend on the user. I know this is possible because I have this 3rd party addin that we use that is installed from c:Program Files and it just stays there and you're never asked to copy the addin when you install it in Excel.

## Excel 2007 :: Computing Last Term Determines Score?

Apr 23, 2012

I'm using excel 2007 for analyzing my students grades. I ran two exams and look for a way to compute a final grade score, which should be equal to the grade of the first exam for those students who took just the first exam and should be equal to the second score for those students who took the second exam. (Clarification: for those students who took both terms, their final grade should be equal to their score in the second term).

## Excel 2010 :: Call Function In Another AddIn (XLAM)

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)

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)

## Computing Annual Maxima

Apr 4, 2009

I have daily streamflow data for a large number of years. One column is the date and the second column is the streamflow value. I want to compute the maximum value and the 3-day maximum (largest average value for any consecutive 3-day period)for each water year (starts on October 1 and ends on Sept 30). Of course the number of days each year varies by one during leap years.

## Computing Business Hours Between Dates?

Jan 9, 2014

computing the number of business hours between 2 dates.

Given the following information:
- Business Hours: 8am -5pm (8:00-17:00)
- if entry queued falls on a weekend or beyond the business hours, it should count the first hour on the next business hour.

Example: scenario 1 = Starts Sunday, 10am and Ends Monday 9am; count of hours should be 1
scenario 2 = starts on Monday 6pm and ends on Tuesday 10am; count of hours should be 2

## Macro Computing Planetary Position

Sep 14, 2006

Im looking for an Excel macro that will allow me to input any date and time and produce x,y,z co-ordinate results for planetary positions relative to the centre of the universe. I already have a database with ephemeris data but find it difficult to use manually inputting date and time constantly. I need to perform so many calculations that automating this procedure is essential to me but i dont know how.

## Computing The Date Of The Next Occurring Specific Weekday?

Feb 18, 2014

My spreadsheet opens. Cell A1 determines what today is, formatted as "weekday, day month year" (e.g., "Tuesday, 18 February 2014").

I have another cell in which I typically enter the date for the coming Friday. Instead of updating this field once each week, I'd like to calculate the date for the next coming Friday based off the value in cell A1 (to be concise, if "today" happens to be a Friday, then it would calculate the date of next Friday). (Continuing the example above, the coming Friday would be computed as 2/21/2014. If I open the spreadsheet on 2/21, it would calculate 2/28.)

What formula or step(s) to follow to render this value automatically?

## Computing With Last 252 Rows Of Varying Length Column

Nov 26, 2009

Using Excel 2003, Column E has a varying and unpredictable number of rows, which only the final 252 rows are of significance. I need to compute the following formulas, here written in English as I can't determine the proper terminology to accomplish this task in Excel:

Final Row with a number in Column E/average (final 252 rows with numbers in column E)+

Final Row with a number in Column E/average (final 126 rows with numbers in column E)+

Final Row with a number in Column E/average (final 63 rows with numbers in column E)

Once again, what makes this not straightforward for me is the column may have wildly varying numbers of rows.

## Computing Monthly Standard Deviation From Daily Data

Apr 7, 2014

I have to compute monthly standard deviation (volatility) from daily data.

The problem consists on the fact that the days during a month are not constant and change over time.

I attach the excel file : Price_Time_Series.xlsx

In this xls file, you can find dates in column A, price in column B and returns in column C.

I want to compute standard deviation at the end of the month for stock returns (column C), by considering all available days.

As you can note from the file, the days change over time and they are note constant. How can I compute standard deviation of them?

Nov 18, 2008

I am trying to protect an addin I have. I can protect the shared woorkbook, but when I save it as an addin the code is visible to the addin.

Jul 28, 2009

I have written a class that can be used as a replacement for Application.FileSearch in Excel 2007, that will take the same parameters etc. with no, or minimal code adjustments.

How do I now alter this so that it can be used as an AddIn by others?

I think that I have to remove all the sheets apart from 1 from the document, and save it as an .xla, but I am struggling with making this an addin, and then being able to use the addin from other code.

Does anyone have some instructions or a link to a website that could direct me? I have done a google search, but search for Excel, VBA, and classes (as well as AddIns), just gives lots of links to classes (as in teaching).

Jan 18, 2007

I did modify some codes inside my addin, then did save the modified addin file by pressing the save icon from the VBA window..........but i found that the addin file was saved without the modification made to it. so i lost all modification did to the file. i donot know what i did wrong ?

May 11, 2009

I'm stuyding the book "Professional Excel Development" which has a Project Template and an addin with several modules and a worksheet. I can view the addin's modules but not it's worksheet - is this normal for addin's? I haven't worked with them much before.

Jul 7, 2009

Basically, I want to convert my Personal.xls VBAProject to say, MyMacros.xla so that I can then create a menu bar item with those addin Macros listed... I've got the code for that part ...

## Addin Worksheet Function In VBA

Mar 20, 2008

I have found an addin (http://www.quantdec.com/Excel/smoothing.htm) that adds a worksheet function smoothing data. It operates with 2 arguments:
- a range for the input data to smooth
- a parameter detailing how to do the smoothing.

It outputs a matrix of smoothed results, and therefore requires the Ctrl-Shift-Enter on a spreadsheet. Until there no problem, the function works properly. However I need to repeat the process about 100 times. Therefore I extended my ranges to duplicate the calculations, and that also worked fine. But because I don't need the intermediate data (100 columns by 1000+ rows) and I don't like to wait 2 minutes for the calculations to finish each time I open the saved file again, I thought I could just use VBA and only output the final results.

This is how the working syntax is in a cell:

And there the nightmare starts :
- I cannot apply it even once: I get a type mismatch whatever I do to calculate the range and put it as an argument of the function. I used a string to store the range name and use it as is (B3:B1032 or even "B3:B1032") or with the Range function Range("B3:B1032"). I even tried to put manually "B3:B1032" with the same result. First major issue

- Because the worksheet function requires a range, I cannot use an array, or at least I tried without success. Therefore I need to output the result on the worksheet each time it is processed, and reread it through the formula, for n times the loop is running for... Kind of inconvenient... So I tried to modify the original smooth() function to create a new one that would accept an array as argument instead of range, but with the same consequence: Type mismatch when I feed the new function with an array (which I checked contains the data to process).

I have tried an example I found somewhere using a built-in Excel Worksheet function MMult and it worked with ranges:

Sub SomeSub()

Dim xArray As Variant, yArray As Variant, zArray As Variant
Dim Fn As Object
Set Fn = Application.WorksheetFunction

xArray = Range("A1:B2").Value
yArray = Range("D1:E2").Value
zArray = Fn.MMult(xArray, yArray)

ActiveCell.Resize(2, 2).Value = zArray

End Sub

Jul 8, 2008

I have workbook which automatacally 'installs' an addin, i.e. ticks the box.

When I open the workbook without Excel running, the code fails to install the addin. When I then use Tools > Addins, the panel of addins is completely blank.

I can only restore the addins list by closing Excel and reopening it.

## VBA Message When Activating Addin

Nov 7, 2008

I have an addin "My Menu" that runs several routines. These routine require the sheet to be converted to text "Sub text ()" no problem so far. But I want to warn the user that the sheet is about to be converted to text.

Is there a VBA way of displaying a message box when "My Menu" is clicked on that says "Sheet will be converted to Text OK ?"

Aug 1, 2006

how to automate AddIns sequence when opening Excel? I have 5 different AddIn (xla) and I have to launch them in a specific order (after the menu Essbase is installed - with xll) cause I'm adding rows in that menu. Excel is launching my xla's in alphabetical order but before Essbase is installed.

## Force An Addin To Be Installed

Mar 26, 2007

Is it possible to force an addin to be installed in Excel 2003 using VBA? I have a spreadsheet that uses the solver.xla addin in a vba macro. It works fine on my machine, where I have manually ticked SOLVER in Tools>Addins and manually added a reference. However, it does not work when I send to someone else. It seems that the addin is not being installed. I have VBA code that opens it:

On Error Resume Next
s = Application.LibraryPath & "solversolver.XLA"
Workbooks.Open filename:=s

but even with this, i get a "Unexpected internal error occurred, or available memory as exhausted" when I try to run the following

On Error Resume Next
ActiveSheet. Unprotect
Application.Run "Solver.xla!SolverReset"
Application.Run "Solver.xla!SolverOk", "\$D\$46", 2, "0", "\$L\$13:\$L\$14"
Application.Run "Solver.xla!SolverOptions", 100, 100, 0.000001, False, False, 2, 1, 1, 0.05, True
Application.Run "Solver.xla!SolverOk", "\$D\$46", 2, "0", "\$L\$13:\$L\$14"
Application.Run "Solver.xla!SolverSolve"
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios _
:=True

The one way to make it work is manually going into Tools>Addins and ticking it. This is confusing me as the above, along with the code that opens it, should not even require a reference, but it doesn't work without it

Jun 19, 2007

I am having a strange error occur. I am trying to debug a function in an add-in I developed, but whenever I try to hover over a value or add a watch, the whole application crashes. It runs without crashing when I don't try to debug it.

## Delete All Names For Addin

Aug 1, 2007

I am trying to write a sub for an addin to delete all names in the active workbook. The code that I tried is below. I run the sub and the messageboxes appear, etc., but none of the names are deleted. I have no idea what is going wrong. I have looked at the earlier related posts and have not had any luck.

Sub namesdeleteall()
Dim intConfirm As Boolean
Dim NM As Name

intConfirm = MsgBox("Are you sure you want to delete all name?", vbOKCancel, "Delete All Names??")

If intConfirm = 1 Then

For Each NM In ActiveWorkbook.Names
NM.Delete
Next

End If

End Sub

## Custom Tab Not Appearing When Distributed As Addin?

Apr 23, 2014

I created an addin with CUSTOM tab (used custom UI editor) to manipulate the ribbon xml.It works fine at home computer and @ work I distributed and install in user> addin directory and activated using excel options> addin>

But the ribbon I created does not appearing (tried on couple of computers) no luck

## Function Runs Within Workbook But Not As AddIn

Feb 6, 2014

When running the following code for NSLookup in a module within the current workbook, it runs fine but when I put it in a XLAM file and try and use it as an addin I only get #NAME? as a result. I believe that that addin is installed properly because I have a second module with a sub routine in it (GetIPStatus) that is working, and when entering the command =NSLookup(A2) in a cell it shows up properly like it's an available function, but it does not seem to execute. Do I have to do anything special for a function to run as an addin?

## How To Create Addin And Feed Macro

Dec 29, 2012

I want the following macro to be used as an excel addin, how to create addin and feed this macro on that particular addin so that its available each time I open a new workbook.

The macro is : [Code] ...........

## Copy A Workbook Functions From Addin

Mar 29, 2009

I have a addin and I ahve some code wriiten in the Workbook_BeforeClose event. On click some "Copy Sheets" menu option in my addin it creates a new workbook and copies some sheets from the addin to the new workbook.

But I want somehow have the code written in Workbook_BeforeClose event of my addin to be copied too...Since I am coping only the sheets , workbook events are not copied.

## Jumps To UDFs In Addin Module

Jan 20, 2013

When I Step through (Using the f8 Key) the below code -- Comments Explain my problem/Question

Code:

Sub SetUpTable()
Worksheets("Sheet1").Activate
Application.Calculation = xlCalculationManual ' Without entering this line the Macro in the next line or two), jumps to and begins running a UDF in a VBE ADDIN
'Module marked as Volitile
For TheYear = 1 To 5
Cells(1, TheYear + 1).Value = 1990 + TheYear

[Code]....

## Uninstall Addin Without Firing The Events In It

Sep 23, 2006

I want to uninstall an addin from another addin. The problem arises when the addin i want to uninstall contains events similar like auto_open and workbook_addininstall
It seems that they get triggered even when i close the addin That way i loose controll over the programsequence. And that is the thing i don't want. If someone has a solution, that would be great. Otherwise i have to reorganize everything and merge two addins in to one And i really don't like all the extra work