Formatting for weight, like pecentage/money/accounting

**DarkSoul** · 07-27-2014, 10:02 AM

New day, new question.

I have blank cells that will have a value inputted as weight. I would like the cells to function like blank cells with specific formatting such as percentage or money.

If the cell is blank, then it simply remains blank, but when a number is inputted I would like it to automatically have "Lbs" added to the end of the number. I know I could concatenate but I'm not sure how to do that with blank cells, nor am I particularly good at concatenating cells with values lol.

Ideally, but not necessary, I would like to be able to convert the weights automatically so if a person inputted 454g it would change it to 1Lb, etc. I think I could probably use conditional formatting to accomplish that though.

**AlKey** · 07-27-2014, 10:14 AM

You can use CONVERT function for conversions however, Excel can't apply formatting since it can't differentiate between numbers.

**DarkSoul** · 07-27-2014, 10:31 AM

I don't quite follow.

Are you saying that I can convert the numbers, but that it won't be able to apply formatting because excel won't know if 454 is g, ounces, or pounds??
I was thinking of using conditional formatting for this, so if the number is <=0 and >454 then format for grams......
I would need to make a custom format for that though and I have no idea how to actually do that at all.

The initial input will always be in grams. I have a rather extensive Lego collection that I'm finally organizing, and I'm tracking some bricks by weight, so when I weigh them I always get the weight in grams.

From there it would just be a matter of converting the number and using a custom conditional format would it not?

**AlKey** · 07-27-2014, 10:45 AM

It is not all that clear. I suggest you upload a sample spreadsheet with about 10-15 rows of data so we could figure our a solution for you.

To upload a file click on Go Advanced at the bottom of your screen then click on PaperClip icon and Add Files, Select File, locate your file and click Open then click Upload and click Done

**DarkSoul** · 07-27-2014, 11:42 AM

I think I made it sound more complicated.

Basically one column with numbers being added to each row as I weigh stuff. The inputted weight is always in grams.
The numbers range anywhere from 90g to 1134g right now.

As you can see 90g = 3.17oz so when I enter 90, that should be converted to 3.17
1134g = 40Oz = 3.03Lbs, so 1134 should be converted to 3.03,

As I'm typing this I'm starting to realize this may not be possible, or at the very least not feasible because 3.17 and 3.03 could both be ounces...... so I'm pretty much stuck with keeping the weight as grams.

Ok, how about this then, simply adding the suffix "g" automatically after I enter number in a new row. The cell would remain blank until I enter a number, then it would add "g" to the end of the number.

**AlKey** · 07-27-2014, 11:51 AM

Format cell as Custom, [<1000]### "g";#.0##, "kg"

This will add g to the number that is less than 1,000 and it will add kg to number greater than 1,000

**DarkSoul** · 07-27-2014, 12:16 PM

That pretty much solves the problem, thank you.

Out of curiosity though, would there be a way to convert grams to pounds, and format it the same way as above?

**AlKey** · 07-27-2014, 12:20 PM

Sure, this will convert g to lbm by formulas not by formatting.

=CONVERT(A1,"g","lbm")

and g to ozm

=CONVERT(A1,"g","ozm")

**DarkSoul** · 07-27-2014, 12:24 PM

what's lbm and ozm ?

so I could use the convert function to get pounds from grams, then with the formatting I would use <454 instead of 1000?

EDIT

When I try that formula it turns 344g AND 466g into 92.23
Neither of those weights are 92.23Lbs lol.

**AlKey** · 07-27-2014, 12:37 PM

344 g = 0.758 lb

=CONVERT(A1,"g","lbm")

	A	B
1	344	0.758

**DarkSoul** · 07-27-2014, 01:06 PM

I was using the wrong cell....
Anyhoo, That would work except it makes a circular reference because I want to use the data inputted into the cell containing the formula, plus I think it would be slightly more complicated to separate grams from pounds, IF statements would be needed.

like
IF the cell value is >454 THEN don't convert.
IF cell value is <= 454 THEN convert.

**DarkSoul** · 07-27-2014, 01:10 PM

double post, sorry.

**AlKey** · 07-27-2014, 01:15 PM

You can't input into the same cell where you have formula written. This is not how excel formulas work. To make any changes to the cell with data in it can only be done with VBA.

**DarkSoul** · 07-27-2014, 01:39 PM

I figured it would be ridiculously complicated.
I'll just stick to formatting for g and kg then.

**newdoverman** · 07-27-2014, 02:17 PM

Select the cells that will have the weights, right click and choose Format Cells. Click on Custom and enter ###" lbs" as the custom format. When a value is entered such as 123 the result will be 123 lbs.

Then in the next column enter =CONVERT(A1,"lbm","kg") and format the cells as ###" kg"

	A	B
1	123 lbs	55.79 kg
2	456 lbs	206.84 kg

**DarkSoul** · 07-27-2014, 03:22 PM

Originally Posted by newdoverman

Select the cells that will have the weights, right click and choose Format Cells. Click on Custom and enter ###" lbs" as the custom format. When a value is entered such as 123 the result will be 123 lbs.

Then in the next column enter =CONVERT(A1,"lbm","kg") and format the cells as ###" kg"

	A	B
1	123 lbs	55.79 kg
2	456 lbs	206.84 kg

Thanks, but it isn't really important enough to add another column. I can just as easily convert the kg number to lbs by multiplying by 2.2

Formatting for weight, like pecentage/money/accounting

LinkBack

Thread Tools

Rate This Thread

Display

Formatting for weight, like pecentage/money/accounting

Re: Formatting for weight, like pecentage/money/accounting

Re: Formatting for weight, like pecentage/money/accounting

Re: Formatting for weight, like pecentage/money/accounting

Re: Formatting for weight, like pecentage/money/accounting

Re: Formatting for weight, like pecentage/money/accounting

Re: Formatting for weight, like pecentage/money/accounting

Re: Formatting for weight, like pecentage/money/accounting

Re: Formatting for weight, like pecentage/money/accounting

Re: Formatting for weight, like pecentage/money/accounting

Re: Formatting for weight, like pecentage/money/accounting

Re: Formatting for weight, like pecentage/money/accounting

Re: Formatting for weight, like pecentage/money/accounting

Re: Formatting for weight, like pecentage/money/accounting

Re: Formatting for weight, like pecentage/money/accounting

Re: Formatting for weight, like pecentage/money/accounting

Thread Information

Users Browsing this Thread

Similar Threads

Auto Calculate Shipping Weight/Cartons and Package Weight.

Problem with Accounting Formatting Option

Accounting Macro Formatting $

Formatting userform textbox to Accounting

OUTSOURCE YOUR ACCOUNTING TO SAVE TIME & MONEY !!

Bookmarks

Bookmarks

Posting Permissions