Archive for the ‘Math and Stats Functions’ Category.

Trig

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:

excel sheet atan

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.

Too Random

faith mountain statue

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?

Is this number prime?

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.

Replacing the Analysis Toolpak Addin - Part 4

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

Replacing the Analysis Toolpak Addin - Part 3

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

Replacing the Analysis Toolpak Addin - Part 1

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