I'm trying to get numbers into Excel in such a way that if someone sums them in the spreadsheet they arrive at the same (sub-)totals as WebFOCUS did when generating the sheet. So far the numbers tend to be off by a few...

For example, some numbers end up in Excel like:

 1 752
   385
 3 626
 5 154
20 151
 3 936

 2 567
   735
======
38 305

If you SUM(A1:A9) you arrive at 38 306! One higher than what WebFOCUS came up with. The difference is due to some decimal numbers that we don't want printed in the Excel sheet...

I found a workaround by doing thusly:

COMPUTE MARGIN/D10.2    = GROSSSALES - COSTS;
COMPUTE R_MARGIN/I10    = MARGIN + 0.5;

But I have to do this for quite a few fields and I need to be really careful about which fields I round this way and which I don't. I can't imagine there's no more convenient method.

Unfortunatly, Excel templates are out of the question, as we're stuck with Excel 2000. I also can't use EXL2K FORMULA on this report, as the borders break the cel references as per one of my other posts (and a bug report).

This message has been edited. Last edited by: Wep5622,

This is a very annoying problem with WebFOCUS. I haven't found a solution. One workaround I've used is to add or subtract .1 from one of the lines. This means you have to do a pile of processing just to find out if the rounding caused a problem with the SUM.

quote:

COMPUTE MARGIN/D10.2 = GROSSSALES - COSTS;
COMPUTE R_MARGIN/I10 = MARGIN + 0.5;

Francis/Wep,

If you changed I10/D10.2 to P10/P10.2, do the results equal?

Tom

I have never been able to fix this issue, changing numeric formats haven't made a difference in my testing.

Search for "percentage" with my name as author and you will see my frustration.

Here is one: [HELP!] How to ensure that percentages add up to 100%

Hi Francis,

OK, Thanks! I agree...

Frustrating...

Tom

Oh dear, and here I thought I was asking a simple question...

Thankfully my requirements aren't as strict as Francis', I'd be quite happy to find out the secret method for how to calculate numbers in one precision but display them in another in Excel, if such a thing exists.

It is obvious to me that WebFOCUS does something to massage the data into the formatting required for the end result, having some control over whether it rounds or formats the data would be great! For example, I'd like to be able to specify 'TYPE=DATA, FORMAT=D5.2S,$' in my style sheet for a field that actually has precision D5.10. This would just set the column format in Excel to '## ##0,00', but keep the actual precision. That way my numbers add up properly, while they are displayed in a sane way to the end users.

Of course they wouldn't add up again if displayed with their actual precision (formatted in Excel like '# ##0,0000000000'), as the thread Francis references makes all too clear - that's what you get with finite precision numbers. That's exactly why being able to manipulate the format in which those numbers are displayed is necessary.

The way I got around this is to use P format with as many decimal positions as allowed. For example, if a field will never be more than 99999, use P17.11. Note, P format by default does not use commas like D format. Output those numbers to excel and have a auto-open macro in excel to change the format to 2 decimal positions. The full number is still there, but displaying rounded to 2 decimal positions. If someone else copies the data to another spreadsheet, they will copy the full precision. So if they sum the numbers again, they will get the same answer. As long as they don't change the data by using the excel function to round or truncate the numbers, everything should be fine, and if they do, then tell them the numbers don't add up because they changed the data.

This is what I did when I had to match some federal measures that went to 12 decimal positions.

Wow thanks, using PACKED instead of DECIMAL worked! We couldn't quite figure out what you intended to do with that macro, but we don't seem to need it anyway...

But that raises the question why summing rounded PACKED numbers works "better" than summing rounded DECIMALs? This seems quite alarming, so we'd like to understand this better. Could you (or someone else) elaborate please?

The details of rounding are handled in the following ways for the following numeric formats:

Integer format. When a value with decimal places is assigned to an integer field, the value is rounded before it is stored. If the value is assigned using a DEFINE or COMPUTE command, the decimal portion of the value is truncated before it is stored.
Packed-decimal format. When a value is assigned to a packed-decimal field, and the value has more decimal places than the field format specifies, the value is rounded before it is stored.

Floating-point single- and double-precision formats. When a value is assigned to one of these fields, and the value has more decimal places than the field format specifies, the full value is stored in the field (up to the limit of precision determined by the field internal storage type). When this value is later displayed, however, it is rounded.

Note that if the decimal portion of a floating-point value as it is internally represented in hexadecimal floating-point notation is repeating (that is, non-terminating), the repeating hexadecimal number is resolved as a non-repeating slightly lower number, and this lower number is stored as the field value. In these situations, if in the original value of the digit to be rounded had been a five (which would be rounded up), in the stored lower value it would become a four (which is rounded down).

Thanks for the explanation, we were under the (wrong) impression that decimals were equivalent to SQL's fixed point numeric type.