Archive for the ‘Math and Stats Functions’ Category.
I’ve received a couple of trig questions lately. I liked trig in high school and college, but I can’t say I put it to use much. There’s only one thing that you need to know about Excel and trigonometric functions: Everything is in radians, not degrees. If you do anything trig-related in Excel, you have to convert to degrees (unless you want radians, of course).
Here’s one problem:
In this example, Peter knows a fixed point on a plot of land. He also knows the coordinates were the corner of a building will be when it’s built. He wants to figure the angle and distance from the known point to the corner of the building so he can point his theodolite at it.
I had to look up theodolite, and it turns out I’ve been calling it a transit all these years.
Finding the distance is easy, thanks to old Pythagoras and his right triangle formula, c2 = a2 + b2. The formula in B4 is: =SQRT((B1^2)+(B2^2)). That one I didn’t have to look up. The rest of of the trig functions weren’t so quick to return to me.
Next I needed the angle. Cell B5 is =DEGREES(ATAN(B2/B1)). Note that I didn’t forget to convert to degrees, since the ATAN function would return radians. To refresh my memory, I used this Trigonometric Formula page.
Finally, I turned that fractional angle into minutes and seconds, thusly:
B6: =TRUNC(B5)
B7: =TRUNC((B5-B6)*60)
B8: =ROUND((B5-B6-(B7/60))*60,0)
Now Peter can point his theodolite (or whatever you do with those things) at 75° 57′ 1” and 6,185mm away will be the start of the new building.
J-Walk recently held a raffle and he describes his procedure for selecting the winner. It involves Excel, but what would you expect from Mr. Spreadsheet?
The interesting part, to me, was step nine:
9. I will press F9 five times to regenerate the random numbers.
He put =RAND() in a column and intended to sort based on that column. F9, you’ll recall, is the shortcut key to recalculate a worksheet. But why recalculate five times? Does that make the numbers more random?
The reason that it interests me is because I’m guilty of the same thing. A fantasy football league I ran a few years ago had a rule that called for a raffle if two or more teams wanted the same player. Each team was allotted a number of chances based on their ranking to give the lower ranked team a better chance. If the second ranked team and the seventh ranked team were in the raffle, the second ranked team would get two of the nine chances and the other team would get seven of the nine. I used a similar set up as John in that I used =RAND() in a column and sorted. I only recalculated three times though, not five.
How many times should you recalculate? The answer is none. The number returned by =RAND() is as random as it’s ever going to get when you enter the formula. John could have entered the formula in the first cell, filled the formula down the column, and sorted and the list would have been as random as Excel can muster.
Why isn’t that enough?
30th June 2005, 08:40 pm by
Vasant
In one of my (many) previous lives, I wanted to be a mathematician.
Even after I realized that being a mathematician would preclude a lifestyle that I wanted to become accustomed to, I still retained an active interest in mathematics. One of the areas that I found fascinating involved prime numbers.
I've seen many UDFs in the newsgroups, mostly provided by Excel MVPs, to determine if a number is prime. However, the following is the shortest UDF that I've been able to come up with:
Function IsPrime(Num As Single) As Boolean
Dim i As Long
If Num <2 Or (Num <> 2 And Num Mod 2 = 0) _
Or Num <> Int(Num) Then Exit Function
For i = 3 To Sqr(Num) Step 2
If Num Mod i = 0 Then Exit Function
Next
IsPrime = True
End Function
The reason for declaring the input number Num as a Single is that if it's declared as an Integer or a Long, an input value of, say, 3.2 will be coerced to 3 and be evaluated as a prime number. Clearly, this is not the desired result. There is probably a better way of handling this, but I can't figure it out.
I'd be interested in some comments as to whether this code can be shortened or made more elegant.
This last part focusses on replacing some of the remaining functions of the Analysis Toolpak Addin (ATP). Reference to the other parts of this article series:
Other ATP functions
| ATP Function |
Description |
ATP Syntax |
Replacement Formula |
Array formula |
| DELTA |
Test whether two values are equal |
=DELTA(number1,number2) |
=--(number1=number2) |
No |
| ISEVEN |
Returns TRUE if the number is even |
=ISEVEN(Number) |
=MOD(TRUNC(Number),2)=0 |
No |
| ISODD |
Returns TRUE if the number is odd |
=ISODD(Number) |
=MOD(TRUNC(Number),2)=1 |
No |
So that is it. For the remaining ATP functions I currently have no replacement formula. If someone could provide corrections, additions I'm happy to amend the above tables accordingly.
Hope you find some of these formulas useful.
Frank
This part focusses on replacing the numerical system conversion functions of the Analysis Toolpak Addin (ATP). Reference to the other parts of this article series:
Numeric System Conversion ATP functions
| ATP Function |
Description |
ATP Syntax |
Replacement Formula |
Array formula |
| BIN2DEC |
Converts binary number to decimal |
=BIN2DEC(Number) |
=SUMPRODUCT(MID("0"&Number,ROW(INDIRECT("1:"&LEN("0"&Number))),1)*2^(LEN("0"&Number)-ROW(INDIRECT("1:"&LEN("0"&Number))))) |
No |
| BIN2OCT |
Converts binary number to octal |
=BIN2OCT(Number,Places) |
Combine solutions for BIN2DEC and DEC2OCT |
No |
| DEC2BIN |
Converts a decimal number to binary |
=DEC2BIN(Number) |
=SUMPRODUCT(INT(MOD(Number/2^(COLUMN(1:1)-1),2))*10^(COLUMN(1:1)-1)) |
No |
| DEC2OCT |
Converts a decimal number to octal |
=DEC2OCT(Number) |
=SUMPRODUCT(INT(MOD(Number/8^(COLUMN(1:1)-1),8))*10^(COLUMN(1:1)-1)) |
No |
| HEX2BIN |
Converts a hexadecimal to a binary |
=HEX2BIN(Number,Places) |
Combine solutions for HEX2DEC and DEC2BIN |
No |
| HEX2DEC |
Converts a hexadecimal to a decimal |
=HEX2DEC(Number) |
=SUMPRODUCT((MATCH(MID("0"&Number,ROW(INDIRECT("1:"&LEN("0"&Number))),1),{"0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F"},0)-1)*16^(LEN("0"&Number)-ROW(INDIRECT("1:"&LEN("0"&Number))))) |
No |
| HEX2OCT |
Converts a hexadecimal to an octal |
=HEX2OCT(Number,Places) |
Combine solutions for HEX2DEC and DEC2OCT |
No |
| OCT2BIN |
Converts an octal number to binary |
=OCT2BIN(number,places) |
Combine solutions for OCT2DEC and DEC2BIN |
No |
| OCT2DEC |
Converts an octal number to decimal |
=OCT2DEC(number) |
=SUMPRODUCT(MID("0"&Number,ROW(INDIRECT("1:"&LEN("0"&Number))),1)*8^(LEN("0"&Number)-ROW(INDIRECT("1:"&LEN("0"&Number))))) |
No |
Missing functions: BIN2HEX, DEC2HEX and OCT2HEX as the creation of the characters A-F which are part of a hexadecimal number is not really feasible using worksheet functions.
Frank
The Analysis Toolpak Addin (ATP) is one of the most often used Excel Addins and provides several useful function. But the ATP has one major drawback: If internationalisation is an issue for you using the ATP will cause problems in your spreadsheets. Unfortunately Excel does not automatically translate the ATP functions to their local equivalents then opened in a different Excel language version but leaves the formulas unchanged. This will result in #NAME (or the local equivalent error message) errors in cells which make use of these functions.
Common recommendation is to not use ATP functions. But that is easier said then done. How to replace these useful functions if you don't want to use VBA User Defined Functions instead? The following article therefore will provide alternative formulas for some of the ATP functions. As the ATP consists of 93 formulas (if I've counted them correctly) this article is divided in several ATP function areas:
I will not provide replacement formulas for all ATP functions (mostly due to lack of knowledge) but maybe someone else will step in and provide the remaining ones. Also I'm hoping for more efficient solutions for some of the replacement formulas I'll present.
Mathematical ATP functions
| ATP Function |
Description |
ATP Syntax |
Replacement Formula |
Array formula |
| BESSELJ |
Returns the Bessel function |
BESSEJ(x,n) |
=SUM(-1^(ROW(INDIRECT("1:50"))-1)/(2^(2*(ROW(INDIRECT("1:50"))-1)+ABS(INT(n)))*FACT(ROW(INDIRECT("1:50"))-1)*FACT(ABS(INT(n))+(ROW(INDIRECT("1:50"))-1)))*x^(2*(ROW(INDIRECT("1:50"))-1)+ABS(INT(n)))) |
Yes |
| DOLLARDE |
Converts a dollar price (expressed as a fraction) into a dollar price expressed as a decimal number |
=DOLLARDE(fractional_dollar,fraction) |
=TRUNC(fractional_dollar)+((fractional_dollar-TRUNC(fractional_dollar))*10)/fraction |
No |
| DOLLARFR |
Converts a dollar price (expressed as a decimal number) into a dollar price expressed as a fraction |
=DOLLARFR(decimal_dollar,fraction) |
=TRUNC(decimal_dollar)+((decimal_dollar-TRUNC(decimal_dollar))/10)*fraction |
No |
| ERF |
Returns the error function integrated between lower_limit and upper_limit |
=ERF(lower_limit,upper_limit) |
=GAMMADIST(upper_limit^2,0.5,1,TRUE)-GAMMADIST(lower_limit^2,0.5,1,TRUE) |
No |
| ERFC |
Returns the complementary ERF function integrated between x and infinity |
=ERFC(x) |
=ChiDist(2*x^2,1) |
No |
| FACTDOUBLE |
Returns the double factorial of a number |
=FACTDOUBLE(number) |
=PRODUCT(IF(MOD(ROW(INDIRECT("1:"&number)),2)=MOD(number,2),(ROW(INDIRECT("1:"&number))))) |
Yes |
| GCD |
Returns the greatest common divisor of 2 - 29 integers |
=GCD(number1,number2,...) |
=MAX(IF((MOD(number2,ROW(INDIRECT("1:"&number1)))&MOD(number1,ROW(INDIRECT("1:"&Anumber1))))="00",ROW(INDIRECT("1:"&number1)))) |
Yes |
| GESTEP |
Tests whether a number is greater than a threshold value |
=GESTEP(number,step) |
=--(number>=step) |
No |
| LCM |
Returns the least common multiple of 1 - 29 integers |
=LCM(number1,number2, ...) |
=number2*MATCH(0,MOD(number2*ROW(INDIRECT("1:"&number1)),number1),0) |
Yes |
| MROUND |
Returns a number rounded to the desired multiple. Midway points are rounded away from 0 |
=MROUND(number,multiple) |
=ROUND(number/multiple,0)*multiple |
No |
| MULTINOMIAL |
Returns the ratio of the factorial of a sum of values to the product of factorials |
=MULTINOMIAL(number1,number2, ...) |
=FACT(SUM(number1,number2,...))/PRODUCT(FACT(number1,number2,...)) |
Yes |
| QUOTIENT |
Returns the integer portion of a division |
=QUOTIENT(numerator,denominator) |
=TRUNC(numerator/denominator) |
No |
| RANDBETWEEN |
Returns a random number between (and inclusive of) two specified numbers |
=RANDBETWEEN(bottom,top) |
=INT((top - bottom + 1) * RAND() + bottom) |
No |
| SERIESSUM |
Returns the sum of a power series expansion |
=SERIESSUM(x,n,m,coefficients) |
=SUMPRODUCT(coefficients,x^(n+m*(ROW(INDIRECT("1:"&ROWS(coefficients)))-1))) |
No |
| SQRTPI |
Returns the square root of a number multiplied by pi |
=SQRTPI(number) |
SQRT(PI()*number) |
No |
So this is the first part. ATP functions for which I don't have a replacement formula in this area are: All BESSEL functions. So feel free to add them 
Update 21-Dec-2004:
Asser was helpful in providing a worksheet formula for the BESSEJ function. So only 3 of the 4 Bessel functions remain
Frank