Need Some Excel Formatting Help - Home Brew Forums
Register Now For Free!

Home Brew Forums > Home Brewing Beer > Brewing Software > Need Some Excel Formatting Help

Reply
 
Thread Tools
Old 10-18-2009, 01:31 AM   #1
jescholler
 
jescholler's Avatar
Recipes 
 
Feb 2009
Louisville, CO
Posts: 534
Liked 6 Times on 6 Posts



I'm trying to create a brewing spreadsheet. What I'm trying to do now is take the low and high gravity for a style and place it in a single cell with a hyphen between (e.g. 1.045-1.060). I have the 1.045 and the 1.060 stored in other cells, lets say A1 and A2 respectively.

Right now, I use the formula =A1&"-"&A2.

The problem is because of the formatting, it results in the cell displaying "1.045-1.06" (without the 0 after the 6).

It's not a huge deal, mainly looks, but there has to be a way for it to display like "1.045-1.060".
__________________
Harsh Bitterness Experiment

Primary: Not until fall :(
Bottle: English Barleywine
On Deck: Session APA, Vanilla Oatmeal Stout

 
Reply With Quote
Old 10-18-2009, 01:39 AM   #2
ohrinet
Recipes 
 
May 2009
Boston, MA
Posts: 53
Liked 2 Times on 1 Posts


If you set the cell format to "text" rather than "general" or "number", you may be able to keep all the formatting the same.
__________________
drinking a chocolate porter

 
Reply With Quote
Old 10-18-2009, 02:21 AM   #3
Yuri_Rage
Gritty.
HBT_MODERATOR.png
 
Yuri_Rage's Avatar
Recipes 
 
Jul 2006
Southwest
Posts: 14,291
Liked 813 Times on 514 Posts


Create a custom number format with 3 decimals and a hyphen. Look at the examples in the dialog box under phone numbers, Social Security numbers, and decimal numbers. It should be somewhat intuitive.

I'd give you the format myself, but I'm using Mac, and I don't have the MS Office suite (and I can't remember the exact characters to type).
__________________
Homebrewed Blog..........YouTube Channel .......... Shirts, posters, etc

 
Reply With Quote
Old 10-18-2009, 03:03 AM   #4
jescholler
 
jescholler's Avatar
Recipes 
 
Feb 2009
Louisville, CO
Posts: 534
Liked 6 Times on 6 Posts


The text formatting didn't work, and I'm not getting the custom formatting to work either.

For the custom format, I tried a couple of things, but I think what I want is "0.000-0.000". I think it's not working because of the concatenation in the formula.

I ended up getting rid of the decimal point, so now it looks better.
__________________
Harsh Bitterness Experiment

Primary: Not until fall :(
Bottle: English Barleywine
On Deck: Session APA, Vanilla Oatmeal Stout

 
Reply With Quote
Old 10-18-2009, 03:04 AM   #5
Yuri_Rage
Gritty.
HBT_MODERATOR.png
 
Yuri_Rage's Avatar
Recipes 
 
Jul 2006
Southwest
Posts: 14,291
Liked 813 Times on 514 Posts


Lose the hyphen in the concatenation formula, and make the custom format add it for you.
__________________
Homebrewed Blog..........YouTube Channel .......... Shirts, posters, etc

 
Reply With Quote
Old 10-18-2009, 03:21 AM   #6
mvdilts
Recipes 
 
Jul 2009
Michigan, USA
Posts: 117
Liked 1 Times on 1 Posts


Try this string =TEXT(A1,"#.###") &- TEXT(B1,"#.####") It works in Excel 2007.


 
Reply With Quote
Old 10-18-2009, 02:24 PM   #7
jescholler
 
jescholler's Avatar
Recipes 
 
Feb 2009
Louisville, CO
Posts: 534
Liked 6 Times on 6 Posts


I'm not having luck removing the concatenation either and then adding it in the custom format. I tried it both when the original cell was 1060 and 1.060.

What mvdilts suggested did work though (with slight modification)! Here's what I ended up with.

=TEXT(A1,"0.000")&"-"&TEXT(A2,"0.000")

I needed to use "0.000" instead of "#.###" because # doesn't display extra zeros. The 0 pads the value with zeros to fill the format.

Thanks everyone! Now back to things that actually affect the functionality of my spreadsheet and not just looks.
__________________
Harsh Bitterness Experiment

Primary: Not until fall :(
Bottle: English Barleywine
On Deck: Session APA, Vanilla Oatmeal Stout

 
Reply With Quote
Old 10-18-2009, 10:50 PM   #8
passedpawn
Waste Allocation Load Lifter - Earth Class
HBT_ADMIN.png
 
passedpawn's Avatar
Recipes 
 
Apr 2009
☼ Clearwater, FL ☼
Posts: 26,579
Liked 7026 Times on 4160 Posts


Quote:
Originally Posted by jescholler View Post
I'm trying to create a brewing spreadsheet. What I'm trying to do now is take the low and high gravity for a style and place it in a single cell with a hyphen between (e.g. 1.045-1.060). I have the 1.045 and the 1.060 stored in other cells, lets say A1 and A2 respectively.

Right now, I use the formula =A1&"-"&A2.

The problem is because of the formatting, it results in the cell displaying "1.045-1.06" (without the 0 after the 6).

It's not a huge deal, mainly looks, but there has to be a way for it to display like "1.045-1.060".
In the cell with the numbers (i.e., A1 and A2), precede the number with an apostrophe ('). This will retain the following zeros. This trick has existed since the earliest version of Excel. I miss Lotus 1-2-3.
__________________
- Andrew

 
Reply With Quote
Reply
Thread Tools


Similar Threads
Thread Thread Starter Forum Replies Last Post
Any Excel genuises out there? RIBeer General Chit Chat 7 09-16-2009 06:43 PM
I use my Excel Spreadsheet LS_Grimmy Brewing Software 15 08-07-2009 07:01 PM
Fun with excel uwjester General Beer Discussion 5 02-14-2009 12:14 AM


Forum Jump