Before I was product manager, I used to do scripting for my clients, and often some of our sales reps would throw something in my direction if it was particularly math heavy, as they knew that I had originally majored in astrophysics. On one occasion, a sales rep had a client who was ready to buy but needed a complex calculation that they had on a spread sheet to be duplicated within the user defined fields of our software. It was the Black-Scholes Model, a complex pricing model that calculates the premium of an option.
I reviewed the spread sheet, confirmed which lines were input, and looked at all of the steps of calculations. There were a lot of steps, but all of the steps were easy – except one. One row of the spread sheet (let’s call it row H) contained an equation that just referenced an internal Excel function run against the value in the previous row. It looked like this: NORMSDIST(G). What the heck is NORMDIST? Well, I looked it up in Excel help and came up with the following equation:
NORMSDIST(x) = (e^(-1*((x^2)/2)))/((2*Pi)^(1/2))
Okay, I can do that. Just take the value in row G (call it X). Square it. Divide it in half. Flip its sign. Take Euler’s number and raise it to the resultant value, then divide the result by the square root of 2 x Pi. That’s easy! I told the sales rep that I could do it. She told the client. The client bought the software, and I was tasked with designing the script.
I set up the user defined fields for all of the input variables and output results, and then I set a field as a button and began to write a script to execute upon a button click. After each step in the spread sheet, I tested it, and I was getting the value in the next line of the spread sheet. Everything was working fine, until I got to the line with the NORMSDIST function. No matter how many times I checked my code, the result when I pushed the button was not the same as what was on the spread sheet. So, I went back to the help system to see if I had made an error in copying down the equation. I read a bit more carefully this time:
NORMSDIST(x) = the area under the curve defined by the function y=(e^(-1*((x^2)/2)))/((2*Pi)^(1/2))
This is what I get for just looking at the equation and ignoring the plain English proceeding it. To use the Australian term, I was gobsmacked! I had made a promise I couldn’t keep. There was no way on Earth that I could work out an integral of that function. My mathematical skills did not rise to that level of expertise. I didn’t even know if it was possible. I still don’t. But Excel was doing it somehow, and that got me thinking – what does it really mean to take an integral in calculus? The evaluation of the integral of a function gives us the area under a curve defined by that function. But this area is the limit of the sum of a bunch of rectangles that fit under that curve as the width of the rectangles approaches zero. Suddenly, I knew how it could be done, and it only took me an extra hour to figure it out.
Needing a base reference point, I used the Excel function to determine that NORMSDIST(0) = .5. So, the area under the curve from -infinity to zero is equal to 1/2. That would be my starting point for positive values of X. If X is negative, I would be subtracting my calculation from the starting point of 1/2, but as long as X is positive, I will be adding to 1/2.
So, I begin…
- Area = .5
- Increment = 1
- For i = increment to X step Increment
- Rectangle = (e^(-1*((i^2)/2)))/((2*Pi)^(1/2))
- Area = Area + Rectangle
Next,
So now, I am adding a bunch of blocks together, whose height is defined by the function, knowing that I’m not going to be close to the right answer. The width of my blocks (1) is way too large.
If my answer gets closer and closer to the answer provided by Excel as I make the value of the increment smaller and smaller, then I know this is working. Then I only need to decide how many decimals of precision are required, and when changes in the increment result in changes in the result beyond that degree of precision, then I am done. I try increment = 0.1.
I try increment = 0.01
I try increment = 0.001
I try increment = 0.0001
And at this point I have arrived. Up to the point of precision that I have decided is necessary, the result is no longer changing. The final code for the section of script looks like this:
Area = .5
For i = .0001 to X step .0001
- Rectangle = (e^(-1*((i^2)/2)))/((2*Pi)^(1/2))
- Area = Area + Rectangle
Next
And now I know that any problem can be solved if you think outside the box (and also make the boxes narrow enough, and get them to fit under the curve).
