Hi! I'm struggling with how to put the correct unit of measure on a number.
In some cases the unit of measure is grams. In other cases, the unit of
measure is mililiters. I was planning on having a hidden column that would
hold the letter "g," the letters "mL" (or that might be empty --
ACCIDENTALLY, of course).

Example

A1 = 6, B1 = mL
A2 = 10, B2 = g

Column B would be hidden (not visible)

An IF-THEN formula gets me part of the way. But how do I do the Custom
Format label like you'd see if you clicked on the following ...

Format -- Cells -- Number -- Custom -- 0.0 "g"
-or-
Format -- Cells -- Number -- Custom -- 0.0 "mL"

I think I can figure out the IF-THEN part of things. It's just the labeling
I'm struggling with. I need to be able to do some math on the contents of
cells in Column A.

If anyone is able to point me in the right direction, it would be much
appreciated

may be taking a helper column and concatenate A and B into that column.
best wishes
Sreedhar

"Tampa-Terry" wrote:

Hi! I'm struggling with how to put the correct unit of measure on a number.
In some cases the unit of measure is grams. In other cases, the unit of
measure is mililiters. I was planning on having a hidden column that would
hold the letter "g," the letters "mL" (or that might be empty --
ACCIDENTALLY, of course).

Example

A1 = 6, B1 = mL
A2 = 10, B2 = g

Column B would be hidden (not visible)

An IF-THEN formula gets me part of the way. But how do I do the Custom
Format label like you'd see if you clicked on the following ...

Format -- Cells -- Number -- Custom -- 0.0 "g"
-or-
Format -- Cells -- Number -- Custom -- 0.0 "mL"

I think I can figure out the IF-THEN part of things. It's just the labeling
I'm struggling with. I need to be able to do some math on the contents of
cells in Column A.

If anyone is able to point me in the right direction, it would be much
appreciated

Assuming your values will **always** be positive numbers, here is a kludge
solution, but it's probably not a very good one. Format the range of cells
using this custom format...

0.00 "mL";0.00 "g"

To get the 'mL' suffix, just enter a number normally; to get the 'g' suffix,
enter your number as a negative value. Now (and this is why the kludge is
not a very good one) this negative value (for the 'g' entries) will
**display** without the leading minus sign, but the value **will** still be
a negative one. That means, in any calculations that refer to a cell with
this formatting, you **must** wrap an ABS function around the cell
reference. As an example, if B2 has the above formatting applied to is and
you wanted to multiply the value in B2 by 3, the formula you use to do that
would have to be...

=3+ABS(B2)

Rick


"Tampa-Terry" wrote in message
...
Hi! I'm struggling with how to put the correct unit of measure on a
number.
In some cases the unit of measure is grams. In other cases, the unit of
measure is mililiters. I was planning on having a hidden column that
would
hold the letter "g," the letters "mL" (or that might be empty --
ACCIDENTALLY, of course).

Example

A1 = 6, B1 = mL
A2 = 10, B2 = g

Column B would be hidden (not visible)

An IF-THEN formula gets me part of the way. But how do I do the Custom
Format label like you'd see if you clicked on the following ...

Format -- Cells -- Number -- Custom -- 0.0 "g"
-or-
Format -- Cells -- Number -- Custom -- 0.0 "mL"

I think I can figure out the IF-THEN part of things. It's just the
labeling
I'm struggling with. I need to be able to do some math on the contents of
cells in Column A.

If anyone is able to point me in the right direction, it would be much
appreciated

A formula cannot change the format of a cell.

Why not leave things as they are but unhide column B? You can always
apply conditional formatting to column B such that if column A is
empty then use a white foreground font - the cell will appear blank
when A is blank.

Pete

On Apr 18, 2:51*am, Tampa-Terry wrote:
Hi! *I'm struggling with how to put the correct unit of measure on a number. *
In some cases the unit of measure is grams. *In other cases, the unit of
measure is mililiters. *I was planning on having a hidden column that would
hold the letter "g," the letters "mL" (or that might *be empty --
ACCIDENTALLY, of course).

Example

A1 = 6, B1 = mL
A2 = 10, B2 = g

Column B would be hidden (not visible)

An IF-THEN formula gets me part of the way. *But how do I do the Custom
Format label like you'd see if you clicked on the following ...

Format -- Cells -- Number -- Custom -- 0.0 "g"
-or-
Format -- Cells -- Number -- Custom -- 0.0 "mL"

I think I can figure out the IF-THEN part of things. *It's just the labeling
I'm struggling with. *I need to be able to do some math on the contents of
cells in Column A.

If anyone is able to point me in the right direction, it would be much
appreciated

Hi,

What criteria do you use to decide if it's mL or g?

Dave

url:http://www.ureader.com/msg/104232779.aspx

Dave Curtis asked, €œWhat criteria do you use to decide if it's mL or g?€�

Dave, I am working with recipes. Dry ingredients that are weighed are
listed in grams. Liquid ingredients are listed in mL.

Yshridhar wrote, €œMaybe taking a helper column and concatenate A and B into
that column.€œ

Thats the idea I had originally. The problem, though, is that I need to be
able do math on the cells that hold €œ6 mL€� or €œ10 g.€� I cant really
multiply the results in a concatenated cell by a multiple because the cells
are no longer considered to be numbers.

Pete_UK wrote, €œA formula cannot change the format of a cell. Why not leave
things as they are but unhide column B? You can always apply conditional
formatting to column B such that if column A is empty then use a white
foreground font - the cell will appear blank when A is blank.€�

Pete, Ill have to reformat the recipes I have to do what you suggest, but
that may end up being just what I need to do. One column for Dry Ingredients
measured (and labeled) in grams and then a second column for Liquid
Ingredients measured (and labeled) in milliliters.

If there were two columns (Columns A and B) with data in either one column
or the other (but not both!), is there a formula that I could use in Column C
that would report the data. The only thing Id have to make provisions for
is that there might not be data in either Column A or B.

Rick Rothstein (MVP - VB), Im holding onto your post because I can see that
kludge solution or not, it would get me the end result I was looking for.
Im going to be trying out both your and Petes ideas and see which works
best layout/formatting-wise.

Thank you, everybody for some fabulous ideas!!!

If you always used two characters to indicate the units, e.g. "6 mL" or "10
gm", or even "6mL" and "10 g", then you could do arithmetic on these by
using:

LEFT(A1,LEN(A1)-3), or
LEFT(A1,LEN(A1)-2)

to extract the numerical part and multiplying by the quantity needed.

Hope this helps.

Pete

"Tampa-Terry" wrote in message
...
Dave Curtis asked, "What criteria do you use to decide if it's mL or g?"

Dave, I am working with recipes. Dry ingredients that are weighed are
listed in grams. Liquid ingredients are listed in mL.

Yshridhar wrote, "Maybe taking a helper column and concatenate A and B
into
that column."

That's the idea I had originally. The problem, though, is that I need to
be
able do math on the cells that hold "6 mL" or "10 g." I can't really
multiply the results in a concatenated cell by a multiple because the
cells
are no longer considered to be numbers.

Pete_UK wrote, "A formula cannot change the format of a cell. Why not
leave
things as they are but unhide column B? You can always apply conditional
formatting to column B such that if column A is empty then use a white
foreground font - the cell will appear blank when A is blank."

Pete, I'll have to reformat the recipes I have to do what you suggest, but
that may end up being just what I need to do. One column for Dry
Ingredients
measured (and labeled) in grams and then a second column for Liquid
Ingredients measured (and labeled) in milliliters.

If there were two columns (Columns A and B) with data in either one column
or the other (but not both!), is there a formula that I could use in
Column C
that would report the data. The only thing I'd have to make provisions
for
is that there might not be data in either Column A or B.

Rick Rothstein (MVP - VB), I'm holding onto your post because I can see
that
kludge solution or not, it would get me the end result I was looking for.
I'm going to be trying out both your and Pete's ideas and see which works
best layout/formatting-wise.

Thank you, everybody for some fabulous ideas!!!

Or, if there is always a space between the number and units' abbreviation
(as appears to be the case), you could leave the 'mL' and 'g' abbreviations
and get the number part using this...

=LEFT(A1,FIND(" ",A1)-1)

Rick


"Pete_UK" wrote in message
...
If you always used two characters to indicate the units, e.g. "6 mL" or
"10 gm", or even "6mL" and "10 g", then you could do arithmetic on these
by using:

LEFT(A1,LEN(A1)-3), or
LEFT(A1,LEN(A1)-2)

to extract the numerical part and multiplying by the quantity needed.

Hope this helps.

Pete

"Tampa-Terry" wrote in message
...
Dave Curtis asked, "What criteria do you use to decide if it's mL or g?"

Dave, I am working with recipes. Dry ingredients that are weighed are
listed in grams. Liquid ingredients are listed in mL.

Yshridhar wrote, "Maybe taking a helper column and concatenate A and B
into
that column."

That's the idea I had originally. The problem, though, is that I need to
be
able do math on the cells that hold "6 mL" or "10 g." I can't really
multiply the results in a concatenated cell by a multiple because the
cells
are no longer considered to be numbers.

Pete_UK wrote, "A formula cannot change the format of a cell. Why not
leave
things as they are but unhide column B? You can always apply conditional
formatting to column B such that if column A is empty then use a white
foreground font - the cell will appear blank when A is blank."

Pete, I'll have to reformat the recipes I have to do what you suggest,
but
that may end up being just what I need to do. One column for Dry
Ingredients
measured (and labeled) in grams and then a second column for Liquid
Ingredients measured (and labeled) in milliliters.

If there were two columns (Columns A and B) with data in either one
column
or the other (but not both!), is there a formula that I could use in
Column C
that would report the data. The only thing I'd have to make provisions
for
is that there might not be data in either Column A or B.

Rick Rothstein (MVP - VB), I'm holding onto your post because I can see
that
kludge solution or not, it would get me the end result I was looking for.
I'm going to be trying out both your and Pete's ideas and see which works
best layout/formatting-wise.

Thank you, everybody for some fabulous ideas!!!

Pete & Rick, the problem may be that I'm new to using the formula and don't
understand what I'm doing well enough to troubleshoot my error. Here's
actual data ...

Cell A27 = Lamb Shanks
Cell B27 is empty because that's the column for liquid measurement (mL)
Cell C27 = 340.0 g (Cell formatted as Custom, with a 0.00 "g" label)
Cell D27 is where I want to put my formula. I'll either have a number in
Column B (unit of measure is mL) or a number in Column C (unit of measure is
g). The only time I might not have a number in either one is if an
ingredient amount were something like "add salt & pepper to taste."

After the formula in D27 is working I'll hide Columns B and C. Column D
would report the unit of measure in grams or mililiters, depending on which
of the two columns holds data.

By the way, Cell C27 already holds a formula. The original recipe
numbers/amounts is off to the side. I'm multiplying THAT number by a
multiple. The results (340 g) show up in Cell C27. Let's say the number off
to the side were 170 and I wanted to double the recipe. I have a cell that
holds the multiple (2 in this case) and every ingredient in the recipe in the
spreadsheet says multiply NUMBER OFF TO THE SIDE x NUMBER IN CELL HOLDING
MULTIPLE.

What I'm wanting to do is to grab the number that shows up in either Column
B or C and display it in column D with the correct label (grams or mL).

If I'm not explaining myself well, let me know, and I'll try again. In the
meantime, I've got to go check out LEFT and FIND. (grin)

Since one of B27 or C27 will always be empty, you should be able to put this
formula in D27 to show only the number (that is, without the unit's
suffix)...

=IF(B27&C27="","",LEFT(B27&C27,FIND(" ",B27&C27)-1))

The "long" way to do the same thing would be...

=IF(B27&C27="","",IF(B27="",LEFT(C27,FIND(" ",C27)-1),LEFT(B27,FIND("
",B27)-1)))

Rick



"Tampa-Terry" wrote in message
...
Pete & Rick, the problem may be that I'm new to using the formula and
don't
understand what I'm doing well enough to troubleshoot my error. Here's
actual data ...

Cell A27 = Lamb Shanks
Cell B27 is empty because that's the column for liquid measurement (mL)
Cell C27 = 340.0 g (Cell formatted as Custom, with a 0.00 "g" label)
Cell D27 is where I want to put my formula. I'll either have a number in
Column B (unit of measure is mL) or a number in Column C (unit of measure
is
g). The only time I might not have a number in either one is if an
ingredient amount were something like "add salt & pepper to taste."

After the formula in D27 is working I'll hide Columns B and C. Column D
would report the unit of measure in grams or mililiters, depending on
which
of the two columns holds data.

By the way, Cell C27 already holds a formula. The original recipe
numbers/amounts is off to the side. I'm multiplying THAT number by a
multiple. The results (340 g) show up in Cell C27. Let's say the number
off
to the side were 170 and I wanted to double the recipe. I have a cell
that
holds the multiple (2 in this case) and every ingredient in the recipe in
the
spreadsheet says multiply NUMBER OFF TO THE SIDE x NUMBER IN CELL HOLDING
MULTIPLE.

What I'm wanting to do is to grab the number that shows up in either
Column
B or C and display it in column D with the correct label (grams or mL).

If I'm not explaining myself well, let me know, and I'll try again. In
the
meantime, I've got to go check out LEFT and FIND. (grin)