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,
Indeed, FlexCel and Excel aren't identical. We try to keep the differences to a minimum (and I think we do a good job at it), but the only way to have identical behaviors between both would be if both had the exact same code base.
There isn't either only one "Excel" version that has the correct results: You might find differences between Excel versions too, because they don't have either the exact code base. When implementing our formula engine, we saw a lot of cases where Excel 2003 would behave different from Excel 2007 or similar with other versions, and it is always a challenge to choose which behavior to emulate. (If you emulate 2007 behavior, an user with Excel 2003 will complain, and viceversa). As one example, this is the first page I get googling differences between Excel 2007 and 2010:
But every Excel version introduces changes, some documented and some not, and it would really be impossible to have a list of difference between us and every version of Excel (and seriously, nobody would read that either).
This all being said, there is no reason to panic: Most of the formulas will work the same in any Excel version or FlexCel, and when they don't normally there is an error in the formulas themselves.
In your case, the problem is that Match by default assumes an ordered list, so we (both Excel and FlexCel) do a binary search to find the match, as that is much faster than a linear search.
So instead of searching every item in the list, as it is sorted, we check the half of the list, then if the item is bigger we repeat the search in the bigger half, else we repeat in the lower half.
Excel does the same too. For example the formula:
=MATCH(4, {1,7,4,5})
When tried in Excel 2016 returns "1". (while 4 is actually at the 3rd position). Why does that happen?
Well, we search in the middle of the list, that is in the "7". Then as 4 is less than 7 and the list is ordered, we assume the value is in the lower half. So we look in the lower part, which only have a 1, and return that 1.
FlexCel in this case will also return 1, because it does the same. On the other hand:
=MATCH(4, {1,7,4,5}, 0)
Will correctly return 3 in both Excel and FlexCel.
Now, this is a simple example. In more complex cases, both FlexCel and Excel can apply their own optimizations to do the search as fast as possible, assuming the list is ordered, because that is a requisite of Match. And yes, the values reported by FlexCel might differ from those Excel returns, but both will be wrong because we both assume the list is ordered.
We try to keep our results as close as possible as those Excel returns, and in this case we also do a binary search as they do, but we can't really guarantee to give the same wrong value Excel is reporting. Ther emight be also rounding differences, as internally Delphi uses "Extended" floating point numbers,
The only solution in this case is just change the Match functions to have a "0" as third parameter. (unless you know the items are actually sorted). You can use FlexCel to check all the formulas in a book and warn if any Match formula doesn't have 3 arguments. (so if they want sorted behavior, they need to manually specify Match(..., 1)
You can use GetFormulaTokens for this:
Whenever you have a match, check it has 3 arguments and report it otherwise.
You can do the same for Lookup, VLookup and HLookup which have a similar problem. But you need to fix the formulas, a match in an unordered list without an "exact match" parameter will be wrond in Excel and FlexCel, and will be right only by pure chance.
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:
First try: 100. 65 is less than 100. Second try "BlaBla". Excel is considering "BlahBlah" as 0, so it is bigger. Next try: 65. Found!
But change "blahblah" to 90:
=MATCH(65,{"",90,"",65,100,150,200,250,300},1)
Now, firs try is 100 again, second try is 90, then we try with "", which in this case, different han in the one before, Excel doesn't consider "" to be 0, so the result is #N/A.
Now we can try changing the "" at the start by an actual 0:
=MATCH(65,{0,90,"",65,100,150,200,250,300},1)
Again, first try at 100, then 90, then 0, so result is 1.
As you can see, it is indeed a binary search, but Excel in some cases is considering the strings as 0, and in others as #N/A. FlexCel is always considering them as #N/A (which sounds more correct to me).
So to recap: in the case:
=MATCH(65,{"","BlaBla","",65,100,150,200,250,300},1) Excel will consider "BlahBlah" as 0 and return a value, while FlexCel correctly assumes it is a N/A and returns N/A.
=MATCH(65,{"",90,"",65,100,150,200,250,300},1) or =MATCH(65,{"blahblah",90,"",65,100,150,200,250,300},1)
Both FlexCel and Excel will consider the string as invalid, so both will return #N/A
=MATCH(65,{0,90,"",65,100,150,200,250,300},1)
Both Excel and FlexCel do a binary search and both return 1, while the value is actually 4.
In this case, the problem seems to come from what Excel is understanding the strings to be. Excel is not being consistent, and sometimes treating the strings like 0 and sometimes like errors. This looks like a bug in Excel to me, which might be fixed in some future version.
But again, while I understand you are looking for the same result even if it is wrong, that is just impossible because:
1)In many cases Excel behavior changes form version to version, so you can't search for "the same result as Excel" but "the same result as Excel version x".
2)It is just an impossible task to copy the exact "Excel version x" behavior foe 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.
I am not sure if the scope of this task is clear enough: There are more than 400 functions implemented by FlexCel: http://www.tmssoftware.biz/flexcel/doc/vcl/about/supported-excel-functions.html
And each one can have its own quirks. As you saw, playing just a little with a single function "Match" already found a bug in Excel, that is not fixed today but might tomorrow. We spent months and months testing every one of the 400 functions to be sure that in all the cases we could think of, the result is correct. But there are always other cases, like here where a string at the middle of the match is treated differently by Excel from a string at the beginning of the match which we just can't replicate.
A document with all the bugs in FlexCel and Excel would not only be too big, but just impossible to create. There is not a list of all the differences between Excel versions because nobody, not even the guys at Microsoft know every difference.
About a xls checker, indeed this can be a nice idea. In this case, the problem was not really that the values were not sorted, but that you are mixing strings and numbers in the same match call. An xls checker should check all types are the same.
=MATCH("b",{"a","b","c"},1) is fine.
=MATCH(2,{1,2,3},1) is also ok.
but
=MATCH(2 ,{"a","b","c"},1) is an error (and in this case both Excel and FlexCel will return an error). IT just seems that in some cases Excel fails to realize to report the error and instead returns an invalid number.
To be more specific, here are the results in different apps:
=MATCH(65,{"",90,"",60,100,150,200,250,300},1)
Excel: N/A
LibreOffice: N/A
Google docs: 4
FlexCel: N/A
=MATCH(65,{"","","",60,100,150,200,250,300},1)
Excel: 4
LibreOffice: N/A
Google docs: 4
FlexCel: N/A
As you can see, all other programs are consistent: They either return 4 or N/A in both cases. (depending if the consider a string to be 0 or an error) .
But Excel is behaving weird: it considers the string as 0 when searching, but an error is the final result is a string. It seems like a bug in Excel to me, both formulas should be either N/A or both 4, but not one one thing and the other another. You either consider the strings as zero or you don't. I imagine the problem here is that Excel uses a binary search algorithm from another place that considers strings as 0, but when returning the result it will check that it is the same type of the value you are searching for, or return an error.
I guess we could copy this behavior and only consider the strings 0 when doing the binary search, and not when doing the search: This will give us compatibility with Excel (2016) but incompatibility wiht both LibreOffice and Google docs.
But the reality is that it doesn't matter: The formula is just wrong, and any value reported is wrong. The only real solution here is to fix the formula, and if you do, all Google docs, Excel, libre office and FlexCel will report the right value. But if the formula is wrong, the behavior is not really defined and you can get different results.
And as you can also see, we found this just by looking a little in a single function. We find this kind of behavior all the time, where the results are different in different Excel versions, libreoffice or google docs, because the formula doesn't make sense. There are just too many of those quirks to document every single one, and they change with every Excel version. And in this case, it is all to get the "same" result as Excel, but a wrong result either way. I think that the fact that you found this difference was a good thing, because it allowed you to correct the sheet.
As said, your original formula, by pure chance produces the right result:
=MATCH(65,{"","BlaBla","",65,100,150,200,250,300},1) gives 4 in Excel.
But, if the formula was:
=MATCH(65,{0,90,"",65,100,150,200,250,300},1)
the result would be 1, while it should be 4. This can produce very subtle bugs in the spreadsheet, and the fact that you caught the error is a good thing.
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