Daily posts of Excel tips…and other stuff
Archive for the ‘Math and Stats Functions’ Category.
Tom asks:
I have three values with wind direction at differrent altitudes.
A1: 020, A2: 2000 feet,
B1: 010, B2: 5000 feet,
C1: 350, C2: 10000 feet
and I would like to forecast the wind direction for D2: 3000 feet. I’ve tried the forecast function but it goes haywire with the change from 010 to 350 degrees.
I made [...]
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 [...]
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 [...]
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 [...]
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:
Part 1: Mathematical functions
Part 2: Date/Time functions
Part 3: Numerical system conversion functions
Part 4: Others
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 [...]
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:
Part 1: Mathematical functions
Part 2: Date/Time functions
Part 3: Numerical system conversion functions
Part 4: Others
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 [...]
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 [...]
