Electric Brewing Supply 30A BCS Giveaway!


Home Brew Forums > Home Brewing Beer > Brewing Software > Need Some Excel Formatting Help
Reply
 
LinkBack Thread Tools
Old 10-18-2009, 01:31 AM   #1
jescholler
Feedback Score: 0 reviews
Recipes 
 
Join Date: Feb 2009
Location: Louisville, CO
Posts: 553
Liked 3 Times on 3 Posts

Default Need Some Excel Formatting Help

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
jescholler is offline
 
Reply With Quote Quick reply to this message
Old 10-18-2009, 01:39 AM   #2
ohrinet
Feedback Score: 0 reviews
Recipes 
 
Join Date: May 2009
Location: Boston, MA
Posts: 55
Liked 1 Times on 1 Posts

Default

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

ohrinet is offline
 
Reply With Quote Quick reply to this message
Old 10-18-2009, 02:21 AM   #3
Yuri_Rage
Gritty.
HBT_MODERATOR.png
Feedback Score: 0 reviews
 
Yuri_Rage's Avatar
Recipes 
 
Join Date: Jul 2006
Location: Southwest
Posts: 13,938
Liked 621 Times on 387 Posts
Likes Given: 56

Default

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
Yuri_Rage is offline
 
Reply With Quote Quick reply to this message
Old 10-18-2009, 03:03 AM   #4
jescholler
Feedback Score: 0 reviews
Recipes 
 
Join Date: Feb 2009
Location: Louisville, CO
Posts: 553
Liked 3 Times on 3 Posts

Default

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
jescholler is offline
 
Reply With Quote Quick reply to this message
Old 10-18-2009, 03:04 AM   #5
Yuri_Rage
Gritty.
HBT_MODERATOR.png
Feedback Score: 0 reviews
 
Yuri_Rage's Avatar
Recipes 
 
Join Date: Jul 2006
Location: Southwest
Posts: 13,938
Liked 621 Times on 387 Posts
Likes Given: 56

Default

Lose the hyphen in the concatenation formula, and make the custom format add it for you.

__________________
Homebrewed Blog..........YouTube Channel .......... Shirts, posters, etc
Yuri_Rage is offline
 
Reply With Quote Quick reply to this message
Old 10-18-2009, 03:21 AM   #6
mvdilts
HBT_SUPPORTER.png
Feedback Score: 0 reviews
Recipes 
 
Join Date: Jul 2009
Location: Michigan, USA
Posts: 116
Liked 1 Times on 1 Posts
Likes Given: 1

Default

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

__________________

mvdilts is offline
 
Reply With Quote Quick reply to this message
Old 10-18-2009, 02:24 PM   #7
jescholler
Feedback Score: 0 reviews
Recipes 
 
Join Date: Feb 2009
Location: Louisville, CO
Posts: 553
Liked 3 Times on 3 Posts

Default

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
jescholler is offline
 
Reply With Quote Quick reply to this message
Old 10-18-2009, 10:50 PM   #8
passedpawn
Waste Allocation Load Lifter - Earth Class
HBT_MODERATOR.png
Feedback Score: 0 reviews
 
passedpawn's Avatar
Recipes 
 
Join Date: Apr 2009
Location: ☼ Clearwater, FL ☼
Posts: 19,253
Liked 3480 Times on 2154 Posts
Likes Given: 2987

Default

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.
__________________
Hey goomba I love how you dance the rumba
But take some advice paisano learn-a how to mambo
If you're gonna be a square you ain't-a gonna go anywhere.
passedpawn is offline
 
Reply With Quote Quick reply to this message
Reply



Quick Reply
Message:
Options
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-13-2009 11:14 PM
Wierd formatting problem Dude HomeBrewTalk Announcements & Feedback 3 01-23-2007 06:51 PM