Google-sheets – How to change the sum of different currencies based on a drop-down value (currency)

google sheets

I'm trying to achieve that when you select 'USD' from the dropdown e.g. it automatically calculate the two tickers with different currency into the currency you select. What I would like was that the columns with 'total' would show me the right formatting. So right now it's only numbers but I would like the formatting to be '$10,150.54' if the currency was set to USD in the dropdown and '10,150.54 kr.' if DKK is selected.

https://docs.google.com/spreadsheets/d/1VLv4zvoSWIS8ldDAqoyX4uk2OtQzcoZBx6L3FTebg68/edit?usp=sharing

I have tried something like this: https://docs.google.com/spreadsheets/d/1qxPR0WkRow3lZxp-IODwP0oifOXFdu90xAbktF-TlQ4/edit#gid=1369942674

It doesn't do exactly what I wanted but it's close. Although, when I do something like this: =VLOOKUP(A3,J6:K10,2,0)&G10 I get an odd result of: kr. 10150.5403080106. Would I be able to change the formatting so it would be something like kr. 10.150,54?

Thanks!

Best Answer

You want to display a Currency prefix for the sum of some transactions based on a drop down Currency indicator. Initally, your problem was that the balance wasn't rounding, and this was resolved by using round().

You then switched to: =ROUNDUP(CONCATENATE(VLOOKUP(A3,J6:K10,2,0),G10),2) but this displayed "1590.48" and removed the currency symbol.

There are two issues involved in this question:

  1. Spreadsheet locale
  2. must the total be a number or may it be a string?

Consider this snapshot:

Snopshot

Spreadsheet Locale

concatenate() appends strings to one another. This means that the result is a string... However, if the string is prefixed by the currency indicator for the spreadsheet locale, concatenate() will return the result as a currency value. This can be rounded, and the result is a currency value. On the other hand, if the currency indicator does not apply to the spreadsheet locale, concatenate() will return the result as a string and round() will generate an error.

  • when the Spreadsheet locale is "United States" AND

    • when the currency is "$" (USD), concatenate will return a number and round will be successful.
    • when the currency is "kr" (DKK), concatenate will return a string and round will generate an error.
      • 'kr.9814.59766852607' is a text and cannot be coerced to a number.

  • when the Spreadsheet locale is "Denmark" AND

  • when the currency is "kr." (DKK), concatenate will return a number and round will be successful.

  • when the currency is "$" (USD), concatenate will return a string and round will generate an error.

    • '$1618,35627709946' is a text and cannot be coerced to a number.

Number vs String

A string can be successfully generated when the formula sequence is reversed .. The result looks something like this:

=concatenate(vlookup(A3,J6:K10,2,0),round(G10,2))

  • lookup the currency indicator
  • round the market value
  • concatenate the rounded value with the currency indicator

Note: I also tried CONCAT() (which joins values) as well as CONCATENATE() (which joins strings). There is no difference in the results.

Related Topic