IF-THEN & Conditional Format Label

[Tampa-Terry]

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

 

[yshridhar]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Pete_UK]

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

 

[Dave Curtis]

Hi,

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

Dave

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

 

[Tampa-Terry]

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_UK]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Tampa-Terry]

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)

 

[Rick Rothstein \(MVP - VB\)]

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]

Rick, I really appreciate your assistance!!!

I think I'm doing something wrong. I know formulas have to be PERFECTLY
typed or else you'll get an error message. I cut and pasted your formula
into my spreadsheet, but am getting a VALUE error.

Cell A27 holds the text "Lamb Shanks"
Cell B27 is empty (because we're not measuring liquid/mL)
Cell C27 displays 685.0 g. The actual numerical value is something like
684.99992. The cell is Custom Formatted as 0.0 "g"
Cell D27 is where I tried both of the formulas and got errors.

Should I be using IF-THEN in the formula, something like IF there's a
number in B27, display it, otherwise IF there's a number in C27, display it,
otherwise IF there's nothing in either column, display nothing.