Hi There
Please help me custom format the attached file - I have tried to create a custom number format which I can drag down (i.e. paste special - format) but for some reason both Cells A3 & A4 do not display the "Ft (short for feet) correctly even though the final sum total (cell A5) does display correctly. Any help would be appreciated - thanks!
Regards
SilverBullet17
Last edited by SilverBullet17; 01-16-2017 at 02:15 PM.
A5 didn't display 51.600 SF when I opened the file.
Your custom format doesn't contain "SF" it contains "FT"
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Thanks Richard, you were correct (I've edited accordingly).
What I am trying to figure out is how to display cells A3 and A4 as 45,000 FT and 5,000 FT accordingly (I could merely add another 0 i.e. ##,#000 " FT" as a once-off) but I cannot use this same formula for Cell A2 (if I did, it would show 1,6000 instead of the 1,600). As such I am looking for a standard formatting formula which would allow me to format Cell A2 to display 1,600 and then copy this custom format (drag down) to Cells A3 and A4 which would hopefully then display 45,000 FT and 5000 FT instead of 45,00 FT and 5,00 FT respectively.
I hope this explains it better - apologies for the confusion.
Try a format string like this:
#,##0" FT"
Hope this helps.
Pete
Hi Pete
Thanks for the help I have in fact tried that exact formula but it doesn't seem to work - it gives me option 1 in attached spreadsheet. I have also tried Option 2 but am actually looking for Option 3.
Do you think there may me something wrong/inconsistent with my excel number settings? If so what would you recommend I change?
Thanks!
Your Option 3 is exactly what I see with the format string that I suggested.
I had assumed that in South Africa your Regional settings were similar to those in the UK, i.e. that you use a full-stop for a decimal point, and commas are used as a thousands separator - is that not the case?
Pete
Part of my confusion on this one is trying to be internationally aware -- Are you using "," as a thousands separator or as a decimal point (I recognize that much of this is that I am not usually "internationally aware" so I am very used to US conventions).
What I see in your file (after Excel switches for my local decimal and thousand's separator settings) is that you are trying to display numbers in the thousands as fractional thousands. The format string as I opened it was ##.#00," FT" The comma tells Excel to scale the displayed number by 1000, the 0's tell Excel to display those digits even if they are insignificant zeros, and the #'s tell Excel to only display the values that are significant, but to not display insignificant zeros. (https://support.office.com/en-us/art...2-09fab54be7f4 help file describing number format codes). So I see 45E3 displayed as 45.00, where Excel displays (as the format code instructs) two of the three insignificant zeroes to the right of the decimal point. I would probably change the third digit to the right of the decimal point to a 0 instead of #, so that Excel will display all three insignificant zeroes to the right of the decimal point (I usually like to see the insignificant zero to the left of the decimal point for numbers between 0 and 1, so I would probably change that as well). I would probably use something like 0.000," FT" (using . for decimal separator and , for thousands separator/scale display by 1000 as I am used to doing. Adapt for your decimal/thousands separator settings).
If I have completely misunderstood what you want, please clarify, and we'll help you come up with what you really want.
Originally Posted by shg
Hi Pete
Thank the lord - for some reason, the South African decimal point was a comma (changed it to a full stop now and it worked) - thanks a mil!!!
You're welcome - glad to help. Thanks for the rep.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks