Invalid formula oddities

Aspose.Cells Product Family
1

I’m getting some odd behaviour with the formula property.

The following formula produces an invalid formula error, and this is the formula I started with before attempting to rework it so that I didn’t get an error:

=(Targets_Album!D6 * (Pricing_Album!AB8 * (1 - Pricing_Album!AC8)))+(Targets_First_Single!D6 * (Pricing_First_Single!L8 * (1 - Pricing_First_Single!M8)))

The following reworked also formula produces an invalid formula error:

=((Targets_Album!D6 * Pricing_Album!AB8)-(Targets_Album!D6 * Pricing_Album!AB8 * Pricing_Album!AC8))+((Targets_First_Single!D6 * Pricing_First_Single!L8)-(Targets_First_Single!D6 * Pricing_First_Single!L8 * Pricing_First_Single!M8))

Wherease the following, equivalent, formula is okay (the difference is in the brackets):

=(Targets_Album!D6 * Pricing_Album!AB8)-(Targets_Album!D6 * Pricing_Album!AB8*Pricing_Album!AC8)+(Targets_First_Single!D6 * Pricing_First_Single!L8)-(Targets_First_Single!D6 * Pricing_First_Single!L8 * Pricing_First_Single!M8)

This formula is produced as part of a loop of variable length. So going back to the first formula the code can generate:

=(Targets_Album!D6 * (Pricing_Album!AB8 * (1 - Pricing_Album!AC8)))

…and this does not produce an error.

A final couple of examples that might help locate the problem. This formula does not produce an error (but is also not correct for my purposes):
=(Targets_Album!D6 * (Pricing_Album!AB8 * (1 - Pricing_Album!AC8)))+(Pricing_First_Single!M8)

This formula is very close to the formula above but does produce an error.
=(Targets_Album!D6 * (Pricing_Album!AB8 * (1 - Pricing_Album!AC8)))+(1 - Pricing_First_Single!M8)



Note that all the above formula work fine when I type them into Excel and (as far as I’ve seen) produce the same result.

I don’t have the time to reduce this problem down to a simple complete example to post/email, but I would appreciate knowing answers to the following:

a) Is this is a known error?

b) Can this error can be produced due to the values that have been input into the cells that the formula references?

Thanks,

Josh

2

Hi Josh,

Which version are you using? The latest hotfix is 1.9.1.3.

3

If this problem still exists on the latest hotfix, please send me your template file and your code on this issue.

Thank you.

4

The problem does still exist. I was previously using 1.9.1.2, now I’m using 1.9.1.3. I can’t email through the code I’ve got at the moment as it’s too complicated and relies on several other code layers to get the data. If I get the time I can try and reproduce it in a simpler example, but we’re going live tomorrow so not sure I’ll find the time!

Regards,

Josh

5

Hi Josh,

I will check this problem right now. To speed up the check, I still have some questions:

Do you use a template file? If yes, could you email it to me?

6

Thanks Laurence.

Yes, we do use a template file, and I’ll email that over. Note that we dynamically create copies of the three sheets that are in the file, then just before saving the file we delete the original three. In the example where it goes wrong, there is only one “Summary” sheet (for the album) and that contains figures that are the results of sums/multiplications from the album’s pricing and target sheets and the single’s target and pricing sheets.

Unfortunately, the columns in the pricing and target sheets are generated automatically too, so the template file won’t show these columns.

Regards,

Josh

7

Hi Josh,

Please download the latest hotfix and have a try.