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.

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.

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.

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.

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

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

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

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

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

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.

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.

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.

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)

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.

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

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

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.

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

=FIBONNACI(SomeNumber) Suggestion: The correct spelling for function should be FIBONACCI, else user may not find the function.

CIRCLE(Radius)

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

SPHERE(Radius)

Issue: Output not getting displayed.

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

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

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

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)

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

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)

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.

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

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)

RANKANDPERCENTILE

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

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

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.

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	B
1	-250000	-8000000
2	20000	90000
3	30500	25000
4	25000	20000
5	37000	10000
6	28000	5000
7	11%	10%
8	12%	9%

1. MIRR(A1:A6,A7,A8)= -30%(Excel)

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

MINOR ISSUES

IMCONJUGATE

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

-- this is ok.

DATE,datevalue,EDATE

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

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)

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)

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

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

MAGIC SQUARE