Calculate Large Binomial Coefficients
Dec 29, 2007
I'm trying to do a binomial distribution summation as part of a VBA function, and have been using the Application.WorksheetFunction.BinomDist function, which works fine until the numbers get large - the binomial coefficients used in the calculation end up being larger than can be held in a double floating-point number in VBA, so excel can't handle it. The final result of the calculation is a probability, so it's not a huge number! I was wondering if anyone knows an alternate way of calculating binomial probabilities which avoid any huge number intermediates.
View 7 Replies
ADVERTISEMENT
Jul 17, 2014
i have a function in a cell (that works) to extract coefficients from a range of cells in a workbook:
VB: =INDEX(LINEST(CP25:CP27,CQ25:CQ27^{1,2}),1)}
i have variables for cp25:cp27 and cq25:cq27 already defined in my vba code. the values for these in the case i am working on are as follows (returns 110.5):
cp25 = 560
cp26 = 570
cp27 = 580
cq25 = .414
cq26 = .479
cq27 = .536
1) how to do this function in VBA only - this is part of a UDF and cannot have any helper cells
2) how to refer to 560,570,580 as a 'range'. is there a way to put these six variables into my ranges for later processing?
All of the google searches i have deal only with linear regression, taking from existing graphs, or say to just use the function i have above.
I have tried
VB:
Var = Application.WorksheetFunction.LinEst(Sheets("references").Range("CP25:CP27"), Sheets("references").Range("CQ25:CQ27^{1,2}"), 1)
[COLOR=#333333][/COLOR]
but return #value! errors. when i remove the ^{1,2} portion, i do return a value but it is incorrect (returns 160), what is the correct syntax for adding in the ^{1,2}? if you do that would be fantastic, but brings me back to issue #2 in that i need to refer to my variables in the vba code and not this range (as they will eventually be going away).
View 5 Replies
View Related
Oct 6, 2011
I have 3 columns (B,D,F) that have 15 minute data intervals and I need to calculate the hourly averages for each column but here is the catch, I need to do this from July-1-2011 00:00:00 to Sept-31-2011, 24:00:00 (that's 26,217 data points and 6,554 =average() calculations......
IS there syntax I could use that would calculate the average every 4-cells so I could get the hourly average without having to type =average(XX:XX) 6,554 times?
Example of set-up:
29-Sep-11
23:00:00
0.820412164= AVERAGE(B8735:B8738)
0.762574274 =average(D8735:D8738)
84.98389991 =average(F8735:F8738)
29-Sep-11 23:15:00 0.819168591
0.762781167
[Code]...
View 6 Replies
View Related
Nov 25, 2013
I am looking to calculate variance across a large data set and would like to know if a macro is possible to calculate for a specific unique cell ID. East, Central, or West and calculate variance across that region.
For instance, in my data set if I have something similar to below. How would I calculate variance in the different regions? Is it possible to automate this process? Also could the Analysis ToolPAk be used instead or in conjunction?
OrderDate
Region
Rep
Item
[Code]....
View 4 Replies
View Related
Sep 10, 2008
I have been working on a problem that requires being able to calculate the chances of any winnng a series of coin tosses with a weighted coin.
The coin has for arguments sake a 65% / 35% chance of landing heads or tails and to win the game you need to hit your side 9 times.
Can you use excel to calculate things like: If heads takes the lead 1-0, what does that mean the its overall percentage chance of winning? If the score is 7-3 to tails, what are the chances of either side gaining victory? etc, etc.
I thought that there would be a way of setting up a spreadsheet that listed the goal (first to 9, 12, 15 or whatever) and below this the percentage chance of heads and the percentage chance of tails. Then somewhere would be a box to enter the current score (0-0, 3-5, 1-1 or whatever) and beside this the current chance of heads or tails gaining victory.
I've tried setting up something like this but am a bit of a dufus when it comes to knowing how, where and when to use the different functions of excel.
View 13 Replies
View Related
Nov 27, 2006
There is trading system which generates a winning trade per ONE stock in ONE month’s time with a probability of 5/12 (0,4166%). If the system trades 10 independent stocks the probability of any number of winning trades occurring out of all of them in ONE month is shown below:
None 0,0046
One 0,0326
Two 0,1047
Three 0,1995
Four 0,2494
Five 0,2172
Six 0,1272
Seven 0,0519
Eight 0,0139
Nine 0,0022
Ten 0,0002
I took these values from a book…I tried to reproduce these values in the spreadsheet that I attached. My table looks as follows:
0 0,0046
1 0,0033
2 0,0023
3 0,0017
4 0,0012
5 0,0008
6 0,0006..........
View 2 Replies
View Related
Mar 9, 2007
I have a VBA function that calculates polynomial coefficients for a series of data pairs. One selects the range of cells that the coefficients are to be stored in, and enters the polynomial formula:
{POLFIT(Xa, Ya, N)}
Where Xa is the array of ordinate values, Ya is the array of data values, and N is the polynomial order to be fit.
It is obvious that one needs to select at least N+1 cells when the array funtion is typed in. But, it is easy to select too few cells.
I am looking for a way to test whether enough cells were selected for the range formula: The function declaration is
Function POLFIT(Xa, Ya, N As Integer) As Variant
Various means I have tried to count POLFIT do not return the correct value.
View 7 Replies
View Related
Apr 8, 2008
I'm working on a problem that calculates data using a binomial distribution. The data derived from the binomial distribution is then used to calculate a cost. I would like to minimize cost by changing the number of " reservations". Can excel solver do this or is it too complicated? I have attached the file with what I'm working on. (Changing E1 to minimize E2 while Cells A9:A102 are calculating a binomial distribution)
View 7 Replies
View Related
Oct 14, 2008
I I have a excel document which has a sheet containing a large number of array formulas.
I turned the automatic calculation off.
However, even when I press F9 to calculate the cells it just crunches, gets stuck at “Calculating 0%” and then crashes.
The majority of the array formulas are IF queries with multiple criteria.
Would it speed up the calculation if I didn’t use array formulas?
View 2 Replies
View Related
May 23, 2007
to getting accurate regression coefficients from a dataset with *small* (standard) numbers, which contains centered predictors. More specifically, I have a dataset with 18 observed data points containing a criterion (y), a centered predictor variable (x), another centered predictor variable (z), and the interaction of the two centered predictor variables (xz). This multiple regression equation is structured to test for interactions between the two continuous predictor variables (x and z) as prescribed by Aiken and West (1991) in their classic book.
When I run the regression in Excel with the centered predictors, some of the regression coefficents in the output are estimated to be 0, although they are clealry *not* 0 as estimated by SPSS 14.0.2. I have spent many hours troubleshooting this problem (and searched many forums on the internet) and still do not know why this is happening.
Initially, I thought the problem might have to do with the cross-product of the centered predictors, but even just doing a regression with one of the centered predictors (for certain centered predictors) yields a regression coefficient of 0 (although it should be non-zero as per SPSS 14.0.2). When doing these multiple regressions with non-centered predictors, all regression coefficients are estimated accurately.
I was wondering if anyone had any insights on why I am experiencing these problems.... If anyone wants a sample of some test data I have used to troubleshoot these problems, you can download a file from: [url] and/or email me at (email deleted by Mod) for more datasets or questions.
View 5 Replies
View Related
Mar 6, 2014
We are trying to find the median of a large set of numbers to calculate the median income in 2010. For an example we have 8,379k people with $2500 average income, 9,783k with $7500 average income and so on. How can I calculate the median average income of such a large amount of entries?
View 8 Replies
View Related
Aug 6, 2003
I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).
Is there a way to split ( ) this file so I can open two files instead?
View 9 Replies
View Related
Apr 29, 2014
I have 5 sheets all with different data within the H column. I want to find the top 10 highest results across all 5 sheets and then pull the worksheet name where those results come from. It would be two separate formulas, one for the LARGE value and the second for the name.
This is all I have.
O2:O11
=LARGE(Sheet1:Sheet5!$H$3:$H$102,1)
to
=LARGE(Sheet1:Sheet5!$H$3:$H$102,10)
I then have this which was the best I could find after about 30 different formulas.
=MATCH(TRUE,COUNTIF(INDIRECT("'"&{"Sheet1","Sheet2","Sheet3","Sheet4","Sheet5"}&"'!$H$3:$H$102"),O2)>0,0)
It gives me the right worksheet but it only gives me a number. I'm assuming it's because of COUNTIF but I'm not sure what else to use.
Most examples I see have the names they want right beside the values. So in my case H3 is 22 (and the largest value within H across all sheets) and if I followed their examples G3 would be Sheet1. So because it's side by side it'd be easily pulled. But the only name references I have are the worksheet name itself or A1 of Sheet1:Sheet5 so I'm not sure how to rework their formulas to suit mine.
View 4 Replies
View Related
May 1, 2009
I need to create a "Top 5" list based on two columns. The example is as attached (the real list is approx 22 000 lines):
I need to find the top 5 largest quantites (column A) for each flag catagory (column D) and include the part number and description. I have tried a pivot table and had some issues. Also tried a LARGE(IF...) type formula and still no luck. Does anyone have any ideas of what is the best way to do this?
View 12 Replies
View Related
Jul 14, 2009
I'm trying to create a mini-table that will give me the 3 highest and lowest values in a range (I know how to do this using LARGE and SMALL functions), but I also want to get the corresponding name (in column A) for each number. This last part I don't know how to do.
I've attached an example sheet that I hope gives a basic idea of what I'm trying to do. I'm looking for a method to fill in the data for cells A13:A15 and C13:C15 in the attached sheet.
I could modify my worksheet a bit and probably get the result I'm looking for, but I'm hoping to come up with a nicer solution, and hopefully learn Excel a bit better in the process.
View 2 Replies
View Related
Sep 4, 2009
I don't know if this is possible but thought i'd check. Could I put together a look up that pulls through the 5 best performing categories' so say i have 200 categories, just to pull on to a report page the top 5 and their result, and produce a report weekly so if the top 5 change it updates this?
View 2 Replies
View Related
Oct 7, 2006
I am having a problem with a workbook, I have copied all cells and pasted Special (Values only). But for some reason the workbook is 38 Meg's. The workbook only contains 6 worksheets with about 60 rows and 32 columns with figures and no formulas. I have tried to find formulas by Goto/Special/Formulas and Excel has found nothing. What shoud I do?
View 4 Replies
View Related
Aug 24, 2014
I was doing this task using sorting then adding column today morning one of my friend told i can do it with formula only, no need to using sort and adding new column
I just attached the sheet : without sorting.xlsx‎
View 5 Replies
View Related
Jan 6, 2014
I have a set of 5,800+ data points between 0 and 1 that I would like to multiply together. When I use PRODUCT for the whole set, the formula returns 0. However, I can use a smaller subset of the data to return a very small number. I'm curious if Excel has a closest-number-to-0 or number-of-cells-for-PRODUCT limitation. Is there another way to perform this calculation?
View 4 Replies
View Related
Aug 20, 2014
Say I wanted to concatenate a row that had 10,000 values in it. How could this be achieved without having to click 10,000 cells while typing a comma in between each one?
View 5 Replies
View Related
Aug 17, 2007
If I have the numbers 1-50 repeating in columns, 1-50, 1-50 etc is there a way to determine that number "1" falls 1st, 51st etc in the sequence without doing a manual count? Or that the number 2 falls 2nd, 52nd etc?
View 10 Replies
View Related
Mar 24, 2008
i have this formula below which works but if the criteria is not there it says FALSE because of the B22:C45,2,0) bit i would rather it says something like "no dissertation" is that possible? ...
View 10 Replies
View Related
Jun 19, 2009
I have a column of numbers in A1:A200 that I need to concatenate with a ";" in between each into one cell. Is there a way to do this without clicking in each cell individually? =concatenate(A1:A200) just returns the value in cell a1.
View 2 Replies
View Related
Jan 8, 2010
I'm having a difficulty using LARGE and SMALL.I want to return the 1st to 5th value of an array (each in a different row) choosing months as criteria. While the LARGE formula works fine, when I replace the LARGE() formula for the SMALL() leaving everything else unchanged, the formula returns ZERO and there are no ZEROS in the original data! Here is the function:
View 4 Replies
View Related
Mar 16, 2013
my colleague did something to one of our files and it is now 845918 kb. I transferred it to a flash drive and then to my hard drive but now I cant open it to see what is wrong. Is there a trick to opening this up? When it was opened on her computer it wouldn't let me add any tabs or move tabs. I think I unprotected it but that didn't change anything.
View 1 Replies
View Related
Oct 11, 2013
I have a list of domains (about 200,000) that looks like this:
I have a list of urls (about 1 million) that looks like this:
I want my results to only show this (since this is the only thing in the second file that does not contain anything from the first file):
I have tried programs designed to do this but they either (a) shorten everything down to the domain or (b) wont remove anything since the domains are part of a longer url in the second file.
View 3 Replies
View Related
Oct 15, 2008
I am using following event macro and it repeats itself more than 300 times. I am at a stage where I am getting a message "Compile Error, Procedure too large", I tried to break it into two but that's not working.
Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
Dim lng As Long
Application.EnableEvents = False
Select Case Target.Address
Case "$E$3"
If Target.Value = "Yes" Then
SelectNumber:
lng = Application.InputBox("Please enter number 0 to 100", , , , , , , 1)
If IsNumeric(lng) = False Or lng < 0 Or lng > 100 Then GoTo SelectNumber
Range("G3") = lng
Else: Range("G3") = 0
.................................................(above statements repeat more than 300 times for different cells)..............................
Application.EnableEvents = True
End Sub
View 15 Replies
View Related
Nov 25, 2008
I'm using Excel 2007 and the sheet has 238536 rows containing 26412 ranges.
The first 5 rows in each range contains data. I wish to input into the sixth cell of every range a formula. Not essential but I'd prefer it to be formatted blue.
I attach short s/s to illustrate my need.
View 2 Replies
View Related
Jan 4, 2013
I am building a database in excel where I have the number of days across the top (horizontal) and in Cell A2:A300 I list our product lines (that is repeated based on different divisions). Beside each product line in row b3:iv3 I have sales data for each working day.
In a new tab I want to formulate the total of ProductA on day 240.
A B C D E F G H
1 Region Product 235 236 237 238 239 240
2 RegionA ProductA 25 31 15 18 10 5
3 RegionB ProductA 21 13 10 5 28 10
4 RegionB ProductB 21 13 10 5 28 10
5 RegionC ProductB 21 13 10 5 28 10
6 RegionA ProductA 25 31 15 18 10 5
7 RegionA ProductC 25 31 15 18 10 5
In another Tab summary of Product Totals:
Day235 Day236 Day237
Product A 76 75 40
Product B 42 26 20
Product C 25 31 15
I tried many formulas for example:
=SUMIF('2012 Sales'!$C$3:$IS$113,$C1&$F$4)
View 9 Replies
View Related
Jun 6, 2013
i was wondering if there was a way i can combine these two formulas into one large one
=IF(AF8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($L$3:$L$103)-ROW($L$3)+1)/($L$3:$L$103=AF8),COUNTIF($AF$8:AF8,AF8))))
=IF(AG8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($M$3:$M$103)-ROW($M$3)+1)/($M$3:$M$103=AG8),COUNTIF($AG$8:AG8,AG8))))
View 2 Replies
View Related
