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 cells for each rate that I'm using. My budget table has a "Total" column where I put in the amount, a "Cur" column where I select the currency, and then the master column where it's converted into the currency that I'm working with for each budget.

The formula I'm currently using is this:

=IFS([@Cur]="EUR";[@TOTAL];[@Cur]="NOK";nokeur*[@TOTAL];[@Cur]="SEK";sekeur*[@TOTAL];[@Cur]="GBP";gbpeur*[@TOTAL];[@Cur]="";)

nokeur, sekeur, gbpeur are named cells containing the currency rates. The last argument is only to not get a REF error on empty rows. (Semicolons instead of commas due to my language settings)

This has gone through a bunch of revisions over the years and I'm constantly trying to shorten the formula as much as possible. This is the shortest I've managed to get it though. I wanted to check if anyone has any advise on how to make it more elegant. the formula does exactly what I want so it's not a problem per se, it's more out of interest.

Also a related topic: sometimes when others are using my sheets, they will mistakenly put values in the Master column, overwriting the formula. This is hard to spot, and ideally I'd like to lock those cells/that column so you can't replace what's there. Locking the workbook can of course achieve this but it also prevents the user from adding additional lines in the table. Is there any smart way to get around this? I've also tried with conditional formatting to clearly highlight the cells if they don't contain a formula but I haven't managed to make it work properly.

excel
Formulas and Functions

3 Replies

  • TStar's avatar
    TStar
    Copper Contributor
    Dec 10, 2025

    Thank you both! Just tried out Peter's solution and it works really well. Just recently learned about the SWITCH function and this is a good, practical way of using it.

    The XLOOKUP solution also makes sense, I'll experiment a bit with it. Makes it easy to add in new currencies if needed, for example. As for locking, I tried it out now and it does work, except that I couldn't add more rows to a table, which won't work for my application. 

  • 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!)

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Dec 08, 2025

    If the master column has a label column next to it:

    Name    Value

    Euro      [EUvalue]

    NOK      [NOKvalue]

    SEK        [SEKvalue]

    and then you can 'Format as a Table' and call that table something like "cTable" then the formula could be:

    =XLOOKUP([@Cur], cTable[name], cTable[value]*[@Total], "")

    having a table like that also makes it easier to maintain.

    As for locking it you should be able to lock the sheet and use the options to allow insert rows and lock/unlock particular cells.

Resources