ZCubes Wiki

Manuals/calci/CALCITOCHECKFIXED

Revision as of 14:34, 30 July 2014 by Swapna (talk | contribs)

PLEASE ADD FIXED ITEMS HERE AFTER TESTING

ROWS function

  • The function displays #NULL error with arguments as array values.

e.g. ROWS({1,2;3,4;5,6})

Expected result: 3

Observed result: #NULL

ROWS EXPLANATION

This will work only in CALCI as the {} notation for array is useful only in spreadsheet platforms and similar languages. In ZOS arrays are to be indicated by [] and Objects will use {} notation.

Rows Function Test Comments

Reading the explanation, this issue can be ignored. --- (4/29/2014)Swapna


RANDOMNUMBERGENERATION, RANDOMNUMBERGENERATIONUNIFORM, RANDOMUNIFORM, PATTERENED

  • Random number output is not displayed. Same command is displayed on space cube.

e.g. =RANDOMNUMBERGENERATION(3, 4, "Uniform", TRUE, 3, 4) 

=RANDOMNUMBERGENERATION(3, 4, "Patterned", TRUE,1, 5, 3, 4)

RANDOMNUMBERGENERATION(30, 4, "Uniform", TRUE, 3, 4)

3.195449711289257 3.2248697481118143 3.0816136938519776 3.2126745639834553 3.990793523611501 3.649687875760719 3.759418284520507 3.965467617381364 3.9545328468084335 3.3741041221655905 3.1905515757389367 3.777756262337789 3.9837128871586174 3.432979935547337 3.9010994143318385 3.7845077733509243 3.421978456666693 3.813554910942912 3.168992273742333 3.2010161119978875 3.9937344735953957 3.459437067853287 3.384454872459173 3.0950622770469636 3.1798048589844257 3.407964985817671 3.6846570973284543 3.6611867914907634 3.9072526150848716 3.2141497172415257
3.2669153639581054 3.2465451690368354 3.0314107462763786 3.88823653687723 3.6240376371424645 3.0279260887764394 3.4216481952462345 3.747306031640619 3.515739459078759 3.175762218190357 3.6369324799161404 3.7142539599444717 3.6433018636889756 3.133950341725722 3.6951540084555745 3.4156028230208904 3.240273320581764 3.4762711247894913 3.7170767160132527 3.180914681404829 3.9355433725286275 3.0196601944044232 3.3859861323144287 3.854840762214735 3.37747969920747 3.7828165935352445 3.326533603016287 3.7698845479171723 3.1485248988028616 3.0072317481972277
3.9251055871136487 3.2557102914433926 3.788066220469773 3.207987993489951 3.588850313099101 3.870454262243584 3.9496661769226193 3.4028041230048984 3.6568462373688817 3.6426883353851736 3.9649933751206845 3.2784565046895295 3.246733759297058 3.380512889707461 3.0992100907023996 3.739959954516962 3.3115603323094547 3.2317469376139343 3.5847271364182234 3.594636262860149 3.9285641501192003 3.712517147185281 3.9386905138380826 3.8844250414986163 3.1710384245961905 3.583163051167503 3.137686180183664 3.990223975619301 3.3122955770231783 3.6850603164639324
3.162752259755507 3.126228274544701 3.4762895670719445 3.242924148682505 3.0085414829663932 3.9302729363553226 3.777372183976695 3.940537365153432 3.640196947613731 3.316979159368202 3.1863948297686875 3.9217444960959256 3.964422813616693 3.0397708313539624 3.883876184700057 3.882750508841127 3.485125683248043 3.8928709470201284 3.5129915438592434 3.0280541735701263 3.621288023889065 3.5997950434684753 3.1812141358386725 3.4793242022860795 3.4653837154619396 3.982316398760304 3.9729056453797966 3.0827220352366567 3.775389570510015 3.457127903588116

Is this correct?

Has been corrected so that true false flag will show inside a cube or on ZOS.

Please check this in the next release

(In ZOS it will show result in the window. In CALCI it will create a new window depending on request flag).

RANDOMNUMBERGENERATION Test Comments

Issue resolved. Working ---- (4/29/2014)Swapna


MATCH(lkup_val, lkup_arr, m_type)

Issue: Function does not work in ZOS if lkup_arr represented with {} brackets, whereas works with [ ].

  • ZCalci works with both type of array representation {}, [] and displays correct results -

e.g.

  • MATCH(44,{40,42,44},1) does not work in ZOS.
  • MATCH(44,[40,42,44],1) returns relative position of '44' as 3 in ZOS.

MATCH EXPLANATION

See after new release if issue persists.

Match Function Test Comments

{} notation works in spreadsheet platforms. Hence {},[] both work in Calci and [] works in ZOS. This issue can be ignored --- (4/29/2014)Swapna


MID(txt, snum, noc)

Issue: Returns #NULL error, if 'txt' starts with a number.

e.g.

  • MID("123456",2,4) returns NULL.
  • MID("A123456",2,4) returns A123

MID Function Test Comments

Issue resolved. Working ----- (4/29/2014)Swapna


SUBSTITUTE(txt, otxt, ntxt, instnum)

Use: Function is used to replace nth instance of 'otxt' with 'ntxt'. Original string is in 'txt'.

Issue: If the original string starts with number, Calci displays NULL error.

e.g. 

SUBSTITUTE("123456","123","888",1)
  • Expected result: "888456"
  • Observed result: #NULL (In Calci)
  • Works well in ZOS.

SUBSTITUTE Function Test Comments

Issue resolved. Working ----- (4/29/2014)Swapna

FVSCHEDULE(Principal, Schedule)

Issue: Calci O/p does not match with that in Excel.

e.g. 1)FVSCHEDULE(2,{0.01,0.56,0.02})

  • O/p in Excel: 3.214224
  • O/p in Calci:
Principal Schedule FVSCHEDULE
2 0.01 2
2 0.56 2
2 0.02 2
  • Test Comments (FVSCHEDULE):

-- Issue still persists as mentioned. --- Swapna

Fixed: FVSCHEDULE(2,[0.01,0.56,0.02]) 3.214224 04/28/2014 jp

FVSCHEDULE Function Test Comments

-- Issue resolved. Working -- (4/30/2014) Swapna

TBILLYIELD(SettlementDate, MaturityDate, Price)

Issue: Displays #NULL error for all examples.

e.g. TBILLYIELD(DATE(2013,10,20),DATE(2014,6,20),93.9)

  • Output in Excel : 0.096241
  • Output in ZOS : #NULL (and for any other example)
  • Test Comments (TBILLYIELD):

-- Issue still persists as mentioned. --- Swapna

Fixed 04/28/2014 TBILLYIELD(DATE(2013,10,20),DATE(2014,6,20),93.9) 0.09624107600678411

TBILLYIELD Function Test Comments

-- Issue resolved. Working -- (4/30/2014) Swapna

HISTOGRAM(SomeArray,BinRange,SomePareToFlag, SomeCumulativeFlag, SomeChartFlag, NewTableFlag)

Propery of NewTableFlag:

  •   can be a logical value TRUE or FALSE. If omitted, Calci assumes it to be FALSE.
  • If   is TRUE, the result is displayed on new zspace sheet.

Output Observed:

For TRUE, result table in Calci shows (both on new zspace)

  • Bin, Frequency columns and
  • two more columns showing Bin and Frequency in descending order

For FALSE, Calci displays only Bin and Frequency columns (on new zspace).

Please check this in the next release

  • Test Comments (HISTOGRAM):

-- Issue still persists as mentioned. O/p displayed in new zspace for both TRUE and FALSE flags. --- Swapna

  • Test Comments: (4/30/2014)Swapna

Syntax: HISTOGRAM(Array, BinRange, NewTableFlag)

e.g. A1:A7 --> 1,7,12,17,20,37,50 B1:B5 --> 10,20,30,40,50 

=HISTOGRAM(A1:A7,B1:B5,"TRUE") or =HISTOGRAM(A1:A7,B1:B5,TRUE)
=HISTOGRAM(A1:A7,B1:B5,"FALSE") or =HISTOGRAM(A1:A7,B1:B5,FALSE)

--O/P values displayed correctly with no extra columns when NewTableFlag written in double quotes as "TRUE". Extra columns displayed when written without quotes e.g. TRUE.

--O/p displayed on new cube for both TRUE and FALSE values of 'NewTableFlag'.

-- Issue still persists as mentioned. --- Swapna (5/5/14)

Syntax is: HISTOGRAM(SomeArray,BinRange,SomePareToFlag, SomeCumulativeFlag, SomeChartFlag, NewTableFlag)

Can you retry after next release?

HISTOGRAM Function Test Comments

Results showing correctly. O/P values match with excel. NewTableFlag functions properly 

...Issue resolved. Working --- Swapna (5/18/14)


MOVINGAVERAGE(SomeArray,PeriodInterval,ShowChart, ShowStandardErrorOptions,NewTableFlag)

  •   if TRUE, should display output on new zspace cube.
  •   if FALSE, should display output on same calci sheet of the data and command.

Observed Output:

  • Output displayed on new zspace cube for both TRUE and FALSE.

Please check this in the next release

  • Test Comments:

--Issue still persists as mentioned. O/p displayed in new zspace for both TRUE and FALSE flags --- Swapna

  • Test Comments : (4/30/2014) Swapna

e.g. Consider the below example-

Period Array Input
0 0
1 45.5
2 60
3 200
4 160
5 300
6 340
7 310
8 500
9 566
10 610 
=MOVINGAVERAGE(B3:B13,6,TRUE)
=MOVINGAVERAGE(B3:B13,6,FALSE)

--Issue: In Calci, O/p displayed on new cube for both TRUE and FALSE values of 'NewTableFlag'.

-- Issue still persists as mentioned. --- Swapna (5/5/14)


==05/16/2014 Syntax is: MOVINGAVERAGE(SomeArray,PeriodInterval,ShowChart, ShowStandardErrorOptions,NewTableFlag)

Here ShowChart will be implemented later.

MOVINGAVERAGE Function Test Comments

Results showing correctly. O/P values match with excel. 'NewTableFlag' functions correctly...Issue resolved. Working --- Swapna (5/18/14)


EXPONENTIALSMOOTHING(SomeArray,DampingFactor,ShowChart,ShowStandardErrorOptions,NewTableFlag)

  •   if TRUE, should display output on new zspace cube.
  •   if FALSE, should display output on same calci sheet of the data and command.

Observed Output:

  • Output displayed on new zspace cube for both TRUE and FALSE.

Please check this in the next release

  • Test Comments (EXPONENTIALSMOOTHING):

-- Issue still persists as mentioned. O/p displayed in new zspace for both TRUE and FALSE flags. --- Swapna

== please give an example for this.

  • Test Comments : (4/30/2014) Swapna

e.g. Consider the following example -

Period Array Input
0 0
1 45.5
2 60
3 200
4 160
5 300
6 340
7 310
8 500
9 566
10 610 
=EXPONENTIALSMOOTHING(B3:B13,0.2,TRUE)
=EXPONENTIALSMOOTHING(B3:B13,0.2,FALSE)

--Issue: In Calci, O/p displayed on new cube for both TRUE and FALSE values of 'NewTableFlag'.

-- Issue still persists as mentioned. --- Swapna (5/5/14)

==05/16/2014 Syntax is XPONENTIALSMOOTHING (Array,DampingFactor,ShowChart, ShowStandardErrorOptions,NewTableFlag)

EXPONENTIALSMOOTHING Function Test Comments

Results showing correctly. O/P values match with excel. 'NewTableFlag' functions correctly...Issue resolved. Working --- Swapna (5/18/14)


DB(C, Salvage, L, P, M)

  • For non-integer values of P and M, CALCI rounds up. Excels calculates with exact values.
  • Result differs with that in Excel -
Function CALCI EXCEL
DB(20000,2000,5,5,6.6) 1477.8488.. $1512.06
DB(20000,2000,5,4.4,6) 477.054249 $2396.29
  • Test Comments (DB):

-- Issue still persists as mentioned. --- Swapna

-- Issue still persists as mentioned. --- Swapna (5/01/14)

-- Issue still persists as mentioned. --- Swapna (5/5/14)

05/15/2014 fixed internally. check in release. DB(20000,2000,5,5,6.6) 1512.0578420784902 Execution Time: (Step 1)4154 msec. Execution Time:4227 msec. $>DB(20000,2000,5,4.4,6) 2396.28818079

  • Test Comments:

Output result not reflecting as fixed. Issue still persists as mentioned. --- Swapna (5/18/14)

--5/21/2014 jp DB(20000,2000,5,5,6.6) 1512.0578420784902 Execution Time: (Step 1)16 msec. Execution Time:109 msec. $>DB(20000,2000,5,4.4,6) 2396.28818079

Next release.

DB Function Test Comments

Results showing correctly. O/P values match with excel. ....Issue resolved. Working --- Swapna (7/30/14)

Function CALCI EXCEL
DB(20000,2000,5,5,6.6) 1512.0578420784902 $1512.06
DB(20000,2000,5,4.4,6) 2396.28818079 $2396.29


DDB(C, Salvage, L, P, F)

  • For non-integer values of P, CALCI rounds up. Excels calculates with exact values.
  • Result differs with that in Excel -
Function CALCI EXCEL
DDB(10000,200,5,4.5,2) 1096 $669.25
DDB(10000,200,5,3.7,2) 1096 $1007.09
  • Test Comments (DDB):

-- Issue still persists as mentioned. --- Swapna

-- Issue still persists as mentioned. --- Swapna (5/01/14)

-- Issue still persists as mentioned. --- Swapna (5/5/14)

-- Issue still persists as mentioned. --- Swapna (5/18/14)

--5/21/2014 jp fixed. test next release. DDB(10000,200,5,4.5,2) 669.2515222246417 Execution Time: (Step 1)24 msec. Execution Time:81 msec. $>DDB(10000,200,5,3.7,2) 1007.0901941967786 Execution Time: (Step 1)15 msec. Execution Time:82 msec. $>DDB(300,500,5,3.7,2) 0

DDB Function Test Comments

Results showing correctly. O/P values match with excel. ....Issue resolved. Working --- Swapna (7/30/14)

Function CALCI EXCEL
DDB(10000,200,5,4.5,2) 669.2515222246417 $669.25
DDB(10000,200,5,3.7,2) 1007.0901941967786 $1007.09


FIXED ISSUES FROM TRIVANDRUM

BETADIST

  • In this function normally limit a and b are optional. But in calci a and b are required.
  • So the limit of x is not in 0 to 1.
  • BETADIST(0.4,8,10)= 0.359492343(Excel
  • = NAN(CALCI)

Test Comments (BETADIST)

  • Issue resolved. Working---(5/2/2014) Devi

ERF

  • In this function when ll>ul the result showing in CALCI is 0. But the Excel value is displaying the value of ll < ul with the negative sign.
  • ERF(3,2)= -0.004655645(Excel) = 0(CALCI)

Test Comments (ERF)

  • Issue resolved.Working---(5/2/2014) Devi

ERFC

  • In this function in Calci is assigned two variables like ERFC(x,accuracy), but when we are giving the 2nd variable, then the only result is 1 for any number.
  • ERFC(2) = 0.004677762(calci)
  • ERFC(2,5)=1(calci)

Test Comments (ERFC)

  • Issue resolved.Working---(5/2/2014) Devi

SUMX2MY2

  • This function is giving the result as null for any value.
Spreadsheet
A B C D
1 29 71 10 16
2 16 50 8 14

=SUMX2MY2(A1:D1,A2:D2) = null

Test Comments (SUMX2MY2)

  • Issue resolved.Working---(5/2/2014) Devi

MIRR

  • For this function result is coming as error.
Spreadsheet
A
1 -250000
2 20000
3 30500
4 25000
5 37000
6 28000
7 11%
8 12%
  1. MIRR(A1:A6,A7,A8)= -30%(Excel)

Test Comments (MIRR)

  • Issue resolved.Working---(5/2/2014) Devi

CLEAN

  • In this function result value is different compared with Excel.
  • CLEAN("�double�") = �double�(EXCEL) = double(CALCI)

Test Comments (CLEAN)

  • Issue resolved.Working---(5/2/2014) Devi

TRIMMEAN

  • The result of this function is different compared with Excel.
Spreadsheet
A B C D E F G H I J K L
1 14 18 21 19 20 23 8 10 7 18 12 11
  1. =TRIMMEAN(A1:L1,0.2) = 15.6(CALCI) = 15.1(EXCEL)
  2. =TRIMMEAN(A1:L1,0.4) = 15.625(CALCI) = =15.25(EXCEL)

Test Comments (TRIMMEAN)

  • Issue resolved.Working---(5/3/2014) Devi

TDIST

  • In this function for the arguments values of df and t are taking only integers. It is not accepting the values of decimals.
  • i.e., when we are assigning decimal values ,it should change in to integers.
  • TDIST(2.4579,20.4,1)=0.0122238(CALCI) = 0.011612361(EXCEL)
  • TDIST(2.4579,20.4,1.2)=0.011612361(Excel) = Null(CALCI)

Test Comments (TDIST)

  • Issue resolved.Working---(5/3/2014) Devi

COMBINATION,PERMUTATION

  • In combination and permutation functions when we are using the like 6c2, then the result is wrong.
  1. 11c5 = 11(CALCI) = 462 (By checking)
  2. 10p4 = 10(CALCI) = 5040(By checking)

Test Comments (COMBINATION,PERMUTATION)

  • Issue resolved.Working---(5/3/2014) Devi

MULTINOMIAL

  • This function is giving the different answer for the decimals.
  • For e.g.,MULTINOMIAL(0,1.2,1.3,1.4,1.5)= 24(Excel) = 120 (CALCI)

Test Comments(MULTONOMIAL)

  • Issue resolved.Working---(7/25/2014) Devi

STDEV FUNCTIONS

  • For standard deviation functions like STDEV,STDEVA,and STDEVPA functions is considering only the logical value TRUE, but it is not taking the value of FALSE.
  • i.e., in a logical value FALSE is not taking for the calculation.
  • STDEV(10,15,17,FALSE)= 7.593857167(Excel) = 3.60555127(CALCI)which is equal to STDEV(10,15,17)
  • STDEVA(12,18,27,32,FALSE)=12.61744824(EXCEL)=8.958236433584458(CALCI)
  • Array={2,12,22,32,false}
  • STDEVPA(A1:A5)=12.09297317(Excel)=11.180339887498949(CALCI)

Test Comments(STDEV,STDEVA)

  • Issue resolved.Working---(7/25/2014) Devi

VAR FUNCTIONS

  • For variance functions like VAR,VARA,VARP,and VARPA funtions for logical value TRUE , the result is correct.
  • But the logical value FALSE ,the result is different compared with Excel.
  • i.e., in a logical value FALSE is not taking for the calculation.
  • VAR(10,25,18,FALSE) = 115.5833333(Excel) = 141.592592(CALCI)which is equal to VAR(10,25,18)
  • VARA(10,15,20,25,FALSE)=41.666666666(CALCI) = 92.5(Excel)
  • VARP(40,61,53,46,FALSE)=449.2(Excel) = 549.2(CALCI)
  • VARPA(12,23,34,45,FALSE)=250.96(Excel) = 151.25(CALCI)

Test Comments(VAR FUNCTIONS)

  • Issue resolved.Working---(7/25/2014) Devi

GROWTH

  • For this function result is different compared with Excel.
Spreadsheet
A B C D E
1 2001 2002 2003 2004 2005
2 1000000 1100000 1252000 1375000 1500000
  1. GROWTH(A1:E1,A2:E2) = 1.0000000039046144(CALCI) = 2001.081161(Excel)

GROWTH([2001,2002,2003,2004,2005],[1000000,1100000,1252000,1375000,1500000]) 2001.0811609385346

  • When we are selecting the entries in a horizontal only one result is displaying.
  • But when we are selecting the entries in a vertical few result is displaying.

Test Comments(GROWTH)

  • Issue resolved.Working---(7/25/2014) Devi

ISPRIME

  • In this function ,when we assigning decimals it is giving different result.
  • For e.g., ISPRIME(5.1)= true.But it is not prime.
  • So it should give result as false.

Test Comments(ISPRIME)

  • Issue resolved.Working---(7/25/2014) Devi

IPMT

  • In this function the CALCI result is different compared with EXCEL.
  • IPMT(4.5%/2,3,2*2,50000,10000,1) = -454.78 (EXCEL) = -480.666(CALCI)= No result(ZOS)

Test Comments(IPMT)

  • Issue resolved.Working---(7/25/2014) Devi

FDIST

  • For some values results are different compared with Excel.
  • FDIST(70.120045,12.2,6.35) = 1.93536 E-05(Excel) = 0.000011229898(CALCI)
  • FDIST(10,1.3,1.5) = 0.134947329626(EXCEL) = 0.194982229(CALCI)

Test Comments(FDIST)

  • Issue resolved.Working---(7/25/2014) Devi
Retrieved from "http://wiki.zcubes.com/index.php?title=Manuals/calci/CALCITOCHECKFIXED&oldid=200227"