Calculating a formula with relative cell references and providing row/col context

[qcurtemanjc]

Hello,

I have a use case where I have a formula like so =$B1="Yes" and I would like the cell references to adjust to their relative value based on the row/col I'm on. For example:

• If I'm on Row 1, the formula would be =$B1="Yes"
• If I'm on Row 2, the formula would be =$B2="Yes"

I was hoping I could use the calculateFormula function for this but it looks like I cannot add row/col context and I haven't been able to find any helper function that can convert a formula relative to a row/col provided. Can you help with this?

For more context, I am trying to replicate this behavior in Google Sheets

Please let me know if you have any more questions, thanks!

[sequba]

Hi @qcurtemanjc, thanks for reaching out.

If I understand your use case correctly, you would like to perform the calculation outside your data sheet. The calculateFormula method is a good way of doing that.

The obvious solution is to iterate through all the rows:

for (let rowNum = 1; rowNum < 100; rowNum++) {
  console.log(hf.calculateFormula(`=B${rowNum}="Yes"`, 0));
}

But you can also use the array arithmetic feature to calculate that formula for the whole column at once and obtain the result in the form of an array.

const resultArray = hf.calculateFormula('=ARRAYFORMULA(B1:B99="Yes")', 0);

or

const resultArray = hf.calculateFormula('=ARRAYFORMULA(B:B="Yes")', 0);

Let me know, if this solution works for you ;)

[qcurtemanjc]

Hey @sequba

Thank you for the response! I have been informed that Hyperformula's "Named Expressions" module might be able to help me achieve what I'm looking for (https://hyperformula.handsontable.com/guide/named-expressions.html#available-methods)

Essentially, I would like to be able to feed a function a formula and a delta and receive the formula translated to a new position based on the delta. The example I provided above was only one example, for my use case, any formula can be used and so I need a solution more flexible than ARRAYFORMULA.

Another use case example might be like this:

Formula: "=COUNTIF($C$2:$C$20, C2)>1"
Delta: {row: 4, column: 0}
Result: "=COUNTIF($C$2:$C$20, C6)>1"
(notice the C2 in the first formula has been translated to C6: 4 rows and 0 columns)

I tried to add this formula to Hyperformula's "Named Expressions" module but it said Uncaught Error: Relative addresses not allowed in named expressions.

Does Hyperformula have a module that can do this?

[wojciechczerniak]

Looks like a job for getFillRangeData 🦸 . Although it needs sheet to work in a context. Delta is calculated automatically between target and source but should be easy to calculate target on delta. It does not modify the sheet. You need to setCellContents or use copy and paste programmatically (they also calculate delta).

[sequba]

As pointed out by @wojciechczerniak, you can achive that with getFillRangeData. Try something like this:

function translateFormula(formula, deltaRows, deltaColumns) {
  const hf = HyperFormula.buildFromArray([[ formula ]], { licenseKey: "gpl-v3" });

  const sourceCell = { sheet: 0, row: 0, col: 0 };
  const targetCell = { sheet: 0, row: deltaRows, col: deltaColumns };

  const [[ translated ]] = hf.getFillRangeData(
    { start: sourceCell, end: sourceCell },
    { start: targetCell, end: targetCell }
  );

  return translated;
}

console.log(translateFormula("=COUNTIF($C$2:$C$20, C2)>1", 40, 1));

[qcurtemanjc]

@sequba @wojciechczerniak This is exactly what I needed. Thank you!!