Urgent Help - How to add currency symbol + Correct Price Commas to Cell

Aaron Stenlake

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!

Georgie

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

Aaron Stenlake

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

Aaron Stenlake

I've tried both and im receiving the below

Georgie

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

Aaron Stenlake

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]#

Georgie

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

Aaron Stenlake

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

Georgie

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:

• Convert Numbers to Text so I can show a range keeping the comma delimiters

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

Aaron Stenlake

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!!

Georgie

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