Manuals/calci/CALCITOCHECK

Please move items to [| this page ] after completion and final testing (Please provide date and test data).

Please enter dates on comments or issues posted.

-- NOTES ON ISSUES --

Next release ver 5.0.101 will be released after 5/10/2015. Watch for a notice on this area indicating release for testing. Latest release number should be indicated on the issues indicated.

PRODUCT, AVG Functions

• If the cells in calci have numerical as well as text and empty cells, PRODUCT and AVG function results in ZOS and Calci vary with respect to Excel.

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

Expected result: 8

Observed result: 0

Comparison table with input values including empty cells, text, logical values-

Function	O/p in EXCEL	O/p in ZOS	O/p in Calci	Comments
PRODUCT(2,,4)	8	Error	A1=2, A2= ,A3=4 then =PRODUCT(A1,A2,A3) returns 0; but =PRODUCT(A1:A3) returns 8	A2 is empty cell
PRODUCT(2,TEXT,4)	8	8	A1=2, A2=TEXT ,A3=4 then =PRODUCT(A1,A2,A3) returns '0'
PRODUCT(2,TRUE,4)	8	8	A1=2, A2=TRUE ,A3=4 then =PRODUCT(A1,A2,A3) returns '8'	TRUE, FALSE values ignored in Excel and ZOS
PRODUCT(2,FALSE,4)	8	8	A1=2, A2=FALSE ,A3=4 then =PRODUCT(A1,A2,A3) returns 0; but =PRODUCT(A1:A3) returns 8	TRUE, FALSE values ignored in Excel and ZOS
AVG(2,3,4,,5)	3.5	Error	A1=2, A2=3 ,A3=4, A4= ,A5=5 then =AVG(A1,A2,A3,A4,A5) returns 2.8; but =AVG(A1:A5) returns 3.5	A4 is empty cell
AVG(2,3,TEXT,4,5)	3.5	3.5	A1=2, A2=3 ,A3=TEXT, A4=4 ,A5=5 then =AVG(A1,A2,A3,A4,A5) returns '3.5'
AVG(4,TRUE,6)	5	3.6666666666666665	A1=4, A2=TRUE ,A3=6 then =AVG(A1,A2,A3) returns 3.6666666666666665; but =AVG(A1:A3) returns 5	TRUE, FALSE values ignored in Excel
AVG(4,FALSE,6)	5	3.3333333333333335	A1=4, A2=FALSE ,A3=6 then =AVG(A1,A2,A3) returns 3.3333333333333335; but =AVG(A1:A3) returns 5	TRUE, FALSE values ignored in Excel

• Test Comments ---Swapna(9/22/15)

PRODUCT, AVG results vary as mentioned in table above with respect to Excel.

OR

• Comparison table with input values including empty cells, text, logical values-

Function	O/p in EXCEL	O/p in ZOS	O/p in Calci	Comments
OR(0,)	FALSE	#ERROR	A1=0, A2= ,B1=0, B2=TEXT then =OR(A1,A2) returns TRUE; but =OR(A1:A2) returns FALSE	A2 is empty cell
OR(0,TEXT)	FALSE	TRUE	B1=0, B2=TEXT then =OR(B1,B2) returns TRUE; also =OR(B1:B2) returns TURE
OR(TEXT,)	#VALUE!	#ERROR	A1=TEXT, A2= ; then =OR(A1,A2) returns FALSE	A2 is empty cell

• Test Comments (09/22/2015)

- Outputs vary with respect to Excel as mentioned in the table.

HOUR,MINUTE,SECOND

• Test Comments: Swapna(09/22/15)

-- Issue with HOUR resolved.

-- Issue with MINUTE resolved.

-- Issue still persists with SECOND

1)For any value in seconds, output displayed is Zero.

1. e.g. SECOND("12:20:10") displays '0'. (instead it should be '10')

2)value is not carry forwarded if SECOND>59 e.g.

1. SECOND("12:10:72") displays '0' (Output can be '12' as in Excel)

DESCRIPTIVESTATISTICS function

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

• e.g. Consider the following example-

	Column1	Column2	Column3	Column4
Row1	Scores
Row2	23
Row3	38
Row4	45
Row5	21
Row6	17
Row7	21

1. =DESCRIPTIVESTATISTICS(A1:A6,"Columns",95,1,1,TRUE,TRUE)

• Test Comments (Swapna 09/22/2015)

Issue 1 -

NewTableFlag=TRUE or FALSE gives #NULL error. O/p displayed only when NewTableFlag is ignored.

Issue 2 -

IsFirstRowLabels does not show any effect in the output.

i.e In Excel for above example, if IsFirstRowLabels=TRUE, the column name gets changed from Column1 to Scores.

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 -

1. TEXT(39300,"YYYY-MM-DD")

returns - 2007-08-06 (in Excel) but 39,300.00 (in Calci)

2) The O/P text is not displayed correctly in Calci for exponential formats- e.g. -

1. TEXT(39300,"0.00E+00")

returns- 3.93E+04 (in Excel) but 39,300 (in Calci)

• Test Comments (TEXT):

-- Issue still persists as mentioned. --- Swapna (09/22/15)

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.
• The above issue is occurring again.

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

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

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

• BETA.DIST(0.4,8,10,0,3,4)=Null
• For any values BETA.DIST is not working.Please let me know the cumulative value for this function- Devi(6/10/2015)

• TEST THIS WELL. 10182015.

BETA.DIST(0.4,8,10,0,3,4) Should the number be not between 3 and 4?

Also what is the different output in Excel for cumulative and not-cumulative? Give examples. Detail how to get the function activated in excel.

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)
• The above issue is occurring again.-(6/10/2015)

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

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)
• The above issue is occurring again. Devi(6/10/2015)

TREND

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

• Now it is showing the result as "Null"-Devi(9/1/2015)

Fixed. Please check and close.mary (06/10/2015)

ZTESTEQUALMEANS

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

• The above issue is occurring again.-(6/10/2015)

-10172015 will see what to do for this. For now, select the area by click on a cell, scroll down and press shift+click.

LEVENES TEST

• This function result is displaying only Test statistic alone.
• Also it can give mean,median and variance value also.Then it may useful for the calculation.
• The test statistic value is different compared with Excel.

Spreadsheet

	A	B	C
1	11	21	28
2	12	20	36
3	14	18	48
4	17	14	51
5	19	10	20
6	22	17	43
7	24	25	24

1. =LEVENESTEST(A1:C7,0.05,TRUE)= 0.7391791881(Calci) = 5.991(Excel)

Calci output

Excel Output

Fixed. Please check and close. MARY(06/10/2015)

• Result differ from Excel Output. Please Check(9/16/2015)

MULTIPLE REGRESSION ANALYSIS

• This function showing the result as null

Spreadsheet

	A	B	C
	AGE	CHOLESTROL LEVEL	SUGAR LEVEL
1	58	189	136
2	69	235	149
3	43	198	165
4	39	137	140
5	63	178	162
6	52	160	152
7	47	198	142
8	31	183	129

• =MULTIPLEREGRESSIONANALYSIS(A1:A8,B1:C8,0.05,TRUE)

EXCEL RESULT:

MINOR ISSUES

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)
• The above issue is occurring again.

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)
• The above issue is occurring again.-Devi(6/10/2015)

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.

• The above issue is occurring again.-Devi(10/3/2015)

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)

-- jp 4/28/2014 Will need to check this one. If we set more wider settings, it can get better. FINV(0.001,5,1,0.0000001, [10, -100000000000, 10000000000]) -11864.687689386672

• The above issue is occurring again.-Devi(10/3/2015)

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 Fixed 10/17/2015 - good testing Devi.

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

• The result is correct for value of ty is 3,but when we are assigning decimal values ,it is not convert in to integers.
• The above issue is occurring again.-Devi(6/10/2015)
• The above function is working for INTEGER values, but it is not considering the decimal values for the 3rd and 4th arguments-Devi(10/3/2015)

jp I got 3 5 14 10 8 2 15 11 19 5 4 15 7 3 0.420003304 0.745340071 in excel and TTEST([3 5 14 10 8 2 15],[11 19 5 4 15 7 3],1,3) 0.37285157760157517 $>TTEST([3 5 14 10 8 2 15],[11 19 5 4 15 7 3],2,3) 0.7457031552031503 in calci. Seems different. Will test and find out what is the difference here.

Issue: jp: Need to check why TDIST in Excel at the integral values wrap in different ways. Try changing degree of freedom in =TDIST(2,dof,1) from 1 to 20 by 0.05. Why does the border line integers switch arbitrarily? TDIST appears good in ZOS, but need to see if there is any real explanation for the integral wrap over in excel.

IMLOG10

• In calci IMLOG10 syntax is IMLOG10(Complexnumber,base).
• But there is no need of mentioning the second argument "base".
• Because IMLOG10 base value is 10 only.

-corrected.

• Also it is not working as the base. It is taking as the next value.
• For e.g.,IMLOG10("2+3i")=0.556971+0.426821i
• IMLOG10("2+3i",2) = 0.556971 0.426821
• 0.301029 2.728752
• In this 1st line values are IMLOG10("2+3i") result and 2nd line is the result of IMLOG10(2).
• So the 2nd argument 2 is not working as the base value.
• Also when we are giving the complex number with out the double quotes it is displaying some other answer.
• For e.g.,IMLOG10(2+3i)=0.0.69897+2.7287i

$>IMLOG10("2+3i",2) 0.5569716761534182 0.42682189085546657 0.30102999566398114 0 IMLOG10("2+3i",2)

0.5569716761534182	0.42682189085546657
0.30102999566398114	0

Fixed on Release 5.0.101

• The above function is showing the correct result with the double quotes, but without the double quotes is showing the wrong result.
• The above issue is occurring again.-Devi(10/3/2015)

Try IMLOG10(["2+3i",2])

0.5569716761534182	0.42682189085546657
0.30102999566398114	0

jp 10/17/2015

KRUSKALWALLISTEST

• This function is giving the different result compared with Excel.

Spreadsheet

	A	B	C
1	25	28	30
2	32	34	32
3	42	45	45
4	52	55	50
5	60	61	65

=KRUSKALWALLISTEST(A1:C5,0.05,TRUE) Calci Output

Excel Output

Fixed. Please check and close. Mary (06/10/2015)

• Result differ from Excel Output. Please Check(9/16/2015)

jp 10/17/2015. CALCI assumes average rank of 4.5 if two elements have 4th rank, which is one way of handling this. Please check on theoretical technique detail on KRUSKALWALLIS test on elements of similar rank. Please give such detail.

Friedman test

• This function is not showing any result.
• Also for this function giving for the table of sign test.

Calci Output

Excel Output

Fixed. Please check and close. Mary(06/10/2015)

• Output Shows Null. Please check (9/16/2015)

fixed. jp. 10.17.2015. Check in release 4.0.2.2

Idempotent

• For Idempotent matrix, MATRIX("idempotent") is not showing the idempotent matrix.
• For this syntax, it is showing only zero matrix.(Devi- 29/4/2015)

-- 05102015 jp This may be tough to do http://en.wikipedia.org/wiki/Idempotent_matrix since it depends on how the MMULT will work. Let us think about it. Does other platforms give results for this?

MINOR CHANGES

• For some functions we are getting the results with the different decimal values

Resolved

04112015 -jp these are ok and are correct.

• EXP(LN(7)) = 6.999999999999999(CALCI) = 7(EXCEL)
• GAMMADIST(8.15372,5,7,TRUE) = 0.0068673(EXCEL)= 0.00686967895087527(CALCI)
• SHOULD USE == for comparison. Not =. AND(1/2=0.5,1*-2=2) = FALSE(EXCEL) and (CALCI) = Can't do that!(ZOS)

LOG

log wont give correct answer LOG will give the right answer but its converted to small letter on enter log is for natural base. LOG is as per spreadsheet and db standards. jp.

Updated on 10/20/2015- Devi

Excel Output