In an attempt to create custom functions for Excel that accept arrays (e.g., a range of cells) as input, and then return either a value or an array, I am facing some challenges.
Here's an example from my function.ts file:
/* global clearInterval, console, CustomFunctions, setInterval */
// OTHER FUNCTIONS
/**
* Percent bias
* @customfunction
* @param Observed: Observed Data
* @param Simulated: Simulated Data
* @returns Percent bias
*/
function PBias(Observed: number[], Simulated: number[]): number {
let sum_obs = Observed.reduce((partialSum, a) => partialSum + a, 0);
let sum_sim = Simulated.reduce((partialSum, a) => partialSum + a, 0);
return (100 * (sum_sim - sum_obs)) / sum_obs;
}
}
The above function gets built without any issues. However, when I start debugging, I encounter an error stating that my custom functions were not loaded.
If I remove the PBias function, other functions load correctly and work as expected.
These functions seem to be working appropriately as demonstrated in this TypeScript playground demo.
I have experimented with a fresh installation of Node.js (version 18.18.0), and Yeoman Generator for Office Add-ins (version 4.3.1, utilizing Excel Custom Functions with a Shared Runtime).