Issue
So far, I am trying to integrate the worksheet function "AVERAGEIFS" into my main code block in Excel Office Scripts. I want to use it in a similar way to how VBA handled it, where I could call the function within my main code. Here, it would push to avgAvai
the averageifs
value for the given input. It would take the average of all of the values in the P column whose D column matched a given input.
However, I can't understand how to use await/context to do so. When I attempt to put this into my main function, it reports a problem that property: 'Function' does not exist on type: 'Workbook'.
let avgAvai: number[];
for (let rowIndex = 3; rowIndex < rowCount; rowIndex++){
if ((usedRangeValues[rowIndex][15] != 0) && (usedRangeValues[rowIndex+1][15] = 0) ){
avgAvai.push(workbook.function.averageifs("P:P", "D:D", usedRangeValues[rowIndex][3]))
}
}
How would I work to integrate this?
Solution
Office Script
does not support a method equivalent to Application.WorksheetFunction
in VBA.
- Using a UDF to simulate
AverageIf
function main(workbook: ExcelScript.Workbook) {
// for testing
let selectedSheet = workbook.getActiveWorksheet();
let rowCount = 5;
let usedRangeValues = selectedSheet.getUsedRange().getValues();
// ***
let avgAvai: number[] = [];
for (let rowIndex = 3; rowIndex < rowCount; rowIndex++) {
if ((usedRangeValues[rowIndex][15] != 0) && (usedRangeValues[rowIndex + 1][15] = 0)) {
avgAvai.push(getAverageIf(selectedSheet, usedRangeValues[rowIndex][3] as string))
}
}
}
function getAverageIf(selectedSheet: ExcelScript.Worksheet, criteria: string): number {
const avgFormula = "=AVERAGEIF(D:D,\"@\",P:P)"
const tmpCellLoc = "AA1" // modify as needed
let tmpCell = selectedSheet.getRange(tmpCellLoc);
tmpCell.setFormulaLocal(avgFormula.replace("@", criteria));
let returnVal = tmpCell.getValue() as number;
tmpCell.setValue("");
return returnVal;
}
- If you don't want to use a temp cell, please try below.
function getAverageIf(selectedSheet: ExcelScript.Worksheet, criteria: string): number {
let lastRow = selectedSheet.getRange("D:D").getLastCell().getRangeEdge(ExcelScript.KeyboardDirection.up).getRowIndex() + 1;
let valueD = selectedSheet.getRange("D1:D" + lastRow).getTexts();
let valueP = selectedSheet.getRange("P1:P" + lastRow).getValues();
let resP: number[] = [];
let rowCount = valueD.length;
for (let i = 0; i < rowCount; i++) {
if (valueD[i][0] === criteria) {
resP.push(valueP[i][0] as number)
}
}
let sum = resP.reduce((acc, num) => acc + num, 0);
let average = sum / resP.length;
return average;
}
Answered By - taller
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.