Writing a formula with formatting

[kevt97]

I'm constanting working with mixed data...units and dollars. Being able to write the formula such as this would help: =$30,000/20,000...(vs =30000/20000) everytime I try this I get an error. I dont want it to be text. I want to enter into the cell with commas and dollar signs. I used Excel everyday for over 10 years and never thought to do this. Does anyone know how ?

 

[GTO]

Hi Kev,

I am less than stellar w/formulas and may misunderstand what it is you are after, but have you tried DOLLAR?

If A1 = $30,000.00
B1 = 20000

=DOLLAR(A1/B1,2)

...would return $1.50

Does that help?

Mark

 

[shemayisroel]

I'm constanting working with mixed data...units and dollars. Being able to write the formula such as this would help: =$30,000/20,000...(vs =30000/20000) everytime I try this I get an error. I dont want it to be text. I want to enter into the cell with commas and dollar signs. I used Excel everyday for over 10 years and never thought to do this. Does anyone know how ?

Interesting query but not sure if possible as excel uses the $ within formulas for absolute referencing and uses the comma to seprate functions within the formula... etc

 

[schielrn]

Welcome to the Board!

If you really must do this or really want to, you will need to put them in quotes:

="$30,000"/"20,000"

And if you want it to come out with dollar signs you will need to format the cell as such or use text to do it:

=TEXT("$30,000"/"20,000","$ #.00")

Hope that helps.

 

[shemayisroel]

Welcome to the Board!

If you really must do this or really want to, you will need to put them in quotes:

="$30,000"/"20,000"

Hope that helps.

Hmmm yeah that is a way around it...

 

[kevt97]

Thank you so much, never knew that "" was the work around. any issues I need to worry about if I do that ?

 

[schielrn]

I'm not aware of any issues other than maybe with other compatibilty versions of Excel. Not sure if a Non-US version would recognize the dollar sign and know that this should be numbers, but I would assume that it would be fine across all versions.

Glad that helps.