Flexcel fails on non-continous array in formulas

Vladimir_Vuleta · November 12, 2013, 8:41am

Hi

I run into an issue with non-continuous arrays when calling GetStringFromCell ( )

( the same issue exists for InsertAndCopyRange ( ), InsertAndCopySheets ( ) etc. )

Example:

If cell formula is = LARGE ( A1 : E1, 1 ) flexcel call returns a proper result.

If cell formula is = LARGE ( ( A1, C1, E1 ) , 1 ) flexcel call returns an empty string

I have not checked any other functions that take array as a parameter

V

adrian · November 12, 2013, 9:54am

Hi,

Thanks for the information. I've found something wrong in FlexCel calculation, but I am not really sure this is what you are seeing.

First of all, let's clear a little the wording so we both refer to the same. The formula

=LARGE((A1, C1, E1 ) , 1 )

doesn't have any array. A formula with an array would be something like:

=LARGE({2,3,4},1)

Which FlexCel handles just fine. (you can't have references in arrays, so this is why you can't have {A1, A2} )

The expression (A1, C1, E1) is an Union of 3 ranges, not an array. "," is the Union operator in Excel. So, same way that A1:A4 means cells A1, A2, A3, A4 because the colon ":" is the Range operator, A1, C1 means cells A1 and C1. The third operator in Excel is space " ", the "Intersection operator. So "A1:B3 A2:A10" means cells A2 and A3, since it is the intersection of the ranges.

So this isn't related to arrays at all, it is good to keep in mind the difference because calculation paths are different for arrays and array formulas. You can combine ranges of cells in any way by using the union (","), intersection (" ") and range (":") operators, but you still have a range of cells, just a not contiguous range.

Both arrays and non contiguous ranges should be supported by FlexCel.

Now, about your current problem:

The only thing I could reproduce is that FlexCel can't recalculate Large and Small functions in those non contiguous ranges. This is an oversight, we will be releasing an update soon that can calculate them. But the rest seems to be ok, I tried InsertAndCopyRange, GetStringFromCell (in a file not recalculated by FlexCel), InsertAndCopySheets, and all of them seem to understand the range fine. For example, if you insert a row before the formula =Large((a1, c1, E1), 1) it will become = Large((A2, C2, E2), 1)

Were you referring to something else with the phrase: " the same issue exists for InsertAndCopyRange ( ), InsertAndCopySheets ( ) etc." ?

I'll see to fix the recalculation of Large and Small, but I don't see anything else wrong.

Vladimir_Vuleta · November 12, 2013, 10:29am

Thanx for a quick replay ... and yes I was referring to union ( rather than array ).

Its fine as long as you are aware of the issue and a fix is coming.

Any idea when ?

As for InsertAndCopyRange ( ), InsertAndCopySheets ( ) its really the same issue.

I call ConvertFormulasToValues( ) before InsertAndCopyRange ( ) or InsertAndCopySheets ( )

and ConvertFormulasToValues failes to recalculate Large((A2, C2, E2), 1)

Thanx again

V

adrian · November 12, 2013, 10:40am

The recalculation has already been fixed here internally.

Now, we are targetting a release for next week, since I also want to add a couple of other features before a new dot release. Tomorrow I am leaving to an Embarcadero event and I'll be returning on friday, so this week is mostly lost. If you need it urgently, I can make a build today before leaving. If you can wait, it would probably be better to wait for an official release next week.

Vladimir_Vuleta · November 12, 2013, 12:12pm

Next week is fine

Thanks a lot

V