## Enhancements to Worksheet Calculations

Version: 2021b

Type: Features

Category: Data Handling

Subcategory: Worksheet

Jira: ORG-22633

• idx(): Return row indices of records that meet specified condition

idx(B==100) // Returns indices of values in B that equal 100

idx(B>=20 && C<=50) // Returns indices of records with B column bigger than 20 and C column smaller than 50

idx(left(A,5)$== "Chris") // Returns indices of values in A where first 5 letters are "Chris" idx(diff(B)>1) // Returns indices in B where the difference between one value and the next value is greater than 1 idx(diff(B)>1)[0] // returns the last row index that fits diff(B)>1 condition • ReportCell(): Access values from report sheets • Sum(): Improved to calculate row wise Mean, StdDev, Min, Max, N, Median. E.g. sum(A:F)_sd • More statistics functions: lcl, ucl, mad, geomean, geosd, harmean lcl(vd[, level]) // lower confidence level  ucl(vd[, level]) // upper confidence level  Mad(vd) // Mean Absolute Deviation  Geomean(vd) // Geometric Mean  Geosd(vd) // Geometric SD 6Harmean(vd) // Harmonic Mean • 3rd argument added for more statistics quantities 1 (default, same as current): mean 2 sd, 3 se, 4 min, 5 max, 6 median, 7 sum, 8 RMS E.g. Enter formula ave(A, 10, 2) in Column B’s F(x) cell to calculate standard deviation of every 10 data in column A. • Confidence() – a 4th argument “dist” is added to provide another algorithm. dist=1 is default, same as current result, dist=2 uses Student's t distribution, same as Excel's CONFIDENCE.T function • Cell formula can now use string variable and cell address. [book1]1!A1 // Cell A1 from book1  [A1$]1!A2 // Cell A2 from a different bookname stored in cell A1

[A1\$]1!B[0] // Last cell of col(B) in book name stored in A1

• Support index 0 for last column in Sum( ), e.g. Sum(1!B:0) to calculation rowwise sum from B to last column in 1st sheet.