[AccessD] My Excel project...you won't believe this one

Hi Darryl et al

Sorry to destroy the party, but all it takes is well defined criteria (business rules) - which seems to have been present here and trivial too - as well as a skilled programmer in this area - no guru or genius is required. If you deal with calculations and feel you can't handle rounding properly, you should put this item on the agenda to fill one of the empty(!) spaces in the upcoming Christmas holiday season. It isn't difficult at all.

Hint: The only native function of VB(A) that handles 4/5 rounding correctly is Format. It is, however, not very fast so if speed is a concern you have to run a custom function. And no, the VB(A) function Round is not the answer to any serious task:

http://www.xbeat.net/vbspeed/c_Round.htm#Round16

And don't forget: Math is fun!

/gustav


>>> darryl at whittleconsulting.com.au 22-12-2011 23:28 >>>
In my last role I saw a similar issue when one of the developers was trying to group data which contained decimals into the nearest whole number (up or down) to determine the band.  He was using the INT function which he didn't understand at all (from what I can tell).

The issue was a series of 4 questions would return a value based on a risk matrix.  So you would have a value like 1.99 or 1.67 and it would all be regrouped as a 1.  They were wondering why their rating engine were returning screwy results (it was basically a risk scale so anything with a risk rating >= 1.5 should have been priced using risk band 2 for example).  Given that this data was used to price customer quotes based on their risk profiles they were underquoting consistently until I spotted the error.

That said, I am not perfect with these things either and have made plenty of similar errors over the years.  Guess it shows the importance of getting everything tested by a whole group of different (and skilled) folks.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson
Sent: Friday, 23 December 2011 4:14 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] My Excel project...you won't believe this one

I am sure that they are losing so much money that most of their thinking investors and auditors have abandoned and the fools who remain don't know the difference between a balance sheet that adds up and one that doesn't.
On Dec 22, 2011 12:03 PM, "Mark Simms" <marksimms at verizon.net> wrote:

> Instead of using the number format to properly display the numbers, 
> the original developer would use the Format function to round a large 
> series of individual values,  and then total-up the rounded results 
> instead of the original values.
> As you are aware, precision is likely lost in doing it this way.
> Ex: .55+.55=1.1
> His method would be to first "round-up" the .55 to .6:
> Ex: .6+.6=1.2
> In this case, the total is now "off" by almost 10% !
>
> And...this is a vital model that helps to run the company's main 
> processing unit.
>
> Wowzer indeed.