Aspose Cells Excel Index Formula Issue

Aspose.Cells Product Family
1

Hi,

I am trying to display the few currency values in terms of KILO,MILLIONS,BILLIONS etc.
Please see tha attached for the Expected result image,Input Template excel and Used. I tried below options but nothing works fine for negative,zero and above 1000 Billion values. Also INDEX function is throwing exception when used with Aspose Cells.


&=&=LOOKUP(1,B{r}/1000^{4,3,2,1,0},TEXT(B{r}/1000^{3,2,1,0},"[<0]#.00
;# “) &{“B”,“M”,“K”,”"})

&=&="$ " &
ROUND(B{r}/1000^INT(LOG(B{r},1000)), 2) &
INDEX({"",“k”," M"," B","
T"}, LOG(B{r},1000)+1)

[Invalid dynamic formula:="$" & ROUND(C{r}/1000^INT(LOG(C{r},1000)), 2,INDEX({"",“K”,“M”,“B”,“T”}, LOG(C{r},1000)+1))[

Please help to fix this.

Regards,
Praveen J
2

Hi Praveen J,


Thanks for the template file and sample code.

I have tested your scenario/ case by using your sample code with your template file a bit, I observed the issue (exception) as you mentioned. Could you please provide us a sample expected file having your desired formulas pasted against the markers in the cells. For example, you may provide us a working sample Excel formulas against your underlying dynamic formula of Smart Markers i.e., “&=&=”$" & ROUND(C{r}/1000^INT(LOG(C{r},1000)), 2,INDEX({"",“K”,“M”,“B”,“T”}, LOG(C{r},1000)+1))", so we could evaluate your issue properly and check it soon.

Thank you.
3

Hi,

I have attached the Achieved and Expected result. PFA.

Thanks,
Praveen.
4

Hi,


Thanks for providing us template file.

After an initial test, I observed the issue regarding exception as you mentioned by using your sample code with your template file. I got:
“[Invalid dynamic formula…]” error involving Index function/formula while using Smart Markers’ dynamic formula in the template file.
The exception is due to inserting any of the following dynamic formulas into the cells:
i.e…,

&=&="$ " & ROUND(B{r}/1000^INT(LOG(B{r},1000)), 2) & INDEX({"",“k”," M"," B"," T"}, LOG(B{r},1000)+1)
&=&=LOOKUP(1,B{r}/1000^{4,3,2,1,0},TEXT(B{r}/1000^{3,2,1,0},"[<0]#.00 ;# “) &{“B”,“M”,“K”,”"})
I have logged a ticket with an id “CELLSJAVA-41246” for your issue. We will look into it to figure it out soon if possible.

Regarding your mentioned error in B4 cell, this is correct behavior due to your formula for the specified value in A4 cell as when you insert MS Excel formula e.g “=”$" & ROUND(A4/1000^INT(LOG(A4,1000)), 2) & INDEX({"",“K”,“M”,“B”,“T”}, LOG(A4,1000)+1)" into B4 cell manually and press enter, you will also get “#NUM!” error too.

Thank you.



5

Hi,

Please update me once you fix the issue. May I know approximate duration when it gets fixed.

Regards,
Praveen J
6

Hi Praveen,


The ticket was logged just a few hours back, and it is currently pending for analysis. We cannot share an ETA unless we have analyze the presented scenario to identify the problem cause. Please spare us some time to properly look into the matter. In the meanwhile, we will keep you posted with updates in this regard.
7

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells
for Java v8.4.0.5 and let us know your feedback.

8

The issues you have found earlier (filed as CELLSJAVA-41246) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.