Hello,

I´m using flexcel (latest download(6.18.5 1/15/2018) with

Delphi xe8( 22.0.19027.8951 ) on w7ul64 but 32 bit-compiler ) and I´ve

had real trouble last week. My job is using a xls-file with some more or

less complex formulas as "blackbox-engine".

I´m putting some input values on sheet "Input" and I (hope) to get some meaningfull answers on the output-sheet.

But the only value I got from flexcel was"#N/v" (Excel (2003 and 2013 32bit) surely deliver correct answers).

I´ve

startet mission "ugly-poisened-bug-search" and found a very simple but

annoying explanation: There are differences in both products and - eXcel

is wrong :-( !

Formulas using "match" (in german "VERGLEICH") need increasing values with the same datatype.

For excample formula:

=VALUE(INDIRECT(CONCATENATE("''TechnischeDaten''!",ADDRESS(11,**MATCH(D10,TechnischeDaten!A6:N6,1**)+0))))

The range** A6:N6**

is the error. In A6 is nothing, in B6 is a description of the row

(alphanumeric), in C6 is nothing and the real data range of numeric

values **starts in D6 and ends in N6**

Excel is able to ignore the Values from a6 to c6 and correctly delivers a value.

Flexcel gets in trouble with this and reports "N/A".

Now my short questions:

1) Is there a List of existing differences of both products

2) or better, a property to get "better" excel-behaviour?

3) is it possible to get the functionname of a formula producing the error (a formula-debugger would be great ;-) ).

Greetings Kurt

Hi,

Hi Adrian,

Thanks for the fast reply!

And thank you very much fpr the link to GetFormulaTokens. I think this will really help the next time.

Once again I've reduced the problem of "Match" to pseudo code **=MATCH(65,****{"". "BlaBla" . "" . 65 . 100 .150 . 200 . 250 . 300****} ,1**) +0

in German =Vergleich**(65; ****{"". "BlaBla" . "" . 65 . 100 .150 . 200 . 250 . 300****} ; 1**) +0

(the list is normally longer)

In my eXcel versions the result is 4 - this doesen´t look like binary search , except the dataype is also used to build the key.

I want to clarify my problem: I´m not looking for the proper result - I´m looking for the **same** result.

It would be great to get the possibility to configure the formula-interpreter.

And

it even would be great to get a list or better table of functions with

differences to all these ugly excel versions. This would really save

time.

My next job will be a xls-Checker looking for problematic formulas in use

greetings Kurt

To me, the formula =MATCH(65,{"","BlaBla","",65,100,150,200,250,300},1) does look as a binary search:

*doesn't*consider "" to be 0, so the result is #N/A.

*every*case, because this would mean finding all bugs in Excel and copying them, and making 0 bugs in the FlexCel representation that copies the bugs from Excel. And when Excel version x + 1 is released, you would now have to find all the bugs in the new Excel, find every bug that was fixed, and change all of the FlexCel implementations to return the same as Excel x + 1.

To be more specific, here are the results in different apps:

Hi Adrian,

Thank you very much again. Indeed, I´ve tried some varations and It fails!

I really will think about a universal "PoisenedFormuaWithpossiblyWrongDataFinder" in the near future. My problem is that customer employees are producing the xls-files and my program has to take it serious. The bad news (for me) is that they have a vivid imagination the good news is that they only have two excel versions.

PS I´ve changed the formulas everything is working fine now.

Greetings Kurt