My current project's business rules are very complex--and the calculations often end up multiplying very small number by very large ones, or by taking ratios of numbers which aren't easily-calculated using Excel. Since my clients are mostly accountants, showing them how Excel doesn't always produce the right results doesn't really help, because (a) they rely on Excel daily, and feel comfortable with it, and (b) they don't really have an alternative.

The QA team and the users they represent have become familiar with FitNesse (http://www.fitnesse.org), and trust it. We have developed standards for presenting numerical results (10 decimal places, truncated from 17) which allow the tests to reliably reproduce the same results, even if minor details of the underlying calculations change (e.g. add values first, then multiply by rates, versus multiplying each value by a rate, then adding the results). There are two areas in which we still have trouble communicating with QA and the users: date arithmetic, and ratios of very large to very small numbers (e.g. 10^8 over 0.0011). The latter problem is complicated by the fact that in the IEEE 754 standard, which is what Excel uses to store numbers, there is no exact representation of numbers which appear in our calculations a lot, such as 0.1.

One neat feature of the FitNesse column fixture is that if the user doesn't put any information into a cell, and yet your fixture code provides a result for that cell, the result is displayed in the cell "without comment" by the Fit framework. What I did for our QA team is create a fixture which performs various calculations for them, based on inputs they provide. Here's an example of part of the fixture as users would see it:

Now they simply punch the "test" button, and here's the result:

The code to do this is shown below. FitNesse cell content is text, so you can place anything you want in a cell--I have taken advantage of this to provide more than just numerical results. The fixture code for these 4 columns is shown below, though I have removed some details of formatting and BigDecimal arithmetic to clarify how it works. I haven't included the laborious date calculations routines; think of it as an exercise. ;)

public Date date=null; // receives the date from the FitNesse web page

// as each tax year is calculated, its associated date is stored here,

// so we can refer to the list in subsequent methods.

private List dates=new ArrayList();

public String taxYear() {

dates.add(date); // for other calculations, not this one.

Format formatter = new SimpleDateFormat("yyyy");

Calendar cal = new GregorianCalendar();

cal.setTime(date);

int month = cal.get(Calendar.MONTH);

// If the date is after June 30 use the given year, otherwise year - 1

if(month > Calendar.JUNE)

return formatter.format(cal.get(Calendar.YEAR) + 1);

else

return formatter.format(cal.get(Calendar.YEAR));

}

public String fromTaxYearStart() {

if (date==null) return "";

Date start = getFirstDayofTaxYear(date);

int delta = daysBetween(start, date);

int total = daysBetween(start, getLastDayofTaxYear(date));

return delta+" (/"+total+" = "+

(new BigDecimal(delta).divide(

new BigDecimal(total)).toPlainString()+")";

}

public String fromPreviousInclusive()

{

if (dates.size()==1 || date==null)

return null;

return daysBetween(dates.size()-2, date);

}

public String fromFirstInclusive()

{

if (dates.size()==0)

return null;

return daysBetween(0,date);

}

With these tools, it is now possible to have a conversation with QA in which we discuss the input values, rather than the validity of the calculations. It remains to be seen how effective they'll be, but I have hopes.