How to filter Positive/Negative/Zeros values in Excel column using Office Scripts?

Question

I want to filter negative values, positive values and zeros separately using office scripts. Below excel sheet "Difference" column has negative/positive/zeros. What I want is,

1. Filter Negative values and add certain data(Yes) to "Status" column
2. Filter Positive values and add certain data(No) to "Status" column
3. Filter Zeros and add certain data(Not Applicable) to "Status" column

Any help would be greatly appreciated.

Answer 1

You don't need to filter the table. It can be done more efficiently using an array.

function main(workbook: ExcelScript.Workbook) {
    // modify table name as needed
    let table1 = workbook.getTable("Table1");
    let dRange = table1.getColumn("Status").getRangeBetweenHeaderAndTotal();
    let dValue = dRange.getValues();
    let cRange = table1.getColumn("Difference").getRangeBetweenHeaderAndTotal();
    let cValue = cRange.getValues();
    const rowCount = cValue.length;
    for (let i = 0; i < rowCount; i++) {
        if (cValue[i][0] === 0) {
            dValue[i][0] = "Not Applicable"
        }
        else if (cValue[i][0] > 0) {
            dValue[i][0] = "No"
        }
        else {
            dValue[i][0] = "Yes"
        }
    }
    dRange.setValues(dValue);
}

---

Simplified with conditional (ternary) operator

    for (let i = 0; i < rowCount; i++) {
        dValue[i][0] = cValue[i][0] === 0 ? "Not Applicable" : (cValue[i][0] > 0 ? "No" : "Yes");
    }

Answer 2

=SWITCH(SIGN([@difference]),-1,"N",0,"Z",1,"P")