Converting calculated number to Units of Measure

[jgoldston]

I have finally worked myself into complete brain inertia - much like the flat line of a heart monitor.

I am trying to return a result from a caculated value to a unit of measure based upon a predefined UOM such as I want the result to be in LBS / OZ / Grams and fractions thereof where appropriate. There are several factors involved and I will try to explain:

In a portal row of a master record (Master Formula Manager (1)) I am listing ingredients of a particular compound. These records are individual in my BOM Formulai (2) db.

When the formula is complete, I receive a "Total Parts" calculated result (sum(BOM formulai:Qty). A batch size is predefined (in this case "50" lbs). To get a conversion factor to turn the parts into units of measure the formula reads (Batch Size/Total Parts) or (50/107) and yeilds a conversion factor of .467.

Once the conversion factor is determined, it is then multiplied by each seperate ingredient (back in the BOM db) to yeild a total lbs per ingredient. As an example the first ingredient calls for 80 parts. The result would be (80*.467 = 37.36 lbs). This is fine and good for future inventory usage calculations, but I need the operators to measure in Pounds and Ounces not just a decimal of pounds.

I have created a second calc field to convert the 37.36 to a result (placeholder right now). I would like the result to read in Pounds and Ounces of 37.36 or rather 37 lbs. 6 oz. (.36*16 (oz. in lb) = 5.76 (round)).

The second issues is that if the part yeilds something less than .1 of a lb then the conversion needs to be made into grams (the next measurable unit we deal with). This conversion would look like (.1*384 (g in lb) = 3.84 g)

I am lost (might be 14 straight hours of working this thing ). Is this a case for Left/Right issues. If/ Case/ ?? The nomenclature is not important to show in the field, I can create a second and third field for oz and grams. Perhaps splitting the 38.16 into three fields? But if the result is measurable in ounces, I don't typically need grams.

To end my whiny-ness. . .This field needs to be dynamic as we run different size batches which would yeild different results based on the conversion factor and desired batch size.

Help -- anybody -- TIA for any assistance!

James Goldston

Orange County, CA

[RussBaker]

James,

This calc (text output) will take a number of "Pounds" and turn it into a text field displaying lbs and oz(rounded to 0 places) unless "Pounds" is less than 0.1, in which case it will display grams (I thought there were approx 453 grams to a pound) rounded to 2 places.

Remember, this would just be for display/printing purposes. As you said, you should keep with your original units for you inventory purposes etc.

Text_Weight =

Case(

Pounds < .1, NumToText(Round(Pounds*453,2)) & " gm",

NumToText(Int(Round(Pounds*16,0)/16)) & " lbs " &

NumToText(Round(Pounds*16,0) - 16*Int(Round(Pounds*16,0)/16)) & " oz"

)

[jgoldston]

Russ

You are my hero! Thank You! Thank You! -- I did not sleep last night but dreamt of this silly formula. I would not have gotten there from here -- had the basic paremeters - but not the formula.

Works like a charm! I need to get the ol text book out and read up on intergers (which is a whole number) and rounding. Alas, my flat-lined brain could not of possibly mustered up your work!

Thanks Again!

James