Sharepoint Online Calculated column

[Winterkid]

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

[#2]

@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)

[#3]

@RobElliott thank you for your fast reply.
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.

[#4]

hi anyone can help me with me on this.

[#5]

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.

[#6]

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.