Freigeben über

Speaking of numbers...

  • Artikel

One of the features in Excel I'm in charge of testing is "Calculation, Functions, and Formulas", which (surprise!) is exactly what it sounds like. Being a big math and numbers geek, I was very excited to learn that I would inherit these features when I started. The way that we test these this area is a nice snapshot of how we utilize AppleScript and our automation system.

Functions are the heart of Excel, even the most novice Excel users are familiar with things like SUM() and AVERAGE(). Excel has somewhere around 320 functions all told, many of which are very specialized and not used frequently. In order to make sure we regularly test every single functions, we've designed a system in which a single script and test file exists for each one. These files consist of functionality, boundary, error condition, and other tests, usually about one per row. Each row (this is row 11 in the SUM.XLS test file) looks something like this:

A

B

C

D

E

F

G

H

I

Manual Example

=SUM(E11:I11)

55

TRUE

10

7

9

27

2

Column A is a description of the test

Column B contains the formula itself

Column C is a hard-coded expected result

Column D is set to "=C11=B11", which returns TRUE if the values equal each other, and FALSE if they do not.

Columns E-I are the arguments used for the formula in Column B

At the top of each file, cell $D$1 is set to “=AND(StatusArea)” with StatusArea being a name defined as whatever part of Column D is filled with tests. It all boils down to one cell on one sheet; If any of the results are incorrect we will know right away.   

For each test file, we have a little piece of AppleScript that opens the file with help from our automation framework, forces a re-calc by replacing “=” with “=”, logs the value of $D$1, and closes the file.

set myusedrange to active sheet's used range
replace myusedrange what "=" replacement "=" look at part search order by rows without match case
Log (LocalizedTrueString, range ("$D$1")'s string value, "String Value of $D$1 (expect: TRUE/(pass)) ")
close every workbook saving no

Lather, rinse, repeat, 321 times.

Each of these scripts runs every time there is a new build, on both CPU platforms. Failures appear right away, and we can compare the results over time to make sure that PPC and Intel are doing the same math. We can also quickly fire off a test run if we want to check out the latest OS patch, or critical check-in to Excel’s code. Scripts like this and our automation system enable us to test across a huge part of the product almost immediately. The simplicity of it all means that anyone can check on the state of our calc engine, and update the test files with new cases they find at any time.

Comments

  • Anonymous
    November 15, 2006
    That sounds great and clever, but the big question is - did you find anything on the current versions of Office? Others have found problems with Intel/10.47.

  • Anonymous
    November 15, 2006
    David, Thanks! We have not made any changes to the calc engine in Excel 2004 since we've released it. We did work with Apple to fix some Rosetta issues since Intel machines have shipped though. As far as current problems with Intel and 10.4.7, we monitor newsgroups and other forums pretty closely, and have not seem any calc-specific problems. If you have more information, please email me through my profile here so I can look into it.

  • Anonymous
    November 16, 2006
    Love it, this is the kind of thing that keeps me coming back.

  • Anonymous
    November 16, 2006
    Truth be told, =C11=B11 is not the best way to test for exact equality, since it allows for slightly different results to go unnoticed. The way to verify the exact equality is to use =C11-B11=0.

  • Anonymous
    November 17, 2006
    Your approach sounds good, but could you give some statistics about time requirements to run the full test suite? Opening and closing over 320 Excel files with AppleScript sounds like it may take a while.

  • Anonymous
    November 17, 2006
    Rho: We do use a few different methods for comparing results, yours would be great for numerical results. There are a large number of functions that return text or non-numeric values as well. Error conditions are checked with =ERROR.TYPE(C11)=ERROR.TYPE(B11), for example. Nikita: With the help of David Weiss, I looked at a recent run and found that my calc scripts ran on a total of 20 different machines for each CPU (Intel and PPC) and took a total of 1 hour and 20 minuts per set. So that would be a total of about 640 runs over the course of 160 minutes, on about 40 separate machines. My calc scripts are actually some of the fastest ones we run, I would guess that 75% or more of that time is overhead, setting up the machine, logging, etc. The actual time it takes to open recalc and close one of my test files is minimal. I think David will be writing soon with some more interesting statistics about the whole automation system.

  • Anonymous
    November 23, 2006
    Happy Thanksgiving to everyone on the Microsoft Office for Mac team!

  • Anonymous
    November 24, 2006
    Yeah, happy thanksgiving, and keep up the good work!

  • Anonymous
    December 03, 2006
    My first reaction to your test spreadsheet is that an error in the logical processing that returned TRUE for every comparison would not get caught. But your response to Rho indicates that you have thought of this. Some responses should deliberately be FALSE to insure that the comparison logic is working.

  • Anonymous
    December 12, 2006
    I do not know what URL Means Hopefully it's an email address for return contact HELP!!!! Yes, I am an old Broad - 76 yrs old. I NEED HELP WITH EXTENDING THE NORTH CAROLINA TAX CHART FORM E-502H (10-06) As of Dec. 1st North Carolina lowered the sales tax 1/4% from 7% to 6.75% This has caused problems in our volunteer shop. I WORK WITH 70 - 80 YR OLD LADIES AT A CHURCH THRIFT SHOP  WHO CAN'T CALCULATE (all our money goes to the Outreach community) THE TAX SHEET ENDS AT 68.52 - 68.66 = 4.63 THE PROBLEM: THEY WANT ME TO CONTINUE THE 6.75% TAX FIGURES TO $100. I NEED THIS BY TOMORROW - THE SHOP IS OPEN FOR SALES ONLY ON THURSDAY. PLEASE PLEASE - CAN ANYONE HELP??? I will await a response. I've tried to use the HELP on excel - but after 2 hours I GIVE UP.  I CALLED APPLE HELP - AND THEY GAVE ME A VERY LONG http://support.microsoft.com address - when I got there I didn't know what to do. Much appreciated. Mabel (Vicki) Broad