Forum Discussion

TStar's avatar
TStar
Copper Contributor
Dec 08, 2025

Dependent calculations in tables

Hi all, I'm using a formula to calculate amounts into a master column in budget sheets depending on the currency. I'm pulling currency rates with the stock function into a separate sheet with named ...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 08, 2025

I usually move away from tables for calculation fields in order to use dynamic arrays.  A shorter version of your formula may be obtained by taking the multiplication by the 'total' field outside the conditional statement and also by using the SWITCH function.

= total * SWITCH(Cur,
      "EUR", 1,
      "NOK", nokeur,
      "SEK", sekeur,
      "GBP", gbpeur,
      1
  )

It is difficult for an end user to tamper with an array formula without destroying it altogether (somewhat conspicuous!)

Resources