Urgent Help - How to add currency symbol + Correct Price Commas to Cell
Hi there,
I'm after some help as im pulling my hair out - I am trying to pull over the correct currency symbol and the right pricing commas for example $85,450 or C$85,450 - I currently have a formula set-up in the currency symbol field for whenever someone selects the currency drop-down in the form in correctly populates
=IF(Currency@row = "USD", "$", IF(Currency@row = "AUD", "A$", IF(Currency@row = "EUR", "€", IF(Currency@row = "NZD", "NZ$", IF(Currency@row = "GBP", "£", IF(Currency@row = "CAD", "C$", ""))))))
The "Service Pricing" field is what needs to pull over the correct currency symbol however this currently already has a formula within which calculates the amount of hours for dev build from various cells and *250 so how would I also add a new formula that pulls over "currency symbol" with the price commas :D
Thanks so much!
Answers
-
Hi @Aaron Stenlake,
You should be able to achieve this by adding two formulas together, so in the ‘Services Pricing’ column, you could do either of the following:
- =[Currency Symbol]@row + EXISTING FORMULA IN ‘SERVICES PRICING’ COLUMN
- =IF(Currency@row = "USD", "$", IF(Currency@row = "AUD", "A$", IF(Currency@row = "EUR", "€", IF(Currency@row = "NZD", "NZ$", IF(Currency@row = "GBP", "£", IF(Currency@row = "CAD", "C$", "")))))) + EXISTING FORMULA IN ‘SERVICES PRICING’ COLUMN
The second option would allow you to remove the ‘Currency Symbol’ column altogether since you’re including the entire formula from that column within the ‘Services Pricing’ column.
Does that work for you?
Georgie
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Georgie Hi Georgie,
Thanks i'll try this now, will this also add the pricing comma's correctly? for example I need it to show "£62,500" instead of "£62500.
Thank you!
-
I've tried both and im receiving the below
-
Hey @Aaron Stenlake,
You can format the column to add the thousands separator format - to do so, click the column header so that the whole column is highlighted, then click the comma , button on the top toolbar (click the three-dot menu on the top toolbar if you can’t see it). For more on this, take a look here.
Hope that helps!
Georgie
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Georgie
Any help with the above screenshot with the formula not working?
The current formula is the below, how would I write it exactly so I can copy and paste, thank you!!
=[Total Hours Included For Build]@row * [Base Price]#
-
Hi @Aaron Stenlake ,
Sorry, looks like I missed your latest comments when I posted my last one! In your Services Pricing column, try:
- =[Currency Symbol]@row + ([Total Hours Included For Build]@row * [Base Price]#)
Does that work?
Georgie
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hey @Georgie
That formula worked a treat! thank you so much
May I ask with the comma / pricing help you gave me, I found the comma and selected it but it didn't seem to do anything and also the numbers moved over to the "left" side of the cell anyway to revert this and also make the pricing commas static to the whole sell like a formula?
Thanks
-
Hey @Aaron Stenlake,
Glad the formula worked!
Ah of course, the reason that’s happening is because the values that are returned by the formula are mixed number and text values, since we’ve added in the currency symbol as text. The thousands separator can’t work on mixed values, only numeric values.
However, it is possible to use formulas to ensure that you can still include the comma in your values. This related thread provides a couple of different ways of doing it and all the steps you’d need to take:
You can also select the desired alignment for the column - similarly to the comma format, you’d click the column header, then click the alignment icon on the top toolbar and select left, center, or right align.
Hope that helps!
Georgie
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Georgie
Just to bring this back to light,
Im trying to push over the right currency coversation at USD - for example if the baseline price is in USD, I want to get the prices in different currencies for example GBP - Now I've added GBP in the sheet summary field with the rate to the USD which is 0.84 and in theory I just need to mulitply the "service pricing" by the sheet summary field value to get this but im getting invalid operation?
Can you help? Thanks!!
-
Hey @Aaron Stenlake,
The INVALID OPERATION error is presented since the formula is unable to calculate, because the Services Pricing column contains a mixed text/number value. However, you could change the formula you’re using in the GBP Total column so that all the values being used in the formula are numeric values, as follows:
- =([Total Hours Included For Build]@row * [Base Price]#) * GBP#
You’ll then be able to format the GBP Total column to add the £ symbol and the thousands separator to the column by clicking the column header and then selecting the comma and the currency symbol icon on the top toolbar.
You can also then use the above as a base formula for your other currency total columns, creating other sheet summary fields for the conversion rates and changing each formula to reference the relevant summary field.
Hope that helps!
Georgie
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!