Shouldn't I be expecting the same result with and without FORMULA when saving a report in EXL2K format?

The following gives me two different results for the MARKUP_PCT COMPUTEd column - incorrect in EXL2K FORMULA format.

TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
COMPUTE MARKUP/D10 = RETAIL_COST - DEALER_COST;
COMPUTE MARKUP_PCT/D6.2% = MARKUP/DEALER_COST * 100;
BY COUNTRY
BY CAR
BY MODEL
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END

Francis, Did you get off by a factor ?
I ran this, and actually did 2 computes
MARKUP_PCT/D6.2% = MARKUP/DEALER_COST * 100;
MARKUP_PCT1/D6.2 = MARKUP/DEALER_COST * 100;
(Note, no % edit option).

Sorry, the result was 1,953.68% vs 19.54 . Although when I click on the COLUMN, the formula seems correct, and in fact if I copy the formula into another cell, it is correct, so I'm not actually sure where the fault lies, but if you are getting a result like that, the workaround seems to be to take out the edit option % sign.

Francis,
<br />
<br />
If you were to save the "Excel"
output as an htm file and then open it up in wordpad or another text editor you will find the xml in all its glory.
<br />
<br />
The code from the first data row that will be created if you add Noreens extra column is

 
<tr height=17 style='height:12.75pt'>
<br />  <td height=17 class=xl24 width=105 style='height:12.75pt;width:79pt'>
ENGLAND</td>
<br />  
<td class=xl24 width=94 style='width:71pt'>JAGUAR</td>
<br />  
<td class=xl24 width=187 style='width:140pt'>V12XKE AUTO</td>
<br />  
<td class=xl26 width=75 style='width:56pt' x:num="7427">7,427
</td>
<br />  
<td class=xl26 width=71 style='width:53pt' x:num="8878">8,878</td>
<br />  
<td class=xl26 width=63 style='width:47pt' x:num="1451" x:fmla="=$E2    -$D2">
1,451</td>
<br />  
<td class=xl27 width=96 style='width:72pt' x:num="19.536825097616802"
<br />  x:fmla="=$F2    /$D2    *100">1,953.68%</td>
<br /> 
 <td class=xl28 width=103 style='width:77pt' x:num="19.536825097616802"
<br />  x:fmla="=$F2    /$D2    *100">19.54
</td>
<br /> 
</tr>

you will notice that although the formula is there and is correct for both instances, the displayed data is incorrect when the computed column is given the percentage sign edit.
The actual stored number
(because it is D format) is, correctly, the same in both instances.
This would explain why Noreen got the correct value when she copied the formula to another cell, as Excel would have recalculated the value and not just displayed what it had been supplied with.
<br />
<br />
In my opinion this is a fault with WebFOCUS because it is creating the original XML code (as seen in the text editor) and as such I would advise raising a case with IB.
<br />
<br />
Given the above evidence they shouldn't be able to say that it is not a bug(?) and be able to progress fairly quickly with the resoilution. I'm sure Noreen would agree?

This message has been edited. Last edited by: <Mabel>,

Actually, the only problem I see here is that you are adding the "* 100" to your percentage calculation. Since you are designating it as a percentage with the "/D6.2%", you do not need the "* 100". That is done for you, so your calculation comes out 100 times larger than it should.

Terry, the % in the column format ONLY puts a percent sign at the end of the value, it does NOT do anything else.

The problem I'm having is obviously a bug, as Noreen and Tony pointed out.

I wonder why this has not been corrected already, it seems to fail on a very simple calculation.

I will open a case.

Thanks.

Francis.

Actually Francis, as Terry indicated, using the % does have an impact, but only in EXL2K FORMULA. So, you were both right. In EXCEL it does nothing.

It is not inconceivable that they skipped this little undocumented feature with % and EXL2K FORMULA.

Actually, just going into EXCEL without running a report and typing in the number 25 in a cell appears as 25. But, if you change the format of the cell to a percentage, you get 2500%.

Maybe this has something to do with the internal workings of EXCEL with the use of a percentage sign and the way that it works.

Ken

Francis, i agree with you..there's a lot wrong with formula than the feature you found. and i also agree with Terry...that this one has a work around...in focus the % is a formatting thing, but in excel, the act of converting a column to a % in order to get the % mark actually changes the cell value as well. so its a toss up...understanding the differences between the 2 products. Within the first hour of ever trying to use formula, we found that parentheses weren't respected in formula creation, and that on table column-total didn't work, it just recomputed. nasty when you have a price*quantity formula and you want it to total, and very def. not recompute. i reported both instantly. but don't know their progress, as we abandoned our 53 dev site. Would you be kind enough to keep us posted about what else you find with FORMULA on your 534 dev site? We asked for hidden columns as well, very important if we're going to make real use of this FORMULA thing.

It is quite obvious that there are some flaws with EXL2K FORMULA. I too have experienced a few interesting things such as Susannah pointed out.

In my opinion, this is the nature of the beast when it comes to new product functionality. EXL2K FORMULA was pegged as one of the great new features and in retrospect, in trying to improve the product to keep up with growing demand of functionality, some things aren't completely functional right off the bat.

Mind you, that's no excuse.

Personally, I think the formulas included in the Excel worksheets could be much better. However, I find in my experience that the formulas are only there to provide a warm fuzzy feeling that the number displayed is actually what was intended.

Ken

Susannah, Ken, TerryW!

You have stated that using % at the end of a format of a DEFINEd or COMPUTEd field in EXL2K FORMULA does more than simply put a percent at the end of the value of the field!

I find this incredible! It behaves differently for EXL2K FORMULA and all the other formats?

How can we write an application where the user chooses the output format of the report?
The same fex is executed, but for the selected format - the same results should be expected.

The other problem I have with FORMULA is that the TOTALs of computed PCT fields do not always add up to 100%, but that's another story.

I will not be using EXL2K FORMULA.

Thanks for the information.