# Sharepoint Online Calculated column

Copper Contributor

# Sharepoint Online Calculated column

Hi All,
I am currently trying to use calculated columns whereby to do calucaltion.
I want the calculated Column TotalAmountSGD, to calculated IF the Currency is SGD then Calculate the Cost of the, Else Currency is Not SGD then Calculate ConvertedSGDRate.

can i know what is the correct formula should i put

 Currency Cost ConvertedSGDRate TotalAmountSGD(Calculated Column) SGD 100 100 SGD 200 200 YEN 18000 176.4 176.4 USD 50 71.78 71.78 YEN 8000 79.01 79.01 SGD 50 50 MYR 100 25 25 USD 150 1545.71 1545.71
5 Replies

# Re: Sharepoint Online Calculated column

@Winterkid you've not said where you're putting the rate of exchange in to do the calculation, because in your image you see to have already done the calculation. Is it a lookup to another list that holds the daily exchange rate for each currency? One way you can make life easy for yourself if you just do a calculated column with cost*rate and if the currency is SGD then the rate is 1 as in my example image below.

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

# Re: Sharepoint Online Calculated column

the exchange rate will be key in from powerapp application itself from my finance members.
as our currency default is SGD my finance colleague will not be going into the application to manually key 1 into the exchange rate as it serve no purpose for them by doing so.

If i set the default value 1 to the exchange rate then it will appear and calculate all the value which will provide false data to the employee before finance colleague update the exchange rate.

# Re: Sharepoint Online Calculated column

hi anyone can help me with me on this.

# Re: Sharepoint Online Calculated column

Keying in exchange rates leads to user errors. You would be better to have a list with all the currencies and current exchange rate as a data source. Put that in a gallery on a screen in your app so you can update the exchange rate on a daily basis or however often you do it. Then in your main screen your users would select the currency from a dropdown and it would add the rate in automatically and do whatever calculation you need. This could be a formula so that if the dropdown.Selected.Value = "SGD" then it multiplies it by 1 otherwise it multiplies your total by the exchange rate. Or in your currencies list you just have one otem as SGD with the rate as 1 which would never need to change.

# Re: Sharepoint Online Calculated column

HI @RobElliott thank you for you reply.
understanding your recommendation on creating a data list for the exchange rate.
Due to the timeline i have which i need to start pilot testing with user start of next month, i maynot have enough time to make it.

For now, i will still get my finance colleague to manually enter the exchange rate.

in the TotalAmountSGD i have set formula in and it work for now.

``=IF(Currency="SGD",Cost,ConvertedSGDRate)``

if you have idea on how to create it please do let me know.
attached picture is from my finance colleague for today exchange rate and they only see the middle rate.