Conversion formula question

[hartmut]

Hello

I need to make a formula that will help me with a project I am trying to do - I am trying to show common things in Cubic feet in relation to other cubic feet (real world objects with text) the user will enter in my database. IF they entered an example it would look up in another table and find the closest match and then bring the text into a new field called (result)

Here is my example but I don't know how to write a formula for this.

Say I have the cubic feet for the follow commonly known objects

1 a match box

2 a cereal box

3.a garbage can

I have a the cubic inches of all of them in the other file

the matchbox = 1.182 cubic inches

the cereal box = 288 cubic inches

The garbage can = 7372.8 cubic inches

The thing I am trying to do is let a user enter an amount in a field called cubic amount and then through a calc let them see what their amount would translate into.

For example if they said

894 Cubic Inches

I would like the calc to make the answer come up with an answer for them like

894 cubic inches is about the volume of 3 cereal boxes with 30 cubic inches left over. ( This would be from another table )

I made another table with those statistic in it.

I am concerned on how to make this happen with a formula

I could obviously make thousands of records that were all increments of a cubic inch but that would be real silly in my opinion. That is why I am asking here.

Thanks very much for your time. I know this is challenging.

[Ender]

From your example, there are two basic methods to pull in the next lower item from a related table. Either using a Lookup with the "use next lower" option, or using a range relationship and sorting it descending. Attached is a demo showing both methods.

You'll notice that it only gives one item type for a given volume, even if another smaller item could be used to take up the remainder. You'd need a custom function to recursively check each remainder to get a result like:

"8784 cubic inches is about the volume of 1 Garbage Can, 4 Cereal Boxes, 219 Match Boxes, with .342 cubic inches left over."

Cubit.fp7.zip

[hartmut]

Thanks so much for the good example . That is exactly what I am looking for and I can modify for other things. I do have one question though ...

I have converted everything along the way to cubic inches. But if I were to get the volume of a railroad box car then I would like the left over answer to be in cubic feet at least. How could that I make it jump to a larger unit when necessary?

[Ender]

Take the number and test if it is divisible by a larger unit volume, if so, convert.

See the updated attachment.

Cubit2.fp7.zip