Manuals/calci/CALCITOCHECK

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

This is OK.

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 Comments: Reading the explanation, this issue can be ignored. --- Swapna

PRODUCT function

• If the cells in calci have numerical as well as text and empty cells, PRODUCT function assigns '0' to text and empty cells (instead they should be ignored).

e.g. PRODUCT(A1:A5) returns '0' where A1=2,A2=4,A3=TEXT,A4= ,A5=1

Expected result: 8

Observed result: 0

• Function works correctly with logical values. i.e. assigns TRUE=1, FALSE=0

Please check in next release

next release: please test all combinations of aggregate functions like SUM AVG etc in ZOS and CALCI modes and empty cells.

• Test Comments (PRODUCT):

-- Issue still persists as mentioned. -- Empty cells are ignored while used in ZOS. But empty cells are assigned 0 value while used in reference cells of Calci. e.g. In ZOS, PRODUCT(2,,4) returns '8'

    In Calci, IF A1=2, A2=, A3=4 then, =PRODUCT(A1,A2,A3) returns '0'.

--Text values assigned 0 value, but should be ignored. -- Logical values both TRUE and FALSE identified as '1' in ZOS.(Logical values work fine in Calci) e.g PRODUCT(2,TRUE,4) returns '8' and PRODUCT(2,FALSE,4) returns '8' --- Swapna

OR function

• If the cells in calci have numerical as well as text and empty cells, OR function assigns '0'(FALSE) to text and empty cells (instead they should be ignored).

e.g. OR(TEXT, ) returns FALSE

Expected result: #NULL

Observed result: FALSE

Please check in next release

next release: please test all combinations of aggregate functions like SUM AVG etc in ZOS and CALCI modes and empty cells.

• Test Comments (OR):

-- Issue still persists as mentioned. -- Swapna

HOUR,MINUTE,SECOND

• If the hour value >24, or Minute value > 59 or Second value > 59, Calci does not automatically advance the time with respect to input times.

e.g. HOUR("25:33") displays '#ERROR' (Output can be '1')

MINUTE("12:72:20") displays '0' (Output can be '12')

SECOND("12:10:72") displays '0' (Output can be '12')

• Function with serial numbers of time as input -

TIMEVALUE("3:30") gives 0.1458333333284827 (which is correct)

If the above serial number is used as input to HOUR, MINUTE and SECOND functions, following are the observations :

• HOUR(0.1458333333284827) displays '19' (Expected Output: '3')
• MINUTE(0.1458333333284827) displays '0' (Expected Output: '30')
• SECOND(0.1458333333284827) displays '0' (correct. But displays '0' for other serial numbers too)

The output is same(i.e. 19,0,0) for any other serial numbers (e.g 0.9479166666642413 for "10:45 PM").

HOUR tests

Getting this after UTC corrections etc. need more testing after next release. The 0.95 and 0.99 seem ok. At 1.0 it becomes 0.

HOUR(0.0..1.0..0.05)

0
1
2
3
4
6
7
8
9
10
12
13
14
15
16
18
19
20
21
22
0

MINUTES test

MINUTE(0.0..1.0..0.1)

0
24
48
12
36
0
24
48
12
36
0

SECOND gave 0 for the above intervals. but for the following it gave non-zero results. SECOND(0.0..1.0..0.341)

0

2

4

These look ok.

Please check if such result to adjust for UTC is required for DAY MONTH etc.

TEST NOTES

Modified. Could we test similar situations for DAY, MONTH, YEAR etc. because generally it needs to reflect the UTC vs. LOCAL TIME issues in these functions. For example, HOUR(0.148) should give for 3 on UTC and depending on local time could be different in local time. (18 due to the local time different. 3:30AM is found as 7PM (previous night) hours CST etc.)

Need close checking.

• Test Comments (HOUR,MINUTE,SECOND):

-- Issue still persists as mentioned. -- Swapna

GOLDENRATIO

a)Suggestion: Argument name can be changed to 'logic_value' instead of 'smallphi'

Reason: Argument can be TRUE or FALSE or 1 or 0 for this function. 'smallphi' and 'capitalphi' are the output values obtained.

b)Calci does not give error for -

*Invalid arguments like GOLDENRATIO(7), GOLDENRATIO("ABCDEF"), GOLDENRATIO(-8)
*Multiple arguments like GOLDENRATIO(100,77)

c)Calci help displays incorrect values for smallphi and capitalphi.

• Observed result:

GOLDENRATIO(TRUE) returns 1.618033988749895

GOLDENRATIO(FALSE) returns 0.6180339887498948

• Expected result:

--see exp -- GOLDENRATIO(TRUE) returns 0.6180339887498948, value of capitalphi Φ --see exp -- GOLDENRATIO(FALSE) returns 1.618033988749895, value of smallphi φ

d)If argument is ignored, i.e. GOLDENRATIO()

• Observed result: 0.6180339887498948, value of capitalphi Φ
• Expected result: 1.618033988749895, value of smallphi φ

Reason: smallphi is default value for Goldenratio. Capitalphi is the conjugate value.

GOLDENRATIO EXPLANATION

GOLDENRATIO(TRUE) returns 1.618033988749895 GOLDENRATIO(FALSE) returns 0.6180339887498948 This is correct. parameter is smallPhi is true, If so answer should be 1.61 etc. if false should be 0.6181 etc. If argument is not defined or is not false, it is considered to be true. So junk value giving smallPhi equivalent is safe. - jp

• Test Comments (GOLDENRATIO):

-- GOLDENRATIO(TRUE), GOLDENRATIO(FALSE) seem to be ok. -- GOLDENRATIO() returns 0.618033..... (value of capital phi). Should it be 1.6180....(value of Small phi)? ----Swapna

HYPERLINK

• Link to specified location gets created in Calci. But document/website does not open up.

e.g.

=HYPERLINK("http://www.google.com","Click here")

• Test Comments (HYPERLINK)

-- Issue still persists as mentioned. --- 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).

• Test Comments: Issue resolved. Working ---- Swapna

ANOVASINGLEFACTOR

• Function displays correct results for analysis by "COLUMNS" option. But is incorrect for "ROWS"

e.g. Consider the example on page http://wiki.zcubes.com/Manuals/calci/ANOVASINGLEFACTOR

=ANOVASINGLEFACTOR(A1:B4,0.05,"ROWS",TRUE) displays same result as with COLUMNS (which is incorrect). Only COLUMNS 1,2 are replaced with ROW 1, 2.

ANOVASINGLEFACTOR([1 3;7 8; 12 5;17 18],0.05,"COLUMNS",TRUE) gave result in ZOS: ANOVASINGLEFACTOR([[1,3],[7,8],[12,5],[17,18]],0.05,"COLUMNS",TRUE)

Anova: Single Factor SUMMARY

Groups	Count	Sum	Average	Variance
Column1	2	4	2	2
Column2	2	15	7.5	0.5
Column3	2	17	8.5	24.5
Column4	2	35	17.5	0.5

ANOVA

Source of Variation	Sum of Squares	Degree of Freedom	Mean of Squares	F	Probability	F Critical
Between Groups:	247.375	3	82.45833333333333	11.993939393939394	0.018128968083516894	6.591451475716938
Within Groups:	27.5	4	6.875
Total:	274.875	7

Please check this in the next release

• Test Comments:

-- Analysis by "ROWS" returns the same result values as that of "COLUMNS". e.g. ANOVASINGLEFACTOR([[1,3],[7,8],[12,5],[17,18]],0.05,"COLUMNS",TRUE) returns same result as ANOVASINGLEFACTOR([[1,3],[7,8],[12,5],[17,18]],0.05,"ROWS",TRUE) --Only the text is changed from Column1,Column2,Column3,Column4 to Row1,Row2,Row3,Row4 --- Swapna

HISTOGRAM(Array, BinRange, NewTableFlag)

Propery of NewTableFlag:

• $NewTableFlag$ can be a logical value TRUE or FALSE. If omitted, Calci assumes it to be FALSE.
• If $NewTableFlag$ 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

MOVINGAVERAGE(Array, PeriodInterval, NewTableFlag)

• $NewTableFlag$ if TRUE, should display output on new zspace cube.
• $NewTableFlag$ 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

EXPONENTIALSMOOTHING(Array, DampingFactor, NewTableFlag)

• $NewTableFlag$ if TRUE, should display output on new zspace cube.
• $NewTableFlag$ 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

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.

• Test Comments : {} notation works in spreadsheet platforms. Hence {},[] both work in Calci and [] works in ZOS. This issue can be ignored --- Swapna

COVARIANCEDATAANALYSIS(Array, GroupBy, NewTableFlag)

Issue: Calci does analysis by Columns when "ROWS" (all capital letters)or "rows" (all small letters) is used. Functions properly for Rows(first letter capital). Actually Columns is default method of analysis.

e.g Array A = 4,5,6,7 , Array B = 8,9,10,11

• COVARIANCEDATAANALYSIS(A1:B4,"ROWS",TRUE) or COVARIANCEDATAANALYSIS(A1:B4,"rows",TRUE) - does analysis by Columns

• Correct result for - COVARIACOVARIANCEDATAANALYSIS(A1:B4,"Rows",TRUE)

• Test Comments (COVARIANCE):

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

DESCRIPTIVESTATISTICS function

Syntax: DESCRIPTIVESTATISTICS(Array, GroupBy, ConfidenceLevel, KthLargest, KthSmallest, NewTableFlag)

• Issue1: Even if NewTableFlag = FALSE, o/p displayed in different Zspace. Not on same sheet where command is written.
• Issue2: Calci does analysis by Columns when "ROWS" (all capital letters)or "rows" (all small letters) is used. Functions properly for Rows(first letter capital).

Actually Columns is default method of analysis.

• Test Comments (DESCRIPTIVESTATISTICS):

-- 1)Issue still persists as mentioned. O/p displayed in new zspace for both TRUE and FALSE flags. -- 2)Analysis results by "rows" or "ROWS" are correct. Only the heading displayed as Column 1, Column2, Column 3 etc instead of Row 1, Row 2, Row 3 etc. --- Swapna

NETWORKDAYS(start_date, end_date, [holidays])

Issue: Incorrect result for following example: A1= 1/1/2014 , A2= 2/28/2104

NETWORKDAYS(A1,A2) displays 41.

Correct result is 43

• Test Comments (NETWORKDAYS):

-- Issue still persists as mentioned. --- 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

• Test Comments (MID) : Issue resolved. Working ----- 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.

• Test Comments (SUBSTITUTE) : Issue resolved. Working ----- Swapna

SEARCH(ftext, otext, snum), SEARCHB, FIND, FINDB

Use: Function searches string 'ftext' in 'otext'. Search to begin from position mentioned in 'snum'.

Issue: If 'snum' is non-integer, the result also is non-integer.

suggestion: If 'snum' is non-integer, it should be truncated and then search should be carried out.

e.g.

SEARCH("C","WELCOME",2.2) 

• Expected result: 4
• Observed result: 4.2

Similar issue with SEARCHB, FIND, FINDB

• Test Comments (SEARCH):

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

FIBONNACI(SomeNumber)

Suggestion: The correct spelling for function should be FIBONACCI, else user may not find the function.

• Test Comments (FIBONNACI):

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

Both spellings supported. 04/28/2014 jp

CIRCLE(Radius)

Issue: CIRCLE.AREA(), CIRCLE.CIRCUMFERENCE() not working. To be checked after next release.

• Test Comments (CIRCLE):

-- Issue still persists as mentioned. -- Swapna

SPHERE(Radius)

Issue: Output not getting displayed.

• Test Comments (SPHERE):

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

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

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

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

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

RANDOMNUMBERGENERATION BERNOULLI

Syntax: RANDOMNUMBERGENERATION(NumVariable,RandomNumber,"Bernoulli",NewTableFlag,ProbabilityValue)

=RANDOMNUMBERGENERATION(3, 4, "Bernoulli", TRUE, 0.5)

Issue: Output not displayed. Displays result table values as 'undefined'.

• Test Comments (RANDOMNUMBERGENERATION BERNOULLI):

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

TEXT

Syntax: TEXT(SomeValue, SomeFormat) e.g. - TEXT(23,"$00.00") returns $23.00

ISSUES: 1) The O/P text is not displayed correctly in Calci for date formats - e.g - TEXT(39300,"YYYY-MM-DD") returns - 2007-08-06 (in Excel) 39,300.00 (in Calci)

2) The O/P text is not displayed correctly in Calci for exponential formats- e.g. - TEXT(39300,"0.00E+00") returns- 3.93E+04 (in Excel) 39,300 (in Calci)

• Test Comments (TEXT):

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

ISSUES FROM TRIVANDRUM

MAJOR ISSUES

GEOMETRIC FUNCTIONS

• In geometric functions like cone,triangle,ellipse etc, it is displaying only the small diagram of that solids.
• For any point it is showing only one picture.
• For e.g.,

1. CONE(5,10)
2. CYLINDER(100,200)
3. ELLIPSE(5,10)
4. TRIANGLE(4,8,12)
5. VECTOR(2,6,7)

• For these values it is displaying only one diagram with out the measurement.
• Also no other functions based on these functions are not working.
• For e.g.,CONE.SURFACEAREA, CONE.VOLUME,VECTOR.UNITVECTOR,VECTOR.DOTPRODUCT,TRIANGLE.PERIMETER,TRIANGLE.AREA etc also not working.

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)

-fixed internally. please check future release. BETADIST(0.4,8,10) 0.35949234293309396 04/28/2014

- for later: note also need BETA.DIST(x,alpha,beta,cumulative,[A],[B]) cumulative which is now missing. look at details. could be function object with DIST inside. this is a different function.

ROMAN

• In this function the result is different compared to Excel. When the 2nd argument value is taking only 0.i.e., The t value is not calculating for 1,2,and 3.
• ROMAN(128,0)= CXXVIII(EXCEL) = Cv15529VIII(CALCI)
• ROMAN(999,1)= LMVLIV(EXCEL) = CMXCIX(CALCI)
• ROMAN(999,2) = XMIX(EXCEL) = CMXCIX(CALCI)
• ROMAN(999,3) = VMIV(EXCEL)= CMXCIX(CALCI)

- this is correct (though not optimized for lower representation ). will enhance it later. - ROMAN(128,0) corrected internally so as not to get v1* on it.

SIGN

• when the argument is of a complex number , Calci is taking the sign of a imaginary part.
• But it should consider the real part sign.
• SIGN(-5+6i) = -1(Excel) = 1(Calci)
• SIGN(5-6i) = 1(Excel)= -1(Calci)

- need to see how to handle complex here. something enhance on arg looping? jp

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)

-fixed internally. check next release. ERF(3,2) -0.004655826850866933 4/28/2014

Some formatting issues here.

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)

-- this is correct. accuracy should be a decimal number like 0.0001. ERFC(2,0.000331) gives 0.004680490395387205

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)

Fixed 4/28/2014 GROWTH([2001,2002,2003,2004,2005],[1000000,1100000,1252000,1375000,1500000]) 2001.0811609385346

RANKANDPERCENTILE

• This function for the rows arrangement the result is wrong.
• RANKANDPERCENTILE(ar,rows,FALSE), the result is wrong compared with excel.

Please give an exact example with input etc.

REGRESSION

• For the more x values(two columns of x values) the result is error.

Spreadsheet

	A	B	C
1	Unit sales	Ads	population
2	4000	12000	300000
3	5200	13150	411000
4	6800	14090	500000
5	8000	11900	650000
6	10000	15000	800000

1. REGRESSIONANALYSIS(A1:A5,B1:C5)= NAN

Need exact ways to test this and results expected. Excel does not have this function.

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)

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)

SUMSQ

• This function is considering the numbers of the complex number.
• But Excel is taking only the real part of the complex number.
• SUMSQ(2+3I,4+5I) = 106(CALCI) = 20(EXCEL)

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

-- works internally. it also worked in prod. please check again.

• Again the result shows "nullm" check the below image- Abin

jp 04032014 Fixed internally. Check in next release.

SAMPLING

• In this function type of the sampling is RANDOM, then the result is correct.
• But the type is PERIODIC, it is not showing the result.

Spreadsheet

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	10	20	11	24	19	17	23	65	10	5	14	45	28

1. SAMPLING(A1:M1,PERIODIC,5)=NULL

TREND

• For this function some additional values also displaying.
• But we couldn't take the answers from calci sheet.

UNIFORM

• In this function it is showing only random numbers between the limits, but it is not showing the value of uniform distribution.
• UNIFORMDISTRIBUTED(5,3,6)=1/3(by checking)
• =5.522187389200553,3.566177821950987,5.04674904467538,5.301322509767488,4.9094569575972855(CALCI)

ZTESTEQUALMEANS

• Since its not possible to drag the values over 20 columns, its not possible to check the result.

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)

- Please retry and report. I do not see a second array here.

• Sir, removed the second Array which is not required. Still the results show error - Abin

- This is fixed internally. Please wait for release after 4/3/2014

MINOR ISSUES

IMCONJUGATE

• Calci displays Conjugate of 3+4i as 3+-4i.
• It should display 3-4i

-- this is ok.

DATE,datevalue,EDATE,DAY

• In Calci Serial starting date is not 1900 as compared with Excel.
• If month is greater than 12, it will carry over to the next year.
• For example, DATE(2008,14,2) returns the serial number representing February 2, 2009.
• It is same for date greater than 31.
• DATE(1600,12,31) = 12/31/3500(Excel)
• DATE(1600,12,31) = 12/31/1600(CALCI)
• DATEVALUE("6/7/1960") = 22074(EXCEL) = 58599(CALCI)
• EDATE("1/1/1500",2) = 60(EXCEL) = Thu Mar 01 1500 00:00:00 GMT+0530 (India Standard Time)(CALCI)
• DAY function is not showing correct result.
• DAY("4/9/2019") = 10(CALCI) = 9(Excel)

BESSEL FUNCTIONS

• BESSELI,BESSELY and BESSELK functions only showing the result for 1st derivative.
• It should show the result for nth derivative.
• For e.g.,
• 1.BESSELI(3,2)= 2.245212431(Excel) this is the n th derivative(In(x))
• = 3.9533702171(Calci)this is the 1st derivative(I1(x))
• 2.BESSELY(0.7,4) = -132.6340573(EXCEL)Yn(x) = -1.1032498713(CALCI)Y1(x)
• 3.BESSELK(5,2)= 0.005308944 (EXCEL)Kn(x) = 0.0040446134(CALCI)K1(x)
• BESSELJ functions is not working for some values.
• BESSELJ(2,3)= 0.12894325(EXCEL)Jn(x)
• = 0.10728467204(calci)J1(x)
• BESSELJ(7,2) = -0.301417224(EXCEL)Jn(x)
• = NULL

BETAINV

• In this function the CALCI result is different compared with EXCEL.
• For e.g.,BETAINV(0.359492343,8,10) = 0.399999976(EXCEL)
• BETAINV(0.359492343,8,10) = 1.75(CALCI)

GAMMA FUNCTIONS

• In GAMMAINV and GAMMALN functions the result is different compared with excel.
• 1.GAMMAINV(1,9,3) = 119.4248486(EXCEL result) = 82.51739521528073(CALCI result)
• 2.GAMMALN(1) = -0.00000000004171(EXCEL result) = 0.00018319639111644828(CALCI result)

LOGINV

• In this function result value is different.
• LOGINV(0.039084,3.5,1.2) = 4.000025219(EXCEL) = 3.9957031(CALCI)
• LOGINV(0.24786,6.25,3.12)= 61.83892171 (EXCEL) = NULL(CALCI)

-- this is ok. LOGINV(0.039084,3.5,1.2,0.00001) gives better accuracy. To keep speed default gives close approximation in these iterations.

-- Seems like ITERATE is not converging in the following case. Possibly the -ve values? check later: LOGINV(0.24786,6.25,3.12)= 61.83892171 (EXCEL) = NULL(CALCI) even 10000 as loop limit did not help.

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).

CLEAN

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

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)

-- Check this. May explain FINV also. Excel and Calci differ on: FDIST(1..200,5,1,0.00001) - check http://en.wikipedia.org/wiki/F-distribution the equation is different from FDIST(SomeNumber,DegreeOfFreedom1,DegreeOfFreedom2)

FINV

• Here the iterated values are different compared with Excel.
• Also for smaller values result is 199.9 in CALCI.
• FINV(0.001,5,1)=199.9(CALCI)=576404.5557(Excel)
• FINV(0,4,2)=199.9(CALCI)=1000000000(Excel)

ISNONTEXT

• When we are giving the text as the argument, the result shows as "Null".
• But the answer should come as FALSE

Spreadsheet

	A	B	C
1	74509	"65142"	MONTHS
2		"months"

1. =ISNONTEXT(B1) = Null
2. =ISNONTEXT(C1) = Null
3. =ISNONTEXT(B2) = Null

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)

4/28/2014 Results seem correct in calci, TRIMMEAN([14,18,21,19,20,23,8,10,7,18,12,11],0.2) 15.1 Execution Time: (Step 1)2755 msec. Execution Time:2831 msec. $>TRIMMEAN([14,18,21,19,20,23,8,10,7,18,12,11],0.4) 15.25

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)

Done. 04/28/2014 jp integrals converted to INT.

TTEST

• When the argument value of ty is 3, then the result is different compared with Excel.
• Also when the value of ty and ta in value of decimals, then calci is not considering the values.

Spreadsheet

	A	B	C	D	E	F	G
1	3	5	14	10	8	2	15
2	11	19	5	4	15	7	3

1. =TTEST(A1:G1,A2:G2,1,3) = 0.382340251837
2. =TTEST(A1:G1,A2:G2,2,3) = 0.76468050367
3. =TTEST(A1:G1,A2:G2,2.2,1.3) = NAN

3 is unequalvariance. let us see what is the issue there. please check jp

ZTEST

• In this function, the decimal values in the result is different compared with excel.

Spreadsheet

	A	B	C	D	E	F	G
1	10	15	7	2	19	20	12
2	3	4	8	1	10	15	5

1. =ZTEST(A1:G1,4) = 0.00042944272036(CALCI) = 0.000431321(Excel)
2. =2*MIN(ZTEST(A1:G1,4),1-ZTEST(A1:G1,4)) = 0.000858885440(CALCI) = 0.000862641(Excel)

PPMT

• In this function ,the result is different compared with excel for the ty value is 1.

1. PPMT(8%/12,1,4*12,10000,1000,1) = -260.14(Excel)

• =-193.474537727(CALCI)

Quadratic

QUADRATIC(1,-10,34) The result shows correct but the format is wrong. "+" and "-" comes together

This is ok. 4/28/2014 jp

NORMAL

• In this function is generating only random numbers.
• But it should give the result according to the formula of the normal distribution.

1. NORMALDISTRIBUTED(250,255,2.5) = 0.022(By checking)

• =748.545737759758,417.93831835416444,-92.67846228553037,etc(CALCI)

1. NORMALDISTRIBUTED(30,36,20.9) = 0.018(By checking)

• = 7.937852412035841,10.385286729354199,83.54572095198611 etc (CALCI).

Check again. NORMALDISTRIBUTED is intended to give random numbers back. NORMDIST is a different function.

NORMDIST(250,255,2.5) 0.021596386605275224 NORMDIST(30,36,20.9) 0.018317552240650948

MAGIC SQUARE

• When we are checking with other sites, it is saying that MAGICSQUARE of order 2 is not possible to construct.
• CALCI is giving the result for the magicsquare of size 2x2, but sum of rows and columns are not same.
• MAGICSQAURE(2) gives wrong result

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)

4/28/2014 This works. It should be like: 11 .C. 5 462 Execution Time: (Step 1)15 msec. Execution Time:113 msec. $>10 .P. 4 5040