Calculate the Total Amount based on Currency Selected from Drop Down List
=IF([Currency]22 = "AED", [Amount]22, IF([Currency]23 = "USD", [Currency]23 * [Rate]23, [Amount]24 * [Rate]24))
Best Answer
-
Hey Denny
Did you build the Sheet Summary fields?
Answers
-
Hey @Denny V
One approach is to place the rates in your summary field (right hand ribbon of your page). This will allow you to reference the rates and keep your formula formatted as a column formula. The Summary fields provide an easy way to store this type of referenced data within the sheet so that you don't have to hard-code the rate directly into the formula. If the list of conversion factors grow, we would consider a separate sheet that we could use as a lookup table.
You will create a field for each rate in the Sheet summary section. You can call it whatever you like but I'll name them so you are clear on the formula.,
The formula below is what goes in your TOTAL column
=IF(Currency@row="AED", [AED Rate]#*Currency@row, IF(Currency@row="USD", [USD Rate]#*Currency@row, IF(Currency@row="EUR", [EUR Rate]#*Currency@row)))
Would this work for you?
Kelly
-
Thanks, Kelly for the response. however, it is showing INVALID OPERATIONS
-
Hey Denny
Did you build the Sheet Summary fields?
-
Yes made the following ,
-
Just checking, the formula is working now, or do we still need to tweak it?
-
Dear Kelly ,
The Formulae worked . Thank for your Help
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!