Clipper - Calc differences between Clipper and Excel

Author

Calc differences between Clipper and Excel

Alan Secker

2007-08-13, 6:55 pm

One of my programs generates invoices and computes
the VAT We also have to keep a parallel record in
Excel The VAT figure is often different by a penny
or so. Here are the last seven items taken straight
from the reports for this month.

17.5% on Clipper Excel
-------- ------- ------
1787.78 312.86 312.86
229.50 40.17 40.16
175.39 30.69 30.69
98.58 17.42 17.43
289.96 50.75 50.74
344.24 60.24 60.24
126.75 22.19 28.18

AQs you can see, some are the same one is a penny down
three are a penny up. some Has anyone else come across
this before? I would like to fix it in Clipper if there
is.

--
I am told that it is impossible to make a program foolproof because fools
are so ingenious.

Patrick Mast

2007-08-13, 6:55 pm

Hey Alan,

> One of my programs generates invoices and computes
> the VAT We also have to keep a parallel record in
> Excel The VAT figure is often different by a penny
> or so. Here are the last seven items taken straight
> from the reports for this month.
>
> 17.5% on Clipper Excel
> -------- ------- ------
> 1787.78 312.86 312.86
> 229.50 40.17 40.16
> 175.39 30.69 30.69
> 98.58 17.42 17.43
> 289.96 50.75 50.74
> 344.24 60.24 60.24
> 126.75 22.19 28.18
>
> AQs you can see, some are the same one is a penny down
> three are a penny up. some Has anyone else come across
> this before? I would like to fix it in Clipper if there
> is.

Did you try with different SET DECIMALS settings?

Clipper and Excel should give the exact result if you always use 2
decimails in both apps.
Now, you ALWAYS have difference, for example.

A price including 21% VAT is 99.00
If you calculate the price excl VAT, it's 99 / 1.21, right?
That's 81.81 (Actualy, its 81.81818181...)

So, if you calculate 81.81 + 21%, you get : 98.99.. That's not 99.

Its and endless discussion with clients on this subject :)

--
Sincerely,

Patrick Mast,
xHarbour.com Inc.
http://www.xHarbour.com

dlzc

2007-08-13, 6:55 pm

Dear Alan Secker:

On Aug 13, 6:53 am, Alan Secker <a...@asandco.co.uk> wrote:
> One of my programs generates invoices and
> computes the VAT We also have to keep a
> parallel record in Excel The VAT figure is
> often different by a penny or so. Here are
> the last seven items taken straight from
> the reports for this month.
>
> 17.5% on Clipper Excel
> -------- ------- ------
> 1787.78 312.86 312.86
> 229.50 40.17 40.16
> 175.39 30.69 30.69
> 98.58 17.42 17.43

I get 17.25 here...

> 289.96 50.75 50.74
> 344.24 60.24 60.24
> 126.75 22.19 28.18
>
> [As] you can see, some are the same one
> is a penny down three are a penny up.
> some Has anyone else come across this
> before? I would like to fix it in
> Clipper if there is.

In Excel, force the first number to be only two decimals (as
necessary):
=int("17.5% on" * 100 + 0.5) / 100
Then force the percentage to also be two decimals:
=int("result above" * 17.5 + 0.5) / 100

Clipper starts with (essentially) implied decimal integers, and forces
intermediate results into the same box when written to disk.

Excel doesn't do this, as it leaves intermediate results as (a type
of) floating point decimal. Most of what you see is rounding, and
when / where it is applied.

David A. Smith

R. Totale

2007-08-13, 6:55 pm

On Mon, 13 Aug 2007 14:53:55 +0100, Alan Secker <alan@asandco.co.uk>
wrote:

>One of my programs generates invoices and computes
>the VAT

Just a thought - our local sales tax is referred to as 5% in day to
day speech, but the tax authorities actually provide a table

1 - 19 cents tax 1 cent
20 - 39 cents tax 2 cents

etc. If a similar table is available for VAT, rather than calculate it
you could get the applicable VAT for any given amount with a lookup in
both programs, eliminate the ambiguity between them, and always be in
strict compliance - which around here anyway is always a good idea
when dealing with taxes.

Klas Engwall

2007-08-13, 6:55 pm

Alan,

>One of my programs generates invoices and computes
>the VAT We also have to keep a parallel record in
>Excel The VAT figure is often different by a penny
>or so. Here are the last seven items taken straight
>from the reports for this month.

I tested your examples at the dot prompt in both PocketDot (S87) and
dClip (5.2e) and did not get the discrepances you get, so I think
there must be something additional going on in your report. Could you
show us the exact code used to calculate the VAT, please?

Regards,
Klas

-------
klas dot engwall at engwall dot com

http://www.engwall.com/clipper/

The LFN Library for Clipper
The LanMan Library for Clipper
The NFPAT1A Timeslice release patch for the Nanforum Toolkit

Joe Wright

2007-08-13, 9:55 pm

Alan Secker wrote:
> One of my programs generates invoices and computes
> the VAT We also have to keep a parallel record in
> Excel The VAT figure is often different by a penny
> or so. Here are the last seven items taken straight
> from the reports for this month.
>
> 17.5% on Clipper Excel
> -------- ------- ------
> 1787.78 312.86 312.86
> 229.50 40.17 40.16
> 175.39 30.69 30.69
> 98.58 17.42 17.43
> 289.96 50.75 50.74
> 344.24 60.24 60.24
> 126.75 22.19 28.18
>
> AQs you can see, some are the same one is a penny down
> three are a penny up. some Has anyone else come across
> this before? I would like to fix it in Clipper if there
> is.
>
>
>
Alan, I can't guess what you are doing different than I.

col1 col2 clip col3 excl
1787.78 312.86 312.86 312.86 312.86
229.5 40.17 40.16 40.16 40.16 *
175.39 30.69 30.69 30.69 30.69
98.58 17.42 17.25 17.43 17.25 *
289.96 50.75 50.74 50.74 50.74
344.24 60.24 60.24 60.24 60.24
126.75 22.19 22.18 28.18 22.18 *

col1 is your first column, col2 your second and col3 your third.
In Clipper I did simple

do while ! eof()
replace clip with col1 * 0.175
skip
enddo

Next I import the table to Excel and create a formula..

=a2 * 0.175

...in cell e2 and copy/paste it through e8. Now format the cells in
column e to 2 decimal places.

My answers differ from yours in three places but I get the same answer
from both Clipper and Excel. I'm at a loss as to why your numbers are
not exactly mine.

--
Joe Wright
"Everything should be made as simple as possible, but not simpler."
--- Albert Einstein ---

Sponsored Links