[Dba-office] Fwd: Interesting Excel problem

[Gustav Brock]

Hi Susan

How about: 

=B4+C4/16

/gustav


-----Oprindelig meddelelse-----
Fra: Dba-office [mailto:dba-office-bounces at databaseadvisors.com] På vegne af Susan Harkins
Sendt: 11. april 2017 19:12
Til: dba-office at databaseadvisors.com
Emne: [Dba-office] Fwd: Interesting Excel problem

Our animal care submits some weights in a strange format:

pound;ounce

So, the baby bobcat's weights might resemble 10;7, 14;3, 14;8, and so on.

Parsing them is no problem. But, evaluating and returning poundage in decimal format is proving a challenge because I'm seeing something weird along the way. Specifically, regardless of how I concatenate the two values, I get unexpected results.

I used Column To Text to parse the values using the ; character as the delimiter. Pounds are in column B, the ounce value is in column C. In column D, I use the formula:

=C4/16

to convert the ounce value into a decimal value.

Here's where I run into trouble -- I've been unable to concatenate the pound integer and the results of that formula to return

10.4375

10 is the number of pounds; .4375 is 7 ounces, expressed as a decimal.

It's impossible to put them together!

I first tried

=B4&D4

which returns

100.4375

instead of 10.4375.

I've tried CONCATENATE(). I've tried working with a text value instead of the results of a formula. I've tried TRIM() and ABS() -- there's something going on that I clearly don't understand.

There's probably an easier way to get what I need -- forest for the trees.
But I don't understand why concatenating these two values -- whether value or text -- wants to add a 0 to the integer.

Susan H.