Dollar Sign not showing in Cell for Currency

[lilahb]

Hi. I used the subtraction formula in a cell, then selected Currency & Accounting to add a dollar sign, but it does not add a dollar sign in front of my total number amount. It shows in the cell I had a Sum formula, but not this one. Why not?

[Sam Capricci]

that cell might not be formatted as currency or accounting (they are different) and if it is formatted that way, then check the format to see that what is in the Symbol box is not "None" but has the sign you want.

[lilahb]

There is the dollar sign symbol in the Symbol box... I'm so confused.

[Sam Capricci]

Does it happen in all your workbooks or just the one? What happens if you hand enter it into the cell?

can you upload a sample (per the instructions at the top of the post in the yellow banner)?

[lilahb]

I just have this one workbook. I've tried using the same formula in other cells, and still doesn't show the dollar sign. I've set both cells I'm using for the subtraction formula to currency and still doesn't show.

[lilahb]

When I hand enter the dollar amount, and set to currency or accounting, it shows the dollar sign. Just not with the formula =IMSUB. But it does show the dollar sign in another cell with the formula =SUM....

[MrShorty]

You are dealing with all real numbers, so I wonder why you are using the IMSUB() function. The IMSUB() function is designed to work with complex numbers, which are stored in Excel cells as text strings, so the IMSUB() function returns a text string -- even if the inputs are all real and the output is real. Number formatting does not change the display of text strings (which includes numbers stored as text like in this case). solutions I see:

1) Use a regular subtraction operator =B28-C25. This results in a number (not text).
2) Add to the formula something will convert from text to a number. With complex numbers, one would use =IMREAL(IMSUB(...)), but one could also use any number of other strategies for converting numbers stored as text to numbers.

The solution you choose depends entirely on your reason for using the IMSUB() function. Since I doubt that an accounting spreadsheet ever expects to actually use complex numbers, I would expect the first solution to be the simplest and most appropriate.

[Sam Capricci]

I have never used the INSUB formula. This site... https://www.excelfunctions.net/excel...-function.html notes that that formula outputs text which will not allow you to format as currency or accounting.
if you add *1 at the end of it that will change the output into a number and the dollar sign will appear and it will convert the output to numeric instead of text.
so either of these will work and the dollar sign will appear and any downstream references to that cell will work because it will convert to numeric...
=--IMSUB(B28,C25)
or
=IMSUB(B28,C25)*1

[lilahb]

THANK YOU!!!!!! I don't know how I got this formula to subtract. But, duh, the simple formula you provided is the one I need to use!! It's been years and I completely forgot the formulas, lol. That solved IT!

[lilahb]

THANK YOU BOTH! Mr. Shorty and Sam, using a regular subtraction operator (=B28-C25) solved my issue. I so appreciate you all's help in such a quick manner!! God Bless you both! You may hear from me in the near future again

[lilahb]

I am using the simple formula of =B28-C25, but I just wanted to let you know Sam, that your answer =IMSUB(B28,C25)*1 solved it, also. Thank you both again!!

[Sam Capricci]

I should've considered why you were using that formula but I didn't. But as Mr Shorty noted, there are simpler formulas, glad you used his recommendation.
If that takes care of your issue then, don't forget to mark the post as solved using the thread tools dropdown at the top of the post.
AND thank you for the rep!