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.