SharePoint List calculated column formula

Question

_{Friday, September 7, 2018 7:46 AM}

Hi i have a requirement to convert the currency value in SharePoint list column on the basis of the selected currency in the other column.

For Example- i selecting the column Value 1 like AUD,GBP from the drop down and in the other column it should get converted into INR.

some where i want to define the value of 1AUD and GBP in INR. please help me on this. Thanks..

All replies (8)

_{Monday, September 10, 2018 8:47 AM ✅Answered}

Hi,

If you have 23 currencies to choose, you might want to create a custom list to manage these currencies and their according exchange rate to INR.

In Currency list:

1. You need to create one currency title text column and one exchange rate to INR number column.

In original list:

1. You need to create a lookup text column called "FC" in your original list to lookup for your Currency list title.

2. Create a number column called "Exchange Rate" which will be assigned value with workflow designed in the next step

3. Create a 2010 workflow related to the original list in SharePoint Designer

4. For "Porjection in INR" calculated currency column, enter formula:

=[Exchange Rate]*[Billing Currency]

Then the result will be like(you can hide unnecessary columns as you desired):

Best Regards,

Sarah Yang

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

_{Saturday, September 8, 2018 5:53 PM}

Hi,

not sure that I have understood this completely..  you want to get a conversion between two currencies using calculated columns.  If this is the case you need to set the exchange rate in a secondary column to use in the calculation.  You can use a look up and a workflow or a set the value in a column when you fill in the form from the list.  If you send more details I may be able to help more

Carla

_{Monday, September 10, 2018 5:57 AM | 1 vote}

Hi,

Based on your description, my understanding is that you want to have one number column and one choice column which choose between AUD and GBP, and then you transfer AUD or GBP to INR in another calculated column as shown below.

To achieve this, you just need to enter the following for formula for the Calculated column(the number is based on today’s currency exchange rate: 1 Australian Dollar equals 51.30 Indian Rupee and 1 Pound sterling equals 93.25 Indian Rupee):
=IF( [AUD/GBP] = "AUD",51.30*[Value] , 93.25*[Value])

Best regards,
Sarah Yang

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

_{Monday, September 10, 2018 6:24 AM}

Hi Sarah,

Thanks for your response.

Actually there are 23 currencies(will come in drop down in 1 column) which needs to be converted in INR so please suggest if we can go with choice column option.

Regards,

Ramakant

Ramakant Rathore

_{Monday, September 10, 2018 6:28 AM}

Hi It would be like in FC(foreign currency- There are currencies), User will put the value in billing currency, that should be converted in to INR. We need to define the currency conversion somewhere. 

Please guide me on this.

Regards,

Ramakant

Ramakant Rathore

_{Wednesday, September 12, 2018 5:27 AM}

Hi Ramakant,

Have you tried the solution I provided above?

If you think my solution is helpful, you could mark it as an answer. It will help others who meet the similar question in this forum.

Best regards,

Sarah Yang

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

_{Wednesday, September 12, 2018 6:51 AM}

Hi Sarah,

Thank You so much.. it was really very helpful in fulfilling my request.

Ramakant Rathore

_{Wednesday, January 30, 2019 8:36 PM}

Hello friend, I have the following problem at the time of the formula in the field calculated from the way you indicate me.



= IF ( [Moneda] = "USD", 3.4 * [Monto], 1 * [Monto])

please .... i need HELP !! thanks