SOLVED

Cell Formating

%3CLINGO-SUB%20id%3D%22lingo-sub-2151093%22%20slang%3D%22en-US%22%3ECell%20Formating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2151093%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20had%20tech%20support%20help%20me%20via%20quick%20assist%20for%20this%20issue.%20He%20wasn't%20able%20to%20correct%20it%20and%20suggested%20posting%20here.%3C%2FP%3E%3CP%3EI%20have%20included%20the%20spreadsheet%20that%20I%20am%20talking%20about.%3C%2FP%3E%3CP%3EI%20am%20unable%20to%20get%20the%20cell%20H6%20to%20format%20to%20currency%20on%20all%20sheets%2C%20except%20for%20the%20one%20labelled%20overall.%20The%20sheet%20labelled%20overall%20doesn't%20require%20that%20formatting%20though.%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2151093%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2151389%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20Formating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2151389%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F972540%22%20target%3D%22_blank%22%3E%40Kcirevam%3C%2FA%3E%26nbsp%3BI%20wonder%20how%20you%20came%20about%20to%20use%20the%20IMSUB%20function.%20Its%20use%20is%20to%20work%20with%20complex%20numbers%20as%20described%20in%20the%20article%20linked%20below.%20The%20result%20is%20a%20text%2C%20not%20a%20number%20and%20therefore%20can%20not%20be%20formatted%20as%20a%20number%20in%20any%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20should%20use%26nbsp%3B%3DE6-G6%20in%20order%20to%20subtract%20two%20regular%20numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fimsub-function-2e404b4d-4935-4e85-9f52-cb08b9a45054%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fimsub-function-2e404b4d-4935-4e85-9f52-cb08b9a45054%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2151425%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20Formating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2151425%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F972540%22%20target%3D%22_blank%22%3E%40Kcirevam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20convert%20the%20text%20to%20numbers%20by%20prefixing%20by%20a%20double%20negation%20but%2C%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bsuggests%2C%20the%20use%20of%20complex%20arithmetic%20to%20capture%20stock%20dealings%20is%20not%20to%20be%20recommended.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPersonally%2C%20I%20would%20also%20convert%20all%20the%20tabular%20data%20to%20Excel%20Tables%20and%20use%20sheet-local%20Names%20to%20reference%20the%20non-tabular%20values.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2151476%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20Formating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2151476%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3EI%20came%20to%20use%20that%20formula%20by%20being%20very%20new%20to%20this.%20Lol%2C%20I%20did%20not%20realize%20I%20could%20do%20it%20the%20way%20you%20recommended.%20That%20did%20fix%20the%20problem.%20Thank%20you%20for%20the%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, I had tech support help me via quick assist for this issue. He wasn't able to correct it and suggested posting here.

I have included the spreadsheet that I am talking about.

I am unable to get the cell H6 to format to currency on all sheets, except for the one labelled overall. The sheet labelled overall doesn't require that formatting though.

Thank you for any help.

3 Replies
Best Response confirmed by Kcirevam (New Contributor)
Solution

@Kcirevam I wonder how you came about to use the IMSUB function. Its use is to work with complex numbers as described in the article linked below. The result is a text, not a number and therefore can not be formatted as a number in any way.

 

You should use =E6-G6 in order to subtract two regular numbers.

 

https://support.microsoft.com/en-us/office/imsub-function-2e404b4d-4935-4e85-9f52-cb08b9a45054 

@Kcirevam 

You could convert the text to numbers by prefixing by a double negation but, as @Riny_van_Eekelen suggests, the use of complex arithmetic to capture stock dealings is not to be recommended.

 

Personally, I would also convert all the tabular data to Excel Tables and use sheet-local Names to reference the non-tabular values.

@Riny_van_EekelenI came to use that formula by being very new to this. Lol, I did not realize I could do it the way you recommended. That did fix the problem. Thank you for the help.